Hi,

I'm new to Triggers, so forgive the elementary question. We have a
process that saves data from a VB2005 form into a table in MS SQL
2005, and it saves all data whether the user changed the value or
not. Is it possible to create a trigger for this table where a log
can be written to if a value is updated with different data? Since
technically all data in the table is being updated I wasn't sure if a
trigger could distinguish between data that didn't change and data
that did.

Thanks --

Alex

Alex,

No, since every column is being updated, the UPDATE ( ) and
COLUMNS_UPDATED( ) functions will not work for you.

You can write code to test the contents of the 'deleted' pseudo table (the
image before the update) with the 'inserted' pseudo table (the image after
the update). Where the contents do not match, you can write the 'inserted'
table value.

However, in order to joint the deleted and inserted table successfully,
there needs to be an unchanging unique value, ideally an unchanging primary
key. Without that you will not be able to say which inserted row matches
which deleted row.

(Also, please note that the trigger fires once whether you insert 1 row or
1000 rows, so the inserted and deleted tables will not just have 1 row in
them all of the time. Your trigger should deal with those cases as well.)

For each auditable column, you can have something like this:

INSERT INTO AuditTable (ColumnName, PKValue, Value)
SELECT 'FirstName', i.PKValue, i.FirstName
FROM inserted i JOIN deleted d
ON i.PkValue = d.PKValue

Or you can use the metadata in sys.columns to be more dynamic in the code.

RLF

"Alex" <samalex@gmail.com> wrote in message
news:4d5908a7-0e24-4815-9f1c-bb4147be315e@d45g2000hsc.googlegroups.com...
> Hi,
>
> I'm new to Triggers, so forgive the elementary question. We have a
> process that saves data from a VB2005 form into a table in MS SQL
> 2005, and it saves all data whether the user changed the value or
> not. Is it possible to create a trigger for this table where a log
> can be written to if a value is updated with different data? Since
> technically all data in the table is being updated I wasn't sure if a
> trigger could distinguish between data that didn't change and data
> that did.
>
> Thanks --
>
> Alex

>> Is it possible to create a trigger for this table where a log can be written to if a value is updated with different data? <<

Read this before you make this design error and get in trouble with
the auditors:

http://www.tdan.com/view-special-features/8135