Another Audit Trigger
-
This is a trigger to record Updates to a table that I modified from O’Reilly’s “Transact-SQL Cookbook”. The goal is to save space in the audit table by only storing values that have actually changed. When I tried the original version, I discovered that the trigger would not record the value of a column that was updated from a Null value to an actual value. I then added some additional logic which records the changed value regardless of whether the original value was a Null value or an actual value.
CREATE TRIGGER AuditUpdate
ON [Table]
FOR UPDATE
AS
BEGIN
INSERT INTO AuditTrail(TableName, Action, NetUserID, Node, ActionDate, EventID, [Column_Name])
SELECT 'TableName', 'O', system_user, host_name(), current_timestamp, newid(),
(CASE WHEN d.[ColumnName]!=i.[ColumnName] THEN d.[ColumnName] ELSE NULL END)
FROM deleted d, inserted i
WHERE d.[ColumnName]=i.[ColumnName]
/* 'O' indicates this row is storing Old values.*/
INSERT INTO AuditTrail(TableName, Action, NetUserID, Node, ActionDate, EventID, [Column_Name])
SELECT 'TableName', 'N', system_user, host_name(), current_timestamp, newid(),
/* Here is the logic which overcame the problem of not recording a change from a Null value to an actual value */
(CASE WHEN d.[ColumnName] IS NULL THEN i.[ColumnName] WHEN d.[ColumnName]!=i.[ColumnName] THEN i.[ColumnName] ELSE NULL END)
FROM deleted d, inserted i
WHERE d.[ColumnName]=i.[ColumnName]
/* 'N' indicates this row is storing New values.*/
END
/* The original logic was as follows: */
/*
(CASE WHEN d.[ColumnName]!=i.[ColumnName] THEN i.[ColumnName] ELSE NULL END)
*/
“If you're not outraged at the media, you haven't been paying attention.”
-
Cool.
-
Here is what you actually need to be using in a trigger:
IF UPDATED( ColumnName ) ...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply