SQL Server audit logging using triggers

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

In this article, we are going to see how we can implement an audit logging mechanism using SQL Server database triggers to store both the previous and the current state of a given target table record in JSON column types.

Database tables

First, let’s assume we need to implement a library application that has a Book table whose audit log information is stored in the BookAuditLog table, as illustrated by the following class diagram:

SQL Server audit logging using triggers and JSON

The Book table stores all the books we have in our library, and the BookAuditLog table stores the CDC (Change Data Capture) events that were created for a given Book record he executing an INSERT, UPDATE, or DELETE DML statement.

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 columns store the following info:

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

The BookAuditLog has a composite Primary Key made out of the BookId, DmlType, and DmlTimestamp columns, as a Book row can have multiple associated BookAuditLog records.

SQL Server audit logging 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.

SQL Server AFTER INSERT audit logging trigger

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

The TR_Book_Insert_AuditLog trigger is executed every time a record is inserted in the Book table.

The Inserted virtual table references the record values that just got inserted, so we can use it to extract the Book column values.

Only the NewRowData column is set since this is a new record that got inserted, so it doesn’t have a previous state to be saved in OldRowData column.

The FOR JSON PATH SQL Server feature allows us to create a JSON object from a query result set.

The DmlType column is set to the value of INSERT, and the DmlTimestamp value is set to the CURRENT_TIMESTAMP.

The DmlTimestamp column is set to the value of the @loggedUser SQL Server session variable, that’s set by the application with the currently logged user:

Session session = entityManager.unwrap(Session.class);

Dialect dialect = session.getSessionFactory()
    .unwrap(SessionFactoryImplementor.class)
    .getJdbcServices().getDialect();
    
String loggedUser = ReflectionUtils.invokeMethod(
    dialect,
    "escapeLiteral",
    LoggedUser.get()
);

session.doWork(connection -> {
    update(
        connection,
        String.format(
            "EXEC sys.sp_set_session_context @key = N'loggedUser', @value = N'%s'", 
            loggedUser
        )
    );
});

SQL Server AFTER UPDATE audit logging trigger

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

Every time a Book record is updated, the TR_Book_Update_AuditLog trigger is executed, and a BookAuditLog row will be created to capture both the old and the new state of the modifying Book record.

SQL Server AFTER DELETE audit logging trigger

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

As you can see, only the OldRowData column is set since there is no new record state.

Testing 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 |

Awesome, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Seize the deal! 40% discount. Seize the deal! 40% discount.

Conclusion

Using SQL Server database triggers and JSON columns to capture the old and the new state of the audited table records is a very straightforward way to implement an audit logging mechanism.

However, the database triggers approach is not the only strategy. If you are using Hibernate, a very simple solution is to use Hibernate Envers.

Another option is to use Debezium and extract the CDC events from the SQL Server Transaction Log. This solution can also work asynchronously, therefore adding no overhead to our application transaction response time.

Transactions and Concurrency Control eBook

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.