0

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

Community
  • 1
  • 1
Aco Vujic
  • 67
  • 3
  • 11

1 Answers1

0

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.

Dave C
  • 7,272
  • 1
  • 19
  • 30
  • Thats my problem, I can not prevent updates if values are identical. I do not know how to compare old and new values. OLD.value and NEW.value do not exist in MSSQL as far as I know. I need the equivalent for MSSQL, but I can not find it. – Aco Vujic Feb 25 '14 at 09:35
  • I believe the only way to do this is to turn on CDC, that for sure records the old/new values. CDC records all changes on a specific table, but it's a substantial amount of overhead for what you're trying to accomplish. – Dave C Feb 25 '14 at 15:46