Best way to implement an audit trail in SQL Server?

Question

I don't know if these requirements are standard or not but I'm wondering is there a solution out there which can do the following:

• For a specified set of tables keep a copy of a record prior to changing it in a audit version of the relevant table.

I rather not have to code this for every table. I'm wondering if there is a solution you can install on top of SQL Server which will do this for you?

Answer 1

There are many ways to do that; it depends which version of SQL Server you are using.

Here are few

• Audit trail with shadow table and trigger Here is the link

• Also you can consider to use SQL Server 2008 Audit feature Here is the link

Answer 2

I create trigger which does it for XML this way we can log all tables to same table, making it more flexible

CREATE TABLE [dbo].[AuditAll] (
    AuditId    int           NOT NULL IDENTITY(1,1),
    [DateTime] datetime      NOT NULL,
    TableName  nvarchar(255) NOT NULL,
    AuditEntry xml           NULL,

    CONSTRAINT [PK_AuditAll] PRIMARY KEY CLUSTERED ( AuditId ASC )
)

I needed only 'old' values, so I store deleted table only, inserted table can be seen in the table anyhow.

CREATE TRIGGER AuditSimple 
    ON Simple
    AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF (SELECT COUNT(*) FROM deleted) > 0 
begin
    Declare @AuditMessage XML
    --set valut to all xml from deleted table
    set @AuditMessage = (select * from deleted for xml auto) 

    insert into AuditAll( DateTime, TableName, AuditEntry ) 
        values ( GetDate(), 'Simple', @AuditMessage )
end

END
GO

I guess this could easily be called in sp_foreach to create it for each table in datatabase but we did not needed it at the moment, just remember to change your table names

cheers

Answer 3

Database tables

Let's say we have a Book table whose audit log information has to be stored in a BookAuditLog table, as illustrated by the following class diagram:

The BookAuditLog table is created like this:

CREATE TABLE BookAuditLog (
    BookId bigint NOT NULL,
    OldRowData nvarchar(1000) CHECK(ISJSON(OldRowData) = 1),
    NewRowData nvarchar(1000) CHECK(ISJSON(NewRowData) = 1),
    DmlType varchar(10) NOT NULL CHECK (DmlType IN ('INSERT', 'UPDATE', 'DELETE')),
    DmlTimestamp datetime NOT NULL,
    DmlCreatedBy varchar(255) NOT NULL,
    TrxTimestamp datetime NOT NULL,
    PRIMARY KEY (BookId, DmlType, DmlTimestamp)
) 

The BookAuditLog table columns store the following data:

• The BookId column stores the identifier of the associated Book row that this log event was created for.
• The OldRowData stores the JSON representation of Book record state prior to executing an INSERT, UPDATE, or DELETE statement.
• The NewRowData stores the JSON representation of Book record state after an INSERT, UPDATE, or DELETE statement is executed.
• The DmlType is an enumeration column that stores the DML statement type that created, updated, or deleted a given Book row.
• The DmlTimestamp stores the DML statement execution timestamp.
• The DmlCreatedBy stores the user who issued the INSERT, UPDATE, or DELETE DML statement.
• The TrxTimestamp stores the timestamp of the transaction that changed the Book record.

The INSERT, UPDATE and DELETE triggers

To capture the INSERT, UPDATE, and DELETE DML statements, we need to create three database triggers that are going to insert records in the BookAuditLog table.

To intercept the INSERT statements on the Book table, we will create the TR_Book_Insert_AuditLog trigger:

CREATE TRIGGER TR_Book_Insert_AuditLog ON Book
FOR INSERT AS 
BEGIN
    DECLARE @loggedUser varchar(255)
    SELECT @loggedUser = CAST(SESSION_CONTEXT(N'loggedUser') AS varchar(255))
    
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
    
    INSERT INTO BookAuditLog (
        BookId,
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Inserted),
        null,
        (SELECT * FROM Inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        'INSERT',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

To capture the UPDATE statements on the Book records, we will create the following TR_Book_Update_AuditLog trigger:

CREATE TRIGGER TR_Book_Update_AuditLog ON Book
FOR UPDATE AS 
BEGIN
    DECLARE @loggedUser varchar(255)
    SELECT @loggedUser = CAST(SESSION_CONTEXT(N'loggedUser') AS varchar(255))
    
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
    
    INSERT INTO BookAuditLog (
        BookId,
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Inserted),
        (SELECT * FROM Deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        (SELECT * FROM Inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        'UPDATE',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

To intercept the DELETE statements on the Book table rows, we will create the following TR_Book_Delete_AuditLog trigger:

CREATE TRIGGER TR_Book_Delete_AuditLog ON Book
FOR DELETE AS 
BEGIN
    DECLARE @loggedUser varchar(255)
    SELECT @loggedUser = CAST(SESSION_CONTEXT(N'loggedUser') AS varchar(255))
    
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
    
    INSERT INTO BookAuditLog (
        BookId,
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Deleted),
        (SELECT * FROM Deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        null,
        'DELETE',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

Demo time

When executing an INSERT statement on the Book table:

INSERT INTO Book (
    Author, 
    PriceInCents, 
    Publisher, 
    Title, 
    Id
)
VALUES (
    'Vlad Mihalcea', 
    3990, 
    'Amazon', 
    'High-Performance Java Persistence 1st edition', 
    1
)

We can see that a record is inserted in the BookAuditLog that captures the INSERT statement that was just executed on the Book table:

| BookId | OldRowData | NewRowData                                                                                                                         | DmlType | DmlTimestamp            | DmlCreatedBy  | TrxTimestamp            |
|--------|------------|------------------------------------------------------------------------------------------------------------------------------------|---------|-------------------------|---------------|-------------------------|
| 1      |            | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | INSERT  | 2020-11-08 08:40:28.343 | Vlad Mihalcea | 2020-11-08 06:40:28.347 |

When updating the Book table row:

UPDATE Book 
SET PriceInCents = 4499 
WHERE Id = 1

We can see that a new record is going to be added to the BookAuditLog by the AFTER UPDATE trigger on the Book table:

| BookId | OldRowData                                                                                                                         | NewRowData                                                                                                                         | DmlType | DmlTimestamp            | DmlCreatedBy  | TrxTimestamp            |
|--------|------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|---------|-------------------------|---------------|-------------------------|
| 1      |                                                                                                                                    | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | INSERT  | 2020-11-08 08:40:28.343 | Vlad Mihalcea | 2020-11-08 06:40:28.347 |
| 1      | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":4499,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | UPDATE  | 2020-11-08 08:43:22.803 | Vlad Mihalcea | 2020-11-08 06:43:22.807 |

When deleting the Book table row:

DELETE FROM Book 
WHERE Id = 1

A new record is added to the BookAuditLog by the AFTER DELETE trigger on the Book table:

| BookId | OldRowData                                                                                                                         | NewRowData                                                                                                                         | DmlType | DmlTimestamp            | DmlCreatedBy  | TrxTimestamp            |
|--------|------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|---------|-------------------------|---------------|-------------------------|
| 1      |                                                                                                                                    | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | INSERT  | 2020-11-08 08:40:28.343 | Vlad Mihalcea | 2020-11-08 06:40:28.347 |
| 1      | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":4499,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | UPDATE  | 2020-11-08 08:43:22.803 | Vlad Mihalcea | 2020-11-08 06:43:22.807 |
| 1      | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":4499,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} |                                                                                                                                    | DELETE  | 2020-11-08 08:44:25.630 | Vlad Mihalcea | 2020-11-08 06:44:25.633 |

Answer 4

You can try out a 3rd party point-and-click trigger based solution such as ApexSQL Audit - an auditing tool for SQL Server databases, which captures data changes that have occurred on a database including the information on who made the change, which objects were affected, when it was made, as well as the information on the SQL login, application and host used to make the change. It stores all captured information in a central repository and exports them in print friendly formats

Disclaimer: I work as a Product Support Engineer at ApexSQL

Answer 5

Have a look at triggers. These can be used to implement something to fit your requirements.

Answer 6

Take a look at this article - Auditing in SQL Server 2008 which beautifully takes advantage of the auditing features already present in SQL Server 2008.

I must also mention that @Microtechie answers points to some great article. Read them and decide which one is more easy to adapt.

Answer 7

I see 3 ways to achieve this:

1. Triggers are the best solution at all.
2. You can implement replication/log shipping for the table or the database which will always be a few mili seconds/seconds old copy of that table/database.
3. Schedule differential backups as per the time duration old copy you need.

With option '2', in case of any problem immediately you can turn off replication/log shipping and get a few seconds previous copy of exact data. With option '3', for example you have differential backup frequency of every 5 minutes, then in case of any problem you can recover 5 minutes old copy of correct data.

Answer 8

With Database Snapshots, you can keep a readonly copy of your data in that instant. Also with your backups of the logs you can restore your information at a specific period of time if necessary.

You can also read information from the log to retrieve the information changed.

The other solution that is not of your preference is to trace the changes using triggers, but it may require to work on each table. You can also enable the Change Data Capture feature to detect changes, this feature also needs to be enable for each table, but it requires less code than the triggers.

Finally, there are third party tools like Apex SQL Trigger that do this job automatically with few clicks and configurations.