This article will explain how to implement an audit trail for MySQL table using Triggers.

Maintaining an audit trail of data changes is considered to be the standard minimum requirement for any enterprise application also a legal requirement for many domains such as banking and cybersecurity. Database table level audit trails will help us to investigate most of the application issues such as unauthorized access, problematic configuration changes, and many more.

An audit trail should help us to answer these basic questions when doing an investigation,

• What data changed?
• When did it change?
• Who changed it?

Using MySQL database triggers will provide some performance advantage since all the audit trail related logic will be executed within the database and there will be fewer code changes required in the application layer.

CREATING SAMPLE TABLE FOR DEMONSTRATION

We are going to implement an audit trail for a sample table called “Persons”. Here’s the DDL statement to create the same,

CREATE TABLE persons (Personid int NOT NULL AUTO_INCREMENT,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
Age int,
is_deleted int NOT NULL DEFAULT 0,
created_by  varchar(255) NOT NULL,
updated_by  varchar(255) NOT NULL,
PRIMARY KEY (Personid));

As you can see above, we should have the following 3 columns in every table for which we need to implement an audit trail

• created_by: username who created the record (Usually take from the application session)
• updated_by: username who last updated the record. (Usually take from the application session)
• is_deleted: 1 or 0. 1 represents the row is deleted. (Always its best practice to soft delete the rows instead of permanently deleting)

CREATING A TABLE TO STORE AUDIT TRAIL DATA

Here’s the DDL statement to create the audit trail table for “persons” table created above,

create table persons_audit_trail(id int NOT NULL AUTO_INCREMENT, 
Personid int NOT NULL,
column_name varchar(255),
old_value varchar(255),
new_value varchar(255),
done_by varchar(255) NOT NULL,
done_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id));

Following is the description of columns,

• Personid: Primary key value of the “persons” table for which we are tracking the changes. We can track the changes for each record using this id.
• column_name: Name of the table column for which the value is created or modified.
• old_value: Value of the column before the modification. (This will be “NULL” if the record is newly created)
• new_value: Value of a column after the modification
• done_by: username who created, updated, or deleted.
• done_at: the current system time

CREATE INSERT TRIGGER

This trigger will be executed whenever a record is created in the “persons” table. This will insert the values into the “persons_audit_trail” table after creating a record in the “persons” table.

DELIMITER $$
CREATE TRIGGER persons_create
AFTER INSERT
ON persons FOR EACH ROW
BEGIN
    insert into persons_audit_trail(Personid, column_name, new_value, done_by) values(NEW.Personid,'FirstName',NEW.FirstName,NEW.created_by);
	insert into persons_audit_trail(Personid, column_name, new_value, done_by) values(NEW.Personid,'LastName',NEW.LastName,NEW.created_by);
	insert into persons_audit_trail(Personid, column_name, new_value, done_by) values(NEW.Personid,'Age',NEW.Age,NEW.created_by);
	
END$$
DELIMITER ;

CREATE UPDATE TRIGGER

This trigger will be executed whenever a record is updated in the “persons” table. This will insert the values into the “persons_audit_trail” table after updating a record in the “persons” table. As you can see below we will insert entries only for the columns for which the value is changed.

DELIMITER $$
CREATE TRIGGER persons_update
AFTER UPDATE
ON persons FOR EACH ROW
BEGIN
    IF OLD.FirstName <> new.FirstName THEN
        insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'FirstName',OLD.FirstName,NEW.FirstName,NEW.updated_by);
    END IF;
	IF OLD.LastName <> new.LastName THEN
        insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'LastName',OLD.LastName,NEW.LastName,NEW.updated_by);
    END IF;
	IF OLD.Age <> new.Age THEN
        insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'Age',OLD.Age,NEW.Age,NEW.updated_by);
    END IF;
	IF OLD.is_deleted <> new.is_deleted THEN
        insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'is_deleted',OLD.is_deleted,NEW.is_deleted,NEW.updated_by);
    END IF;
END$$
DELIMITER ;

TESTING THE AUDIT TRAIL

Now we can test the audit trail by inserting, updating, and deleting a row from the “persons” table.

First, let’s insert a new record by user “test_user1”,

insert into persons(FirstName, LastName, Age, created_by, updated_by) values('Rajesh','Kumar',37,'test_user1','test_user1');

Now let’s update the age by user “test_user2”,

update persons set Age=20, updated_by='test_user2' where Personid=1;

Update the LastName by user “test_user1”,

update persons set LastName='AAA', updated_by='test_user1' where Personid=1;

Lastly, we will delete the record by updating “is_deleted” to 1 by user “test_user3”,

update persons set is_deleted=1,updated_by='test_user3' where Personid=1;

We can query the audit trail table to see whether all the data changes are captured,

select * from persons_audit_trail where Personid=1;

As we can see below all the insert, update and delete events are captured and we can see who did it at what time,

Follow Me

• 
• 
•