Update trigger select fields from same row

0
votes

i want an update trigger an a specific field. if that field value is changed i want to insert into a different table selecting all values of the row where update was made even though it was just for one field .

example 

id--------value1--------value2
1-----------abc ----------efg

if value1 is updated to hij, i want to select id(1), value1(hij) and value2(efg) and insert into a different table.

i cannot do inserted.Id or inserted.value2 since both fields are not updated.

NOTE: please note only 1 field is updated, other field values are the same before and after, in my question i have just used an example, but in real life a record will be inserted and i am expected to insert the same values onto a different table. but upon insert the record wont be approved until later when approved field value is changed thats when i am expected the bring the values from other fields to different table.

2
sqlsql-servertsql

2 Answers

1
votes

In your UPDATE trigger, you have access to the Deleted and Inserted pseudo tables which contain the old values (before the UPDATE) and the new ones after the UPDATE.

So you should be able to write something like this:

CREATE TRIGGER trg_Updated
ON dbo.YourTableName
FOR UPDATE
AS
   INSERT INTO dbo.ThisOtherTableOfYours(Id, Value1, Value2)
      SELECT
         i.Id, i.Value1, i.Value2
      FROM 
         Inserted i
      INNER JOIN
         Deleted d ON i.Id = d.Id
      WHERE 
         i.Value1 <> d.Value1

The SELECT basically joins the two pseudo tables with the old and new values, and selects those rows which have a difference in the Value1 column.

From those columns, the new values after the update are being inserted into your other table. And the Inserted table does contain ALL columns (with their new values) from your table - not just those that have been actually updated - ALL of them!

0
votes

You should use a simple trigger:

create or replace trigger NAME on TABLENAME after update as 
   if :new.value1 = 'hij'{
      insert into OTHERTABLE (id, value1, value2) values (:old.id, :old.value1, :old.value2);
   }