SQLTeam.com | Weblogs | Forums

Audit Trigger change of one field


#1

I have an Audit Table I created.
CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(128),
OldValue varchar(1000),
NewValue varchar(1000),
UpdateDate datetime DEFAULT (GetDate()),
UserName varchar(128)
)

I'd like to create an audit trigger that watches only one field.

Table name is oeordlin_sql
field name I want to track changes is tax_sched

Not sure how to write the code to get this trigger created.


#2

#3

FWIW in my experience an audit table that logs data changes BY COLUMN, rather than BY ROW, is much less useful - hard to report on etc. and the resulting Audit table is a lot more "bulky"


#4

I have had the same experience on some legacy databases that I had to work with where the audit table was very cleverly done with table name column, changed column name, and whether it was an update or insert, and what the changed value is.

A nightmare to report on!! Now I stick with the KISS principle. Keep It Simple and Sweet.


#5

We had a client who asked us to generate Audit "by column" for all the application tables - I guess it was about 100 tables. We mechanically generated the code (once we figured out what it needed to look like!) so it wasn't hard to do, but the resulting Trigger scripts were absolutely massive, and the size of the Audit tables was horrific (relative to the actual database's core tables).

In our row-based Audit tables, now, we don't even store the "current" value (to save space - its IN the database, after all :slight_smile: ) we just store rows from DELETED when anything is Updated/Deleted.


#6
CREATE TRIGGER oeordlin_sql__trg
ON dbo.oeordlin_sql
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(tax_sched)
BEGIN
    INSERT INTO dbo.Audit (
        Type, TableName, PrimaryKeyField, PrimaryKeyValue,
        FieldName, OldValue, NewValue,
        UpdateDate, UserName )
    SELECT
        'U', 'oeordlin_sql', '<add_pk_name_here>', CAST(<add_pk_name_here> AS varchar(1000)),
        'tax_sched', d.tax_sched, i.tax_sched,
        GETDATE(), ORIGINAL_LOGIN()
    FROM inserted i
    INNER JOIN deleted d ON
        i.$IDENTITY = d.$IDENTITY --or i.pk_column = d.pk_column
    WHERE
        INULL(i.tax_sched, '~') <> ISNULL(d.tax_sched, '~')        
END --IF
GO --end of trigger

#7

Do you think that actually worth including (I note that you have a test on Insert/Deleted values for tax_sched actually being different)

Just that we never bother with the UPDATE(column) test ... but perhaps we should do?!!


#8

UPDATE is a very efficient, single bit check, so it's worth checking it to avoid more involved logic if there's no possibility that tax_sched was affected.