Update trigger

Question

Update trigger

harikolluri

SSC Veteran

Points: 233
More actions
September 2, 2003 at 8:27 am

#82837

I want to insert a row into another table (table b)For each column updation of one table(table a).if I update col1 value with same value in table a I do not want to insert into table b.I may do multi-row updation also,in such a case how can i write the trigger.
Can anyone help me on this problem.Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

NPeeters SSChampion Points: 12535 More actions · Answer 1

Try something like



CREATE TRIGGER tr_Update

    ON tableA

    FOR UPDATE

AS

   INSERT INTO tableB (<FIELDS>)

     SELECT <FIELDS>

       FROM INSERTED I

         INNER JOIN

            DELETED D

         ON I.IdField = D.IdField

        AND I.col1 <> D.col1

harikolluri SSC Veteran Points: 233 More actions · Answer 2

harikolluri

SSC Veteran

Points: 233

September 2, 2003 at 10:09 am

#472214

why is deleted came into picture?

NPeeters SSChampion Points: 12535 More actions · Answer 3

In each trigger, regardless of the type (INSERT, UPDATE and DELETE), there are two 'internal' tables : 'inserted' and 'deleted'.

By default, the 'inserted' table contains the new values, whereas 'deleted' contains the old values.

So, for an INSERT trigger, the deleted table will be empty.

For an UPDATE trigger, both the inserted and deleted table will contain values.

For a DELETE trigger, the inserted table will be empty.

In my solution, I compare the old and new values using the inserted and deleted tables. This will give me an indication if the column has been updated.