Database tables
Let's assume we have a library application that has the following two tables:
![MySQL audit logging using triggers and JSON columns]()
The best way to store the old and new row state is to use JSON columns. So, for each table that you want to enable audit logging, you can create an audit log table, like this one:
CREATE TABLE book_audit_log (
book_id BIGINT NOT NULL,
old_row_data JSON,
new_row_data JSON,
dml_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
dml_timestamp TIMESTAMP NOT NULL,
dml_created_by VARCHAR(255) NOT NULL,
PRIMARY KEY (book_id, dml_type, dml_timestamp)
)
- The
book_id column stores the identifier of the book row that has been either created, updated, or deleted.
- The
old_row_data is a JSON column that will capture the state of the book record prior to executing an INSERT, UPDATE, or DELETE statement.
- The
new_row_data is a JSON column that will capture the state of the book record after executing an INSERT, UPDATE, or DELETE statement.
- The
dml_type is an enumeration column that stores the DML statement type that created, updated, or deleted a given book record.
- The
dml_timestamp stores the DML statement execution timestamp.
- The
dml_created_by stores the application user who issued the INSERT, UPDATE, or DELETE DML statement.
Intercepting INSERT, UPDATE, and DELETE DML statements using triggers
Now, to feed the audit log tables, you need to create the following 3 triggers:
CREATE TRIGGER book_insert_audit_trigger
AFTER INSERT ON book FOR EACH ROW
BEGIN
INSERT INTO book_audit_log (
book_id,
old_row_data,
new_row_data,
dml_type,
dml_timestamp,
dml_created_by
)
VALUES(
NEW.id,
null,
JSON_OBJECT(
"title", NEW.title,
"author", NEW.author,
"price_in_cents", NEW.price_in_cents,
"publisher", NEW.publisher
),
'INSERT',
CURRENT_TIMESTAMP,
@logged_user
);
END
CREATE TRIGGER book_update_audit_trigger
AFTER UPDATE ON book FOR EACH ROW
BEGIN
INSERT INTO book_audit_log (
book_id,
old_row_data,
new_row_data,
dml_type,
dml_timestamp,
dml_created_by
)
VALUES(
NEW.id,
JSON_OBJECT(
"title", OLD.title,
"author", OLD.author,
"price_in_cents", OLD.price_in_cents,
"publisher", OLD.publisher
),
JSON_OBJECT(
"title", NEW.title,
"author", NEW.author,
"price_in_cents", NEW.price_in_cents,
"publisher", NEW.publisher
),
'UPDATE',
CURRENT_TIMESTAMP,
@logged_user
);
END
CREATE TRIGGER book_delete_audit_trigger
AFTER DELETE ON book FOR EACH ROW
BEGIN
INSERT INTO book_audit_log (
book_id,
old_row_data,
new_row_data,
dml_type,
dml_timestamp,
dml_created_by
)
VALUES(
OLD.id,
JSON_OBJECT(
"title", OLD.title,
"author", OLD.author,
"price_in_cents", OLD.price_in_cents,
"publisher", OLD.publisher
),
null,
'DELETE',
CURRENT_TIMESTAMP,
@logged_user
);
END
The JSON_OBJECT MySQL function allows us to create a JSON object that takes the provided key-value pairs.
The dml_type column is set to the value of INSERT, UPDATE or DELETE and the dml_timestamp value is set to the CURRENT_TIMESTAMP.
The dml_created_by column is set to the value of the @logged_user MySQL session variable, which was previously set by the application with the currently logged user:
Session session = entityManager.unwrap(Session.class);
Dialect dialect = session.getSessionFactory()
.unwrap(SessionFactoryImplementor.class)
.getJdbcServices()
.getDialect();
session.doWork(connection -> {
update(
connection,
String.format(
"SET @logged_user = '%s'",
ReflectionUtils.invokeMethod(
dialect,
"escapeLiteral",
LoggedUser.get()
)
)
);
});
Testing time
When executing an INSERT statement on the book table:
INSERT INTO book (
id,
author,
price_in_cents,
publisher,
title
)
VALUES (
1,
'Vlad Mihalcea',
3990,
'Amazon',
'High-Performance Java Persistence 1st edition'
)
We can see that a record is inserted in the book_audit_log that captures the INSERT statement that was just executed on the book table:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by |
|---------|--------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-07-29 13:40:15 | Vlad Mihalcea |
When updating the book table row:
UPDATE book
SET price_in_cents = 4499
WHERE id = 1
We can see that a new record is going to be added to the book_audit_log by the AFTER UPDATE trigger on the book table:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by |
|---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-07-29 13:40:15 | Vlad Mihalcea |
| 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE | 2020-07-29 13:50:48 | Vlad Mihalcea |
When deleting the book table row:
DELETE FROM book
WHERE id = 1
A new record is added to the book_audit_log by the AFTER DELETE trigger on the book table:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by |
|---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-07-29 13:40:15 | Vlad Mihalcea |
| 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE | 2020-07-29 13:50:48 | Vlad Mihalcea |
| 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | | DELETE | 2020-07-29 14:05:33 | Vlad Mihalcea |
That's it!
