Update Trigger Question

Question

Post reply

Update Trigger Question

John-79567

SSCommitted

Points: 1892
More actions
July 26, 2005 at 2:01 pm

#186715

How do I capture the column names of the updated fields? I can trap the user id, but i cant seem to figure out the syntax to capture which columns are being updated.
Here's an example of my trigger:
CREATE TRIGGER Audit
ON dbo.myTable
FOR INSERT, UPDATE, DELETE
AS
IF (SELECT COUNT(*) FROM Inserted) > 0
BEGIN
  IF (SELECT COUNT(*) FROM Deleted) > 0
  BEGIN
   INSERT Audit.DBO.audit_myTable
    (
     auditUserID, auditOperation, auditDate, Column1, Column2
    &nbsp
    SELECT
     SUSER_SNAME(), 'U', GETDATE(), Column1, Column2,     FROM
     Inserted

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

the syntax to tell if a column was updated is:

IF update(Column_Name)

but I don't understand what are you trying to accomplish?

* Noel

John-79567 SSCommitted Points: 1892 More actions · Answer 2

I am trying to create an audit table to capture insert, updates, and deletes. So far, i have it working with the exception of listing the names of the change fields. I would like to insert the names of the fields being updated into a column. This column will be used as a reference point when researching updates.

For example:

History_MyTable

ID UserID ModifiedDate ModifiedColumns Column1 Column2

1 Guest 1/1/2005 Column1 Blah Doh

1 Guest 1/2/2005 Column1, Column2 BlahBlah Blah

Original Table - MyTable

ID Column1 Column2

1 Doh Doh

noeld SSC Guru Points: 96590 More actions · Answer 3

That Design is not very consistent in the sense of hadling "multiple columns" updates.

The two most used mechanisms are:

1.Simply insert the deleted and the Inserted rows with "OLD" and "NEW" indicators on a table that mirrors the same structure than the audited one

2. Create ONE audit table for ALL tables and create columns for PK,TableName,ColName, OldValue and NEWValue and on the Trigger generate one insert statement per column or use the union all with one select statement per column

each has its pros and cons ....

Up to you but usually the first approach is good enough

* Noel

Ray M SSC-Insane Points: 21093 More actions · Answer 4

To add onto noeld's comment,

You really want to keep auditing as fast as possible, creating a solution as you requested would cause the transaction to be open longer causing alot of contention issues in the table. which will effect performance.

Pseudo code.

Inserts: Insert into Mytable_Audit, Select from Inserted

Updates Insert into Mytable_audit, Select from Deleted

Deletes Insert into Mytable_audit, Select from Deleted

so all rows from audit table unioned with row from Main table will give you the entire history of the record.

Then make the old field new field functionality a function of the presentation layer.

John-79567 SSCommitted Points: 1892 More actions · Answer 5

I appreciate the feed back. I think im going to go with the mirrored approach and program the presentation layer to provide old data / new data information. Thanks everyone.