Triggers on multiple tables and update it into single column-CodePudding

I have a requirement where, on updating employee form. I have to keep track which fields updated by user. The employee form we are saving has total of 6 tables. When user updates any column, I want to track the old value and the new value that he changed.

For that I have created a Audit logs table, In that I have kept fields like, id, employee_id, table_name, old_value, new_value

So suppose when user edits two columns in employee form, i.e firstname and lastname. I want to save those two records in old value column in two different rows individually. i.e in first record, it should get the firstname and in second record it should get lastname. & any column that i change related to any of the 6 tables then it should update in my trigger table Please suggest me how to get this query

Below I have written a basic trigger

CREATE TRIGGER advance_audit 
BEFORE UPDATE ON employee 
FOR EACH ROW 
INSERT INTO employees_audit 
    SET action = 'update', old_name= OLD.firstname, old_name = OLD.lastname;

CodePudding user response：

CREATE TRIGGER advance_audit 
AFTER UPDATE ON employee 
FOR EACH ROW 
BEGIN
IF NEW.firstname <> OLD.firstname THEN 
    INSERT INTO employees_audit (action, column_name, old_name, new_name, operator)
    VALUES ('update', 'firstname', OLD.firstname, NEW.firstname, SYSTEM_USER());
END IF;
IF NEW.lastname <> OLD.lastname THEN 
    INSERT INTO employees_audit (action, column_name, old_name, new_name, operator)
    VALUES ('update', 'lastname', OLD.lastname, NEW.lastname, SYSTEM_USER());
END IF;
-- the same blocks for each another column to be audited
END

Use AFTER trigger. It will not save into the log if the query fails.