locked
Auditing Design RRS feed

  • Question

  • We need to keep track of row modifications done by a user.

    Can you point me to a sample design?

    Thanks in advance

    Monday, October 24, 2011 10:15 PM

Answers

  • Hi rgelfand,

    There are various alternatives, and different people have different
    preferences. Here is what I usually choose - supposed you have to
    audit changes to the "Customers" table, and this table looks like this:

    CREATE TABLE Customers
        (CustomerID int NOT NULL,
         CustomerName nvarchar(120) NOT NULL,
         DateOfBirth date NOT NULL,
         ShoeSize tinyint,
         PRIMARY KEY (CustomerID)
        );

    (Okay, not very realistic, but I like to keep examples short).

    Here's how my audit table would look:

    CREATE TABLE CustomersAudit
        (CustomerID int NOT NULL,
         MomentOfChange datetime2(7) NOT NULL,
         ChangeType char(1) NOT NULL,
         CustomerName nvarchar(120) NOT NULL,
         DateOfBirth date NOT NULL,
         ShoeSize tinyint,
         PRIMARY KEY (CustomerID, MomentOfChange)
        );

    And then I add these triggers:

    CREATE TRIGGER CustAuditIns
    ON Customers AFTER INSERT
    AS
    BEGIN;
        IF @@ROWCOUNT() = 0 RETURN;
        IF NOT EXIST (SELECT * FROM inserted) RETURN;
        INSERT INTO CustomersAudit
                    (CustomerID, MomentOfChange, ChangeType,
                     CustomerName, DateOfBirth, ShoeSize)
        SELECT CustomerID, SYSDATETIME(), 'I',
                     CustomerName, DateOfBirth, ShoeSize)
        FROM     inserted;
    END;
    CREATE TRIGGER CustAuditUpd
    ON Customers AFTER UPDATE
    AS
    BEGIN;
        IF @@ROWCOUNT() = 0 RETURN;
        IF NOT EXIST (SELECT * FROM inserted) RETURN;
        INSERT INTO CustomersAudit
                    (CustomerID, MomentOfChange, ChangeType,
                     CustomerName, DateOfBirth, ShoeSize)
        SELECT CustomerID, SYSDATETIME(), 'U',
                     CustomerName, DateOfBirth, ShoeSize)
        FROM     inserted;
    END;
    CREATE TRIGGER CustAuditDel
    ON Customers AFTER DELETE
    AS
    BEGIN;
        IF @@ROWCOUNT() = 0 RETURN;
        IF NOT EXIST (SELECT * FROM inserted) RETURN;
        INSERT INTO CustomersAudit
                    (CustomerID, MomentOfChange, ChangeType,
                     CustomerName, DateOfBirth, ShoeSize)
        SELECT CustomerID, SYSDATETIME(), 'D',
                     CustomerName, DateOfBirth, ShoeSize)
        FROM     deleted;
    END;

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Peja TaoEditor Wednesday, October 26, 2011 5:04 AM
    • Marked as answer by Peja TaoEditor Monday, October 31, 2011 1:40 AM
    Monday, October 24, 2011 10:31 PM
  • Hi Which is the version of SQL Server you are using.

    If you are using SQL Server 2008 then probably u can use CDC (Change Data Capture)

    If not then above method suggested by Hugo would be the best one i feel.


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com
    • Proposed as answer by Peja TaoEditor Wednesday, October 26, 2011 5:04 AM
    • Marked as answer by Peja TaoEditor Monday, October 31, 2011 1:40 AM
    Tuesday, October 25, 2011 7:12 AM

All replies

  • Hi rgelfand,

    There are various alternatives, and different people have different
    preferences. Here is what I usually choose - supposed you have to
    audit changes to the "Customers" table, and this table looks like this:

    CREATE TABLE Customers
        (CustomerID int NOT NULL,
         CustomerName nvarchar(120) NOT NULL,
         DateOfBirth date NOT NULL,
         ShoeSize tinyint,
         PRIMARY KEY (CustomerID)
        );

    (Okay, not very realistic, but I like to keep examples short).

    Here's how my audit table would look:

    CREATE TABLE CustomersAudit
        (CustomerID int NOT NULL,
         MomentOfChange datetime2(7) NOT NULL,
         ChangeType char(1) NOT NULL,
         CustomerName nvarchar(120) NOT NULL,
         DateOfBirth date NOT NULL,
         ShoeSize tinyint,
         PRIMARY KEY (CustomerID, MomentOfChange)
        );

    And then I add these triggers:

    CREATE TRIGGER CustAuditIns
    ON Customers AFTER INSERT
    AS
    BEGIN;
        IF @@ROWCOUNT() = 0 RETURN;
        IF NOT EXIST (SELECT * FROM inserted) RETURN;
        INSERT INTO CustomersAudit
                    (CustomerID, MomentOfChange, ChangeType,
                     CustomerName, DateOfBirth, ShoeSize)
        SELECT CustomerID, SYSDATETIME(), 'I',
                     CustomerName, DateOfBirth, ShoeSize)
        FROM     inserted;
    END;
    CREATE TRIGGER CustAuditUpd
    ON Customers AFTER UPDATE
    AS
    BEGIN;
        IF @@ROWCOUNT() = 0 RETURN;
        IF NOT EXIST (SELECT * FROM inserted) RETURN;
        INSERT INTO CustomersAudit
                    (CustomerID, MomentOfChange, ChangeType,
                     CustomerName, DateOfBirth, ShoeSize)
        SELECT CustomerID, SYSDATETIME(), 'U',
                     CustomerName, DateOfBirth, ShoeSize)
        FROM     inserted;
    END;
    CREATE TRIGGER CustAuditDel
    ON Customers AFTER DELETE
    AS
    BEGIN;
        IF @@ROWCOUNT() = 0 RETURN;
        IF NOT EXIST (SELECT * FROM inserted) RETURN;
        INSERT INTO CustomersAudit
                    (CustomerID, MomentOfChange, ChangeType,
                     CustomerName, DateOfBirth, ShoeSize)
        SELECT CustomerID, SYSDATETIME(), 'D',
                     CustomerName, DateOfBirth, ShoeSize)
        FROM     deleted;
    END;

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Peja TaoEditor Wednesday, October 26, 2011 5:04 AM
    • Marked as answer by Peja TaoEditor Monday, October 31, 2011 1:40 AM
    Monday, October 24, 2011 10:31 PM
  • Hi Which is the version of SQL Server you are using.

    If you are using SQL Server 2008 then probably u can use CDC (Change Data Capture)

    If not then above method suggested by Hugo would be the best one i feel.


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com
    • Proposed as answer by Peja TaoEditor Wednesday, October 26, 2011 5:04 AM
    • Marked as answer by Peja TaoEditor Monday, October 31, 2011 1:40 AM
    Tuesday, October 25, 2011 7:12 AM