CODEWITHSUNDEEP
sql-servertriggerssql-update
Aco Vujic
Aco Vujic

Reputation: 67

Trigger that inserts only updated column names

I have created trigger that inserts names of the columns changed (insert, update, delete) in the audit table.

I have problem when I update columns. Lets say I have a table dbo.TABLE with columns COL1, COL2, COL3.

Further, lets say that I only have one row:

           COL1   | COL2   | COL3
          ---------------------------
           value1 | value2 | value3

If my update statement looks like this:

         Update dbo.TABLE set COL1 = 'test1', COL2 = 'test2';

In my audit table will be inserted: 

         UPDATED
         -------
         COL1, COL2

This is OK, but lets say I have same table dbo.TABLE with first values (value1, value2, value3).

If my update statement looks like this:

         Update dbo.TABLE set COL1 = 'value1', COL2 = 'test2';

In my audit table same result will be inserted as above (COL1, COL2).

How can I alter my trigger so only updated column (COL2) will be inserted? I need some kind of statement that will check value of column before updating.

My trigger is too big to put all of it here, so I will only put part of the code that returns columns updated.

SELECT  @idTable = T.id 
FROM sysobjects P JOIN sysobjects T ON P.parent_obj = T.id 
WHERE P.id = @@procid

SELECT @Columns_Updated = ISNULL(@Columns_Updated + ', ', '') + name 
    FROM syscolumns t
    WHERE id = @idTable
    AND CONVERT(VARBINARY,REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT, 2),  colorder - 1) > 0

This is original post from where I have taken the code: SQL Server Update Trigger, Get Only modified fields

Upvotes: 0

Views: 797

Answers (1)

Dave C
Dave C

Reputation: 7392

Are you able to prevent updates if the values are identical? This would be the best approach, and allow you to use the same code you have in your trigger (not to mention far more efficient from an i/o perspective).

If the front end driving the updates is a UI, then I would drop a logging class in it to record before/after data using that.

Failing that, I think (someone else might correct me here) you'd need to use CDC (change data capture) to compare old/new values.

Upvotes: 0

Related Questions