Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Audit table design RE: Audit table design

  • February 17, 2014 at 9:12 pm

    #1689715

    eheraux (2/17/2014)

    Would a separate trigger need to be created for each field we wish to log?

    No. Inside one trigger you can test whether a column was changed by using the UPDATE(fieldname) built-in function. This tests whether that field had a change as a result of the calling INSERT or UPDATE to the table that's involved.

    Oooh, be careful about that. The UPDATE(fieldname) function detects only whether or not the field was part of the triggering action, not whether the value actually changed or not. If you update (for example) a LastName column from "Smith" to "Smith", it will still be registered as a "change" even though the value didn't actually change. You'd need to compare the INSERTED to the DELETED tables to find out if the value actually changed. Of course, you'd use the UPDATE(filedname) function as the "short circuit" to see whether the column needs such a check or not. Shifting gears a bit, you wouldn't even need that if the trigger was doing "whole row" auditing instead of "field level" auditing.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)