Update Trigger Question
-
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
 
SELECT
SUSER_SNAME(), 'U', GETDATE(), Column1, Column2, FROM
Inserted
-
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 -
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
-
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 -
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.
-
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply