Triggers on MS SQL Tables to create an Audit Trail

Posted on 2005-05-02
Last Modified: 2013-01-22
I am trying to create an audit trail for my database and I need some direction.

I have a database that is used for job scheduling.  The concept of a job is pretty complex (lots of variables) and was thus split into about 27 different tables that all tie together with a combination of JOB_ID and TABLE_ID.  An update to a job might be run against any 1 or more of those 27 tables depending on what needs to be changed.

I need to be able to track these changes.  I need to create an audit reporting table that contains:
Date of change
Job being changed (JOBNAME column of the DEF_JOB table where JOB_ID and TABLE_ID of the changed value match the DEF_JOB's JOB_ID and TABLE_ID - this links the master job table (one of the 27 tables that make up a job) to whatever was changed)
User making the change
Table/Field changed
Old Value
New Value.

So my trigger needs to be able to write all that information back to the audit table.  Does ANYBODY know how I can do this, or even if I can?  I can supply the schema if it helps.

Question by:JRamos1200
    LVL 30

    Accepted Solution

    Create a table to contain your log, with fields as you have specified.

    Date of change - Default should be GETDATE()
    Job being changed - Set from table trigger
    User - Default should be SYSTEM_USER
    Table/Field changed - Set from table trigger
    Old Value - Set from table trigger
    New Value - Set from table trigger

    Create an UPDATE trigger on each table.

    Within these triggers insert the record into your log table.

    Be aware of the following:

    -SYSTEM_USER returns the SQL Server login so if everyone is logging in as the same thing, this is no good.
    -If multiple rows are updated, the update trigger will be called ONCE for all rows

    You may need more detail that this so let me know if you need help.
    LVL 34

    Assisted Solution

    Nigel has some really good generic triggers on his site that will give you a pretty good direction on what you need to write for triggers:


    Author Comment

    nmcdermaid ,

    Indeed I would like more information.  I have never created a trigger before so have no understnading of the syntax of the trigger itself, or of how to pull the info out of the "trigger table" what ever that is!


    Author Comment


    Thanks for the link.  I am reading through it.
    LVL 30

    Expert Comment

    If you look up 'CREATE TRIGGER' in books online it'll give you some dry (but thorough) explanations.

    The link will probably have the whole thing explained.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Split the points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now