About COLUMNS_UPDATED and .Net Applications

Hello All,
I am using COLUMNS_UPDATED in my triggers to audit changes on my tables. I
test everything with Query Analyzer and everything looks perfect.
But when I test the triggers with the applications written by our developers
(using Visual Basic or C#) looks like these applications are sending UPDATEs
including all the fields and COLUMNS_UPDATED shows always all the columns of
the record even if only one field was updated. Am I missing something? Or
maybe some recommendation to our developers?
Thanks,
Benjamin Nevarez
SQL Server Database AdministratorCOLUMNS_UPDATED doesn't tell you what data has changed, only what
columns are referenced in an UPDATE. So even if the developers did
things differently, COLUMNS_UPDATED still may not give you what you
want.
Also, depending on how you handle the values returned by
COLUMNS_UPDATED you may experience other problems. The column ordinal
numbers aren't always updated in a consistent manner whenever the table
structure changes.
For these reasons I recommend you avoid COLUMNS_UPDATED. Identify
changed rows and columns by joining the INSERTED and DELETED tables
instead.
David Portas
SQL Server MVP
--|||Well, what I am doing is
1) Assign the field values from inserted and deleted tables to variables
like in
select @.inserted1 = field1, @.inserted2 = field2, ...
select @.deleted1 = field1, @.deleted2 = field2, ...
2) Manually compare the data like in
if (@.inserted1 = @.deleted1)
--do something
The problem here is that I need to do that for every field in every table.
Is there a faster way?
Thanks,
Ben
"David Portas" wrote:

> COLUMNS_UPDATED doesn't tell you what data has changed, only what
> columns are referenced in an UPDATE. So even if the developers did
> things differently, COLUMNS_UPDATED still may not give you what you
> want.
> Also, depending on how you handle the values returned by
> COLUMNS_UPDATED you may experience other problems. The column ordinal
> numbers aren't always updated in a consistent manner whenever the table
> structure changes.
> For these reasons I recommend you avoid COLUMNS_UPDATED. Identify
> changed rows and columns by joining the INSERTED and DELETED tables
> instead.
> --
> David Portas
> SQL Server MVP
> --
>|||On Thu, 3 Mar 2005 10:27:02 -0800, Benjamin Nevarez wrote:

>Well, what I am doing is
>1) Assign the field values from inserted and deleted tables to variables
>like in
> select @.inserted1 = field1, @.inserted2 = field2, ...
> select @.deleted1 = field1, @.deleted2 = field2, ...
>2) Manually compare the data like in
> if (@.inserted1 = @.deleted1)
> --do something
>The problem here is that I need to do that for every field in every table.
Hi Ben,
No, that's not the problem. The main problem is that your trigger will
break on any multi-row update. A trigger gets fired once per execution
of an UPDATE statement, not once per row affected, so the trigger code
should be able to handle one row affected as well as no rows affected or
1000 rows affected.
To find out what data actually changed, you must:
a) Take steps to ensure that the primary key columns of your table are
never changed in an UPDATE statement - these columns are the only way to
match rows from the inserted and deleted pseudo-tables;
b) Use the following FROM and WHERE clause as part of a query that
handles the rows in which the value for Column01 actually changed
(assuming the primary key consists of columns KeyCol01 and KeyCol02)
FROM inserted AS i
INNER JOIN deleted AS d
ON d.KeyCol01 = i.KeyCol01
AND d.KeyCol02 = i.KeyCol02
WHERE i.Column01 <> d.Column01
c) If Column01 allows NULLS, change the WHERE clause to
WHERE COALESCE (NULLIF (i.Column01, d.Column01),
NULLIF (d.Column01, i.Column01)) <> NULL
Of course, you still have to do this for all relevant columns. Yes,
programming sometimes involves lots of tedium.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually I wrote one solution with a simple join ... and NULLIF. This does
everything in one step. The insert is something like this
insert into audittable
select i.pk, nullif(i.col1, d.col1), nullif(i.col2, d.col2), nullif(i.col3,
d.col3), system_user, getdate()
from inserted i inner join deleted d on i.pk = d.pk
Thanks All,
Ben
"Hugo Kornelis" wrote:

> On Thu, 3 Mar 2005 10:27:02 -0800, Benjamin Nevarez wrote:
>
> Hi Ben,
> No, that's not the problem. The main problem is that your trigger will
> break on any multi-row update. A trigger gets fired once per execution
> of an UPDATE statement, not once per row affected, so the trigger code
> should be able to handle one row affected as well as no rows affected or
> 1000 rows affected.
> To find out what data actually changed, you must:
> a) Take steps to ensure that the primary key columns of your table are
> never changed in an UPDATE statement - these columns are the only way to
> match rows from the inserted and deleted pseudo-tables;
> b) Use the following FROM and WHERE clause as part of a query that
> handles the rows in which the value for Column01 actually changed
> (assuming the primary key consists of columns KeyCol01 and KeyCol02)
> FROM inserted AS i
> INNER JOIN deleted AS d
> ON d.KeyCol01 = i.KeyCol01
> AND d.KeyCol02 = i.KeyCol02
> WHERE i.Column01 <> d.Column01
> c) If Column01 allows NULLS, change the WHERE clause to
> WHERE COALESCE (NULLIF (i.Column01, d.Column01),
> NULLIF (d.Column01, i.Column01)) <> NULL
>
> Of course, you still have to do this for all relevant columns. Yes,
> programming sometimes involves lots of tedium.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>