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.