none
Using update function from an after trigger

    Question

  •  

    Hi ! I have to do an audit table for my entitys from my database. I choose to do this using triggers.

    Here is a sample of my code:

     

    create trigger myTrigger on dbo.person

    after insert,delelte, update

    as

    begin

    ....

      if update(lastname)

      begin

       -- do some events

      end

     

     if update(surname)

      begin

      -- do other events

      end

    ....

    end

     

     My question is that can i notice what columns were update without "iterate" thorugh them like in my example. For example to take only the colums which were updated using Inserted or Deleted tables or other method. I think that this could be done because using an "intersect" action between those tables you can notice what columns were updated but i do not know how to do this in mssql.

     

     Some links with this theme are also welcomed and very helpful.

     

     Thanks !


     

    Friday, October 31, 2008 9:50 PM

Answers

  • Just one more thought - you could do column-based logging in a single INSERT statement.  It wouldn't eliminate needing to compare 50 columns, but it would at least be more efficient.  Also,  SQL 2008 (Enterprise Edition) includes a Change Data Capture feature, but I won't assume that you're using EE.

     

    A single-pass logging statement might look something like this:

     

    Code Snippet

    CREATE TRIGGER myTrigger ON dbo.person

    AFTER UPDATE

    AS

    BEGIN

          INSERT      SomeLogTable (FirstName, MiddleName, Surname.....etc)

          SELECT      CASE

                                  WHEN d.FirstName <> i.FirstName THEN d.FirstName

                                  ELSE NULL

                            END,

                            CASE

                                  WHEN d.MiddleName <> i.MiddleName THEN d.MiddleName

                                  ELSE NULL

                            END,

                            CASE

                                  WHEN d.LastName <> i.LastName THEN d.LastName

                                  ELSE NULL

                            END  

          FROM  Deleted d

          JOIN  Inserted i ON d.SomeKeyField = i.SomeKeyField

    END

     

     

    HTH.

    Sunday, November 02, 2008 4:11 AM
    Moderator
  • Except and INTERSECT work on identical, union compatible resultsets so you maybe can UNPIVOT your Deleted and Inserted tables to single column tables then use Except to filter out unchaged column.

    maybe you can try something like this: (I did not try it)

     

    Select * From

       (Select * From Deleted

         UNPIVOT (

           ColumnValue

           For ColumnName

           IN ([FisrtName], [LastName], ...etc) As X)

        )

    Except

      Select * From

         (Select * From Inserted

         UNPIVOT (

           ColumnValue

           For ColumnName

           IN ([FisrtName], [LastName], ...etc) As Y)

          )

     

    Or, just hardcode your column names

    Select FirstName from Inserted Except select FirstName from Deleted

    Select LastName from Inserted Except select LastName from Deleted

     

    It also feels like a scenario where dynamic queries can come to play. Something like Iterating through your table's column name and executing dynamic queries againt the result set

    Sunday, November 02, 2008 5:56 AM

All replies

  • You're on the right track - it's definitely a good idea to see which rows were updated, rather than performing unnecessary work just because a single row was updated.  You can join the Inserted and Deleted tables, as you had alluded to, to figure out which rows were updated.  Here is an example of an UPDATE trigger which does this:

     

    Code Snippet

    CREATE TRIGGER myTrigger ON dbo.person

    AFTER UPDATE

    AS

    BEGIN

     

          INSERT      SomeLogTable (SomeColumn1, SomeColumn2)

          SELECT      d.UpdatedColumn1, d.FirstName

          FROM  Deleted d

          JOIN  Inserted i ON d.SomeKeyField = i.SomeKeyField

          WHERE d.FirstName <> i.FirstName

     

          INSERT      SomeLogTable (SomeColumn1, SomeColumn2)

          SELECT      d.UpdatedColumn1, d.Surname

          FROM  Deleted d

          JOIN  Inserted i ON d.SomeKeyField = i.SomeKeyField

          WHERE d.Surname <> i.Surname

     

    END

     

     

    Keep in mind that inserts can make use of the Inserted virtual table, deletes can make use of the Deleted virtual table, and updates can use both.  Obviously, if it's a delete or an insert, you're not going to check to see if a column was updated, so this logic only really applies to update triggers.

     

    Does this help?

    Saturday, November 01, 2008 3:19 AM
    Moderator
  •  Thanks a lot ... your post is very helpful when i know that i will have a progresive history for each entity from my table.

     

     Some tables has more than 50 columns.

     

     When an update event occurs against that table , i am intersted only on colums which were updated, your above code is assome , but when i have more than 50 colums i will write a code like that , or use "updated" function for each column ?

     

     Could i get only the updated columns using only an intersection between Inserted and Deleted tables ?

     

    For example i update Surname and Address for an person:

     

    Inserted Table

    FirstName   Surname    Phone  Address 

       john            smith        123      abc

     

    Deleted table

    FirstName     Surname            Phone      Address

     john           SmithUpdated       123       NewAddress

     

    After my trigger runs , using that mecanism ( t-sql or using updated columns , here i don't really know how to do) i get only the :  Surname and Address columns, the updated one. So i am trying not to check if a colum was updated i try to get only the updated one in an efficient way. Notice that i have more than 50 columns in my table.

     

    Thanks  ! 

     Regards , Radu

     

     

    Saturday, November 01, 2008 6:45 PM
  • There isn't really a straightforward way of getting just a listing of the changed row/column intersects.  Though it seems terribly inefficient, most apps I have worked with will log out the entire "before" row from the Deleted virtual table, rather than try to figure out which column was updated.  They will then handle sorting out which columns were updated if and when the log data ever needs to be queried.

     

    Hope this helps.

    Sunday, November 02, 2008 4:01 AM
    Moderator
  • Just one more thought - you could do column-based logging in a single INSERT statement.  It wouldn't eliminate needing to compare 50 columns, but it would at least be more efficient.  Also,  SQL 2008 (Enterprise Edition) includes a Change Data Capture feature, but I won't assume that you're using EE.

     

    A single-pass logging statement might look something like this:

     

    Code Snippet

    CREATE TRIGGER myTrigger ON dbo.person

    AFTER UPDATE

    AS

    BEGIN

          INSERT      SomeLogTable (FirstName, MiddleName, Surname.....etc)

          SELECT      CASE

                                  WHEN d.FirstName <> i.FirstName THEN d.FirstName

                                  ELSE NULL

                            END,

                            CASE

                                  WHEN d.MiddleName <> i.MiddleName THEN d.MiddleName

                                  ELSE NULL

                            END,

                            CASE

                                  WHEN d.LastName <> i.LastName THEN d.LastName

                                  ELSE NULL

                            END  

          FROM  Deleted d

          JOIN  Inserted i ON d.SomeKeyField = i.SomeKeyField

    END

     

     

    HTH.

    Sunday, November 02, 2008 4:11 AM
    Moderator
  • Except and INTERSECT work on identical, union compatible resultsets so you maybe can UNPIVOT your Deleted and Inserted tables to single column tables then use Except to filter out unchaged column.

    maybe you can try something like this: (I did not try it)

     

    Select * From

       (Select * From Deleted

         UNPIVOT (

           ColumnValue

           For ColumnName

           IN ([FisrtName], [LastName], ...etc) As X)

        )

    Except

      Select * From

         (Select * From Inserted

         UNPIVOT (

           ColumnValue

           For ColumnName

           IN ([FisrtName], [LastName], ...etc) As Y)

          )

     

    Or, just hardcode your column names

    Select FirstName from Inserted Except select FirstName from Deleted

    Select LastName from Inserted Except select LastName from Deleted

     

    It also feels like a scenario where dynamic queries can come to play. Something like Iterating through your table's column name and executing dynamic queries againt the result set

    Sunday, November 02, 2008 5:56 AM
  • Thanks a lot for yours posts !

     They were very helpful !

     

    Sunday, November 02, 2008 3:23 PM