MYSQL TABLE AUDIT TRAIL USING TRIGGERS

PLEASE SHARE

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

8 Comments

  1. I tried it works very well
    Thank you so much
    But I have a question
    I want to show audit trails on a page

    Sample:
    Adam xxx deleted the product.
    I understand that with “is_deleted” the row is deleted

    How can I specify the following two processes
    Adam xxx updated the product
    Adam added xxx product

    1. Thank You, Adem.
      The following query can be used for your purpose.
      (SELECT done_by, Personid,’INSERTED’,done_at FROM persons_audit_trail WHERE old_value IS NULL AND new_value IS not NULL GROUP BY done_at) UNION
      (SELECT done_by, Personid,’UPDATED’,done_at FROM persons_audit_trail WHERE old_value IS NOT null AND new_value IS not NULL and COLUMN_NAME <> ‘is_deleted’ GROUP BY done_at) UNION
      (SELECT done_by, Personid,’DELETED’,done_at FROM persons_audit_trail WHERE COLUMN_NAME=’is_deleted’ AND old_value=’0′ AND new_value=’1′);

      The output will be similar to this. This data can be displayed as per the format required by you with your programming language.
      done_by;Personid;INSERTED;done_at
      test_user1;1;INSERTED;2022-01-17 00:59:09
      test_user2;1;UPDATED;2022-01-17 00:59:21
      test_user1;1;UPDATED;2022-01-17 00:59:31
      test_user3;1;DELETED;2022-01-17 00:59:43

      1. Thank you so much
        Great idea it worked for me

        Suppose you have too many columns in a table.
        Is there more than one time log on trigger?
        Example table “aaa”
        columns
        1111=> 2022-01-14 08:26:58
        2222=> 2022-01-14 08:26:58
        3333=> 2022-01-14 08:26:58
        4444=> 2022-01-14 08:27:58
        101010=> 2022-01-14 08:26:59
        303030=> 2022-01-14 08:27:01
        909090=> 2022-01-14 08:27:01
        Is such a situation possible?
        If so, what solution would you suggest?

        1. The above query prints only one entry per insert/update, even with multiple column entries. Please note there is a “GROUP BY done_at” in the query, which makes sure to group all the columns inserted or updated simultaneously. Thanks

          1. Thank you for the answer
            When listing audit trails in a table I want to get them into a group with rowspan.
            Therefore, I will assume that transactions with the same date belong to a table.
            If it can be a different date, it will cause me to exclude that record.
            Let’s say you have 10 columns in a table, and when I add rows, can there be different times when the operation on these 10 rows is done?
            “done_at”
            2022-01-14 08:26:59
            2022-01-14 08:26:59
            2022-01-14 08:26:59
            2022-01-14 08:26:59
            2022-01-14 08:26:59
            2022-01-14 08:26:59
            2022-01-14 08:26:59
            2022-01-14 08:26:59

  2. Hello again,
    I tried to make some changes to not use if conditions to determine what was done and to list audit trails.
    Column name for processes performed: “process”

    For insert:
    $ftvtk->bindValue(‘:process’, ‘Added’, PDO::PARAM_STR);

    For update:
    $ftvtk->bindValue(‘:process’, ‘Updated’, PDO::PARAM_STR);

    For deletion
    Audit trail is updating before triggering
    $ftvtk->bindValue(‘:process’, ‘Deleted ‘, PDO::PARAM_STR);

    Then it deletes
    DELETE FROM urunler WHERE id=? LIMIT 1

    My question is this,
    What kind of condition do I need to write in order not to save the “process” column to audit trail when the “Added” data in the “process” column is changed to “Updated” data in the normal update process?
    Note: I want it to save the “process” column to the audit trail table only when it is changed to “Deleted”

    IF OLD.process new.process THEN
    As follows
    IF NEW.process == ‘Deleted’ THEN

  3. Thank you Rajesh. I spent more hours to find the solution to implement AUDIT in mysql table. I tried your code and works well. I have question on this.
    I have a table contain 74 editable fields . Do i need to use 74 IF conditions to update audit table?

    Thank You

Leave a Reply

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

41 + = 42