How to create Triggers to add the change events into Audit Log tables

Question

Suppose we have 50 tables in a database and we want to capture all the changes (Previous value and new value of columns) across the columns of each table. An audit table will be there, which will have below columns:

ID, Server_Name, User_Name, Date_Time, Table_Name, Column_Name, Old_Value, New_Value

There will be one audit table which will capture the changes of all the tables from that database. I believe we can create triggers for each of the table of that database. But please let me know how all the data will be added into one audit table. If you can provide me with a working example that will be very helpful.

Thanks and regards, Partha

Answer 1

I can provide you a kind of algorithm to work upon, most of the ground work is already done:

This can be your audit table, should add timestamp column as modified date or more info as per your requirements:

CREATE TABLE audit (
     old_data VARCHAR(100),
     new_data VARCHAR(100),
     tbl_name VARCHAR(100)
)
|

This can be used as a reference trigger; note that there will be a separate trigger for each table:

CREATE TRIGGER testtrigger BEFORE UPDATE ON <table_name>
  FOR EACH ROW BEGIN
    INSERT INTO audit(old_data, new_data, tbl_name) VALUES (OLD.first_name, NEW.first_name, "testtable");
  END;
|

You can have multiple insert statement one for each column. If you want to put a restriction of not inserting the data that is not changed you can do the following change in the trigger:

IF(OLD.column_name <> NEW.column_name) THEN
    --Your insert query here
ELSE
    --NOOP
END IF;

Let know if more information is required.

Answer 2

This is a little bit improved version of Vlad answer. Audit table has column for 'diff' of changes.

Auditing rules:

• INSERT and DELETE - complete record with all fields is stored to audit table
• UPDATE - only changes on fields is stored

Audit table structure. diff is column where changes are stored.

NB: privileges is JSON column on table we are auditing in this case.

CREATE TABLE roles_audit_log (
    roles_id INT UNSIGNED NOT NULL,
    diff JSON,
    dml_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    dml_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    dml_created_by VARCHAR(255) NOT NULL default 'system',
    PRIMARY KEY (roles_id, dml_type, dml_timestamp)
);

Trigger for UPDATE statement. Will fill diff column with fields that were changed.

DELIMITER $$

CREATE TRIGGER roles_audit_au AFTER UPDATE ON `roles` FOR EACH ROW
BEGIN
    DECLARE m_change text;
    SET m_change = JSON_OBJECT();

    IF coalesce(NEW.role, '') != coalesce(OLD.role, '') THEN
        SET m_change = JSON_SET(m_change, '$.role', NEW.role);
    END IF;

    IF coalesce(NEW.created_at, '') != coalesce(OLD.created_at, '') THEN
        SET m_change = JSON_SET(m_change, '$.created_at', NEW.created_at);
    END IF;

    IF coalesce(NEW.updated_at, '') != coalesce(OLD.updated_at, '') THEN
        SET m_change = JSON_SET(m_change, '$.updated_at', NEW.updated_at);
    END IF;

    IF coalesce(NEW.privileges, '') != coalesce(OLD.privileges, '') THEN
        SET m_change = JSON_SET(m_change, '$.privileges', NEW.privileges);
    END IF;

    INSERT INTO `roles_audit_log` (
        roles_id,
        diff,
        dml_type,
        dml_created_by
    ) VALUES(
        NEW.id,
        m_change,
        'UPDATE',
        coalesce(@logged_user, 'system')
    );
END;$$
DELIMITER ;

Trigger for DELETE statement. Will fill diff with complete row that was deleted.

DELIMITER $$

CREATE TRIGGER roles_audit_ad AFTER DELETE ON `roles` FOR EACH ROW
BEGIN
    INSERT INTO `roles_audit_log` (
        roles_id,
        diff,
        dml_type,
        dml_created_by
    ) VALUES(
        OLD.id,
        JSON_OBJECT(
            'id', OLD.id,
            'role', OLD.role,
            'privileges', OLD.privileges,
            'created_at', OLD.created_at,
            'updated_at', OLD.updated_at
        ),
        'DELETE',
        coalesce(@logged_user, 'system')
    );
END;$$
DELIMITER ;

Trigger for INSERT statement. Will fill diff with complete row that was deleted.

DELIMITER $$

CREATE TRIGGER roles_audit_ai AFTER INSERT ON `roles` FOR EACH ROW
BEGIN
    INSERT INTO `roles_audit_log` (
        roles_id,
        diff,
        dml_type,
        dml_created_by
    ) VALUES(
        NEW.id,
        JSON_OBJECT(
            'id', NEW.id,
            'role', NEW.role,
            'privileges', NEW.privileges,
            'created_at', NEW.created_at,
            'updated_at', NEW.updated_at
        ),
        'INSERT',
        coalesce(@logged_user, 'system')
    );
END;$$
DELIMITER ;