locked
update value on the other table using trigger RRS feed

  • Question

  • I have  table  Payment  and  in  this  table found column  'CurrencyValue'

    and  other  table  is  Supplier  and  have  column  'Total'

    I want 

    if  the 'CurrencyValue' insert ot update he update  the  value  of 'Total'

    Sunday, July 11, 2010 4:41 PM

Answers

  • You can do something like this(you need to replace ID with whatever the common key between both tables). Please test it first.

     

    CREATE TRIGGER utr_Insert_Update_Supplier
    ON Payment
    AFTER INSERT,UPDATE
    AS
    IF UPDATE(CurrencyValue)
    	BEGIN
    		IF EXISTS(SELECT * 
    				FROM Supplier S
    					JOIN inserted i
    						ON S.id = i .id)
    			BEGIN
    				UPDATE Supplier
    				SET Total = i.Total
    				FROM Supplier S
    					JOIN inserted i
    						ON S.ID = i.ID
    			END
    		ELSE
    			BEGIN
    				INSERT INTO Supplier(ID,Total)
    				SELECT ID,CurrencyValue
    				FROM inserted 
    			END
    	END



    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by KJian_ Friday, July 16, 2010 9:15 AM
    Sunday, July 11, 2010 5:20 PM
  • You can use either trigger or computed column for this task.

    Here is an example of using trigger:

    create trigger trInsertUpdatePament on Payment AFTER INSERT, UPDATE, DELETE 
    
    AS
    
    BEGIN
     if @@ROWCOUNT = 0
     return
    
     IF Exists (select 1 from Deleted) and not exists (select 1 from Inserted)
     -- Delete Operation
     begin
     ;with cte as (select ClientID, sum(CurrencyValue) as Removed from Deleted group by ClientID)
     update Supplier set Total = Total - D.Removed from
     Supplier inner join cte D on Supplier.ClientID = D.ClientID -- use the field which is relevant to join
     end
    
     IF not Exists (select 1 from Deleted) and exists (select 1 from Inserted)
     -- Insert Operation
     
    begin
     ;with cte as (select ClientID, sum(CurrencyValue) as [New] from Inserted group by ClientID)
     update Supplier set Total = Total + I.[New] from
     Supplier inner join cte I on Supplier.ClientID = I.ClientID -- use the field which is relevant to join
    end
    
     IF Exists (select 1 from Deleted) and exists (select 1 from Inserted)
     -- Update Operation
    update Supplier set Total = Total + X.Diff from
     Supplier inner join (Select I.ClientID, sum(I.CurrencyValue - D.CurrencyValue) as Diff from Inserted I 
    inner join Deleted D on I.PaymentID = D.PaymentID group by I.ClientID) X on Supplier.ClientID = X.ClientID
    
    END

     

    The above is from the top of my head - not tested

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Friday, July 16, 2010 9:15 AM
    Sunday, July 11, 2010 5:22 PM

All replies

  • You can do something like this(you need to replace ID with whatever the common key between both tables). Please test it first.

     

    CREATE TRIGGER utr_Insert_Update_Supplier
    ON Payment
    AFTER INSERT,UPDATE
    AS
    IF UPDATE(CurrencyValue)
    	BEGIN
    		IF EXISTS(SELECT * 
    				FROM Supplier S
    					JOIN inserted i
    						ON S.id = i .id)
    			BEGIN
    				UPDATE Supplier
    				SET Total = i.Total
    				FROM Supplier S
    					JOIN inserted i
    						ON S.ID = i.ID
    			END
    		ELSE
    			BEGIN
    				INSERT INTO Supplier(ID,Total)
    				SELECT ID,CurrencyValue
    				FROM inserted 
    			END
    	END



    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by KJian_ Friday, July 16, 2010 9:15 AM
    Sunday, July 11, 2010 5:20 PM
  • You can use either trigger or computed column for this task.

    Here is an example of using trigger:

    create trigger trInsertUpdatePament on Payment AFTER INSERT, UPDATE, DELETE 
    
    AS
    
    BEGIN
     if @@ROWCOUNT = 0
     return
    
     IF Exists (select 1 from Deleted) and not exists (select 1 from Inserted)
     -- Delete Operation
     begin
     ;with cte as (select ClientID, sum(CurrencyValue) as Removed from Deleted group by ClientID)
     update Supplier set Total = Total - D.Removed from
     Supplier inner join cte D on Supplier.ClientID = D.ClientID -- use the field which is relevant to join
     end
    
     IF not Exists (select 1 from Deleted) and exists (select 1 from Inserted)
     -- Insert Operation
     
    begin
     ;with cte as (select ClientID, sum(CurrencyValue) as [New] from Inserted group by ClientID)
     update Supplier set Total = Total + I.[New] from
     Supplier inner join cte I on Supplier.ClientID = I.ClientID -- use the field which is relevant to join
    end
    
     IF Exists (select 1 from Deleted) and exists (select 1 from Inserted)
     -- Update Operation
    update Supplier set Total = Total + X.Diff from
     Supplier inner join (Select I.ClientID, sum(I.CurrencyValue - D.CurrencyValue) as Diff from Inserted I 
    inner join Deleted D on I.PaymentID = D.PaymentID group by I.ClientID) X on Supplier.ClientID = X.ClientID
    
    END

     

    The above is from the top of my head - not tested

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Friday, July 16, 2010 9:15 AM
    Sunday, July 11, 2010 5:22 PM
  • Thk's  u  very  Much

     

     

    Thursday, July 15, 2010 8:58 PM