Creating an audit table via triggers

[voxinforma]

I would like to create an audit table that would record old and new values whenever data is modified in any table.

My database might look like this:
table1 (table1ID, field1, field2)
table2 (table2ID, field1, field2)

and my audit table like this:
AuditTable (AuditTableID, TableName, FieldName, ID, OldValue, NewValue)

I am trying to use triggers on table1 and table2 via the Deleted and Inserted tables to write OldValue and NewValue, but cannot figure out how to get the approprite TableName. I only want to add the changed values to AutitTable; unchanged values should not be added.

Any help would be appreciated.

Thanks.

 

[tlbroadbent]

Here is a sample trigger for table1. You'll need to create a similar trigger on table2. I have assumed that AuditTableID is an identity column.

CREATE TRIGGER trgUpdateTable1
ON table1
FOR update AS

IF UPDATE(Field1)
BEGIN
-- Insert Field1 Audit record.
INSERT INTO AuditTable
(TableName, FieldName, ID, OldValue, NewValue)
SELECT 'Table1', 'Field1', d.table1ID, d.Field1, i.Field1
FROM deleted d
INNER JOIN inserted i
ON d.table1ID=i.Table1ID
END

IF UPDATE(Field2)
BEGIN
-- Insert Field2 Audit record.
INSERT INTO AuditTable
(TableName, FieldName, ID, OldValue, NewValue)
SELECT 'Table1', 'Field2', d.table1ID, d.Field2, i.Field2
FROM deleted d
INNER JOIN Join inserted i
ON d.table1ID=i.Table1ID
END

GO Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[voxinforma]

Thanks, Terry, and granted that works. But I actually have dozens of tables with hundreds of fields, so writing a separate procedural block for each field seemed too graceless. Is this the best way?

 

[tlbroadbent]

You can use the COLUMNS_UPDATED() function. See SQL BOL for syntax and other considerations.

COLUMNS_UPDATED() function description from BOL:[ul]Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns in the table were inserted or updated.

The COLUMNS_UPDATED function returns the bits in order from left to right, with the least significant bit being the leftmost. The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost. COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

COLUMNS_UPDATED can be used anywhere inside the body of the trigger.[/ul] Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.