August 21, 2006 at 3:10 am
I am building a update trigger for audit, but i will only create the audit record if the actual content of the record is changed.
The record is updated via a odbc update statement where all columns is passed.
My question is now:
Is columns_updated() returning a update flag if the col is included in the update statement or is the update flag set only if the actual value iss changed.
The reason for my question is that it seems to me that both Columns_updated() and updated(colName) is returning true if the col is part of the update ignoring the actaul value of the column.
Henrik Holm
JL-Data
August 21, 2006 at 7:33 am
Hi Henrik,
Both the columns_updated() and update(colName) just check to see if a column is updated (physically). The fact that it has been updated to the same value is irrelevant as far as these checks are concerned - the update has physically occurred so they both evaluate to true.
In short, you're going to have to do this yourself by comparing the before and after values in the deleted and inserted tables respectively.
August 24, 2006 at 2:55 am
Henrik,
I've also found that the update trigger will fire even if no rows are affected by the UPDATE statement.
It's therefore a good idea to check the rowcount of the inserted table first and return out of the trigger if this is 0.
August 24, 2006 at 4:52 am
The case is
UPDATE SomeTable SET SomeCol = Whatever WHERE 1 = 0
That makes the trigger execute, updating any record that does not exist.
It is good to check the INSERTED table in the trigger.
N 56°04'39.16"
E 12°55'05.25"
August 24, 2006 at 5:20 am
Thanks,
is the way to check rowcount this:
if (select count(*) from inserted) > 0 ...
Anyone know a better way to compare ins/del than this:
if (select col1 from inserted) <> (select col1 from deleted)
if (select col2 from inserted) <> (select col2 from deleted)
etc, for all columns in the table
Henrik Holm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply