audit /track changes [closed]

Posted on

Question :

I want to track changes on SQL Server 2012, and save it for 5 days.

I want get the time that each insert/update/delete operation occurred on the SQL Server and save the new insert line(on insert operaion)/ old+new value(for update operation) / removed line(for delete operation).

I’ve searched a lot, but I can’t find a solution for it.

Answer :

If you require change tracking across your entire database, you might consider implementing SQL Server Change Tracking.

If you want to track changes to a single table, you might consider rolling your own solution, as in the example I’ve created below:

The sample table we want to track changes made against:

CREATE TABLE dbo.SomeData
(
    ID int NOT NULL
        CONSTRAINT PK_SomeData
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeVal varchar(100) NOT NULL
    , SomeDate datetime NOT NULL
);

The table where we’ll keep track of changes:

CREATE TABLE dbo.SomeData_Changes
(
    ChangeID bigint NOT NULL
        CONSTRAINT PK_SomeData_Changes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ChangeGroup int NOT NULL
    , ChangeDate datetime
        CONSTRAINT DF_SomeData_Changes_ChangeDate
        DEFAULT ((GETDATE()))
    , OpType varchar(1) NOT NULL
    , ID int NOT NULL
    , SomeVal varchar(100) NOT NULL
    , SomeDate datetime NOT NULL
);

The change-tracking infrastructure we need includes a sequence to group changes together:

CREATE SEQUENCE dbo.SomeData_Changes_Group
AS int
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 1000;
GO

This is a trigger that will track all changes made to the SomeData table:

CREATE TRIGGER SomeData_ChangeTracker
ON dbo.SomeData
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Group int;
    SET @Group = NEXT VALUE FOR dbo.SomeData_Changes_Group;

    IF EXISTS (
        SELECT 1
        FROM deleted
        )
    BEGIN
        INSERT INTO dbo.SomeData_Changes (OpType, ChangeGroup, ID, SomeVal, SomeDate)
        SELECT 'D'
            , @Group
            , d.ID
            , d.SomeVal
            , d.SomeDate
        FROM deleted d
    END

    IF EXISTS (
        SELECT 1
        FROM inserted
        )
    BEGIN
        INSERT INTO dbo.SomeData_Changes (OpType, ChangeGroup, ID, SomeVal, SomeDate)
        SELECT 'I'
            , @Group
            , i.ID
            , i.SomeVal
            , i.SomeDate
        FROM inserted i
    END
    PRINT N'In trigger';
END
GO

Here, we test the change tracker:

INSERT INTO dbo.SomeData (SomeVal, SomeDate)
VALUES ('test row 1', GETDATE());

SELECT *
FROM dbo.SomeData_Changes;
╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║       ChangeDate        ║ OpType ║ ID ║  SomeVal   ║        SomeDate         ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║        1 ║           1 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

An update:

UPDATE dbo.SomeData
SET SomeVal = 'Test Row 1'
WHERE ID = 1;

SELECT *
FROM dbo.SomeData_Changes;
╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║       ChangeDate        ║ OpType ║ ID ║  SomeVal   ║        SomeDate         ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║        1 ║           1 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        2 ║           2 ║ 2018-03-02 10:17:21.710 ║ D      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        3 ║           2 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ Test Row 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

The above output shows ChangeGroup “2”, which is the update. It consists of both a “delete” and an “insert”, which is how many table updates are actually accomplished behind the scenes in SQL Server.

A delete:

DELETE 
FROM dbo.SomeData
WHERE ID = 1;

SELECT *
FROM dbo.SomeData_Changes;
╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║       ChangeDate        ║ OpType ║ ID ║  SomeVal   ║        SomeDate         ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║        1 ║           1 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        2 ║           2 ║ 2018-03-02 10:17:21.710 ║ D      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        3 ║           2 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ Test Row 1 ║ 2018-03-02 10:17:21.703 ║
║        4 ║           3 ║ 2018-03-02 10:17:21.713 ║ D      ║  1 ║ Test Row 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

The delete is ChangeGroup 3 above.

Cleanup:

IF OBJECT_ID(N'dbo.SomeData', N'U') IS NOT NULL
BEGIN
    DROP TRIGGER SomeData_ChangeTracker;
    DROP TABLE dbo.SomeData;
END
IF OBJECT_ID(N'dbo.SomeData_Changes', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.SomeData_Changes;
    DROP SEQUENCE dbo.SomeData_Changes_Group;
END
GO

You could trim rows from the change-tracking table using a SQL Server Agent Job that performs the following every day at midnight:

DELETE 
FROM dbo.SomeData_Changes
WHERE dbo.SomeData_Changes.ChangeDate < DATEADD(DAY, -5, GETDATE());

Community wiki answer:

You can use SQL Server Database Audit.

All editions of SQL Server support server level audits. Database level auditing is limited to Enterprise, Developer, and Evaluation editions. For more information, see Features Supported by the Editions of SQL Server 2012.

For more modern versions:

All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.

Those pages in the documentation are an overview. The left-side navigation links to further information, and there are links in the page as well to more details.

Leave a Reply

Your email address will not be published. Required fields are marked *