Audit logging mechanism using the mysql server

Introduction

we can implement an audit logging mechanism using SQL Server database triggers to store the previous and the current state of the given target table record in JSON column types.
 

Database tables

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

SQL Server audit logging using the triggers and JSON

Book table stores all books we have in library, and the BookAuditLog table stores the CDC (Change Data Capture) events that were created for a the given Book record,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 :

The BookId column stores the identifier of the row book.
The OldRowData is JSON column storing the state of Book record prior to executing the statement.
The NewRowData is  JSON column storing the state of the Book record after  executed the statement.
The DmlType is a column storing the DML statement type.
The DmlTimestamp stores the execution timestamp.
The DmlCreatedBy stores  user.
The TrxTimestamp stores transaction timestamp.
The BookAuditLog has composite Primary Key made from the BookId,type, and timestamp columns, as  Book row can have the multiple associated BookAuditLog records.

SQL Server audit the logging triggers

To audit the DML statements, we need to create  database triggers that  insert records in the BookAuditLog table.

SQL Server AFTER INSERT trigger

To audit the INSERT statements on  Book table, we will create  TR_BookInsert_AuditLog trigger:

CREATE TRIGGER TR_BookInsert_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_BookInsert_AuditLog trigger  executed each time  record is inserted in the Book table.

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

Just NewRowData section is set since this is new record that embedded, it doesn't have past state to be saved in OldRowData segment.

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

The DmlType section is set estimation of INSERT, and the DmlTimestamp esteem is set CURRENT_TIMESTAMP.

The DmlTimestamp segment is set estimation of the @loggedUser SQL Server meeting variable, that is set by application with the current client:
 

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 trigger

To catch the UPDATE explanations on the Book records, we will make the accompanying 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

Each time a Book record is refreshed, the TR_Book_Update_AuditLog trigger is executed, and a BookAuditLog line will be made to catch both the old and the new condition of the changing Book record. 

SQL Server AFTER DELETE review logging trigger 

To capture the DELETE articulations on the Book table lines, we will make the accompanying TR_Book_Delete_AuditLog trigger:ntercept 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 should be obvious, just the OldRowData section is set since there is no new record state.

Testing
When executing an INSERT statement:

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

A record  inserted in the BookAuditLog captures the INSERT statement :

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

When updating the Book table row:

UPDATE Book
SET PriceInCents = 4499
WHERE Id = 1

A new record is going  added to  BookAuditLog   AFTER UPDATE trigger :

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

When deleting the Book table row:

DELETE FROM Book
WHERE Id = 1

A new record added to  BookAuditLog by the AFTER DELETE trigger :

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