SQL Server audit logging using triggers

Last modified:

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!

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:

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()
    
    DECLARE @oldRecord nvarchar(1000)
    DECLARE @newRecord nvarchar(1000)
    
    SET @oldRecord = (SELECT * FROM Deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
    SET @newRecord = (SELECT * FROM Inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
    
    IF @oldRecord != @newRecord
        INSERT INTO BookAuditLog (
            BookId,
            OldRowData,
            NewRowData,
            DmlType,
            DmlTimestamp,
            DmlCreatedBy,
            TrxTimestamp
        )
        VALUES(
            (SELECT id FROM Inserted),
            @oldRecord,
            @newRecord,
            '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 |

If you want to transform the JSON-based audit log object to the initial relational table record, you can use the SQL Server OPENJSON function, as explained in this article.

Awesome, right?

I'm running an online workshop on the 11th of October about High-Performance SQL.

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

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.

Follow @vlad_mihalcea