Creating a trigger in mysql to save changes made in one table into another

Question

I'm new to using triggers but having read quite a bit on it I think its the best solution to what I need. I have a table in mysql called employees and I want to create an audit trail of changes made to employees into another table called employee_changes. But I only want to create the audit entries for only the columns that have changed in the employees table. So for instance if the user changes the employee's salary, I want a row to be created in employee_changes capturing the ID of the employee, the name of the column that was changed (in this case salary), the old value and new value of the salary as well as the date and name of the user. The name of the user that made the change will always be updated in a field (LastUpdatedBy) in the employees table when the record is saved on the front end, so by the time the trigger is firing, it can get the user name from the LastUpdatedBy column.

With the help of This Post and This Post I was able to come up with this code:

DELIMITER //
CREATE TRIGGER emp_update
AFTER INSERT
ON employees FOR EACH ROW
BEGIN
DECLARE col_names CURSOR FOR
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'employees'
ORDER BY ordinal_position;
select FOUND_ROWS() into num_rows;
SET i = 1;
the_loop: LOOP
IF i > num_rows THEN
    CLOSE col_names;
    LEAVE the_loop;
END IF;

FETCH col_names 
INTO col_name;    

 IF OLD.col_name <> NEW.col_name THEN
    INSERT INTO employee_changes (EmployeeNumber, FieldName, OldValue, NewValue, ChangeDate, ChangedBy) 
    VALUES(OLD.EmployeeNumber, col_name, OLD.col_name, NEW.col_name, NOW(), NEW.LastUpdatedBy);
 END IF;

SET i = i + 1;  
END LOOP the_loop;

END //
DELIMITER ;

But when I run it, nothing happens, no trigger is created in INFORMATION_SCHEMA.TRIGGERS. And so when I update the employees table, nothing happens in my employee_changes table.

I feel like I'm almost there but I'm stuck.

Thanks for your help.

Please execute your code somewhere where you can read the error messages (e.g. from workbench or mysql client). You have some errors in your code (variable declaration, use of `old` in an `insert` trigger). — Solarflare, Sep 01 '16 at 09:28

Shadow · Answer 1 · 2016-09-01T09:31:29.023

0

The following line definitely throws an error:

IF OLD.col_name <> NEW.col_name THEN

because col_name is not the name of a field, it is a variable. This will never work in MySQL, you have to provide the exact column names. Forget the dynamic query of the column names, you have to hard code the test for each column.

Similarly, in the insert statement you cannot use the OLD.col_name or NEW.col_name formula, you have to explicitly provide the field names one by one. If you change the structure of the table, then you have to change the code of the trigger.

Unfortunately, this is a limitation of MySQL, currently I'm not aware of any solution or workaround for this issue.

edited Sep 01 '16 at 09:31

answered Sep 01 '16 at 09:27

Shadow

33,525
10
51
64

Thanks Shadow, but I have dozens of tables each with quite a number of fields. Is there another way I could perhaps achieved the same result? – Percy Kumah Sep 01 '16 at 09:31
Unfortunately, this is a limitation of MySQL, currently I'm not aware of any solution or workaround for this issue in mysql. You could try to implement the logging on application level (you know what data is changed in the application) or you may attempt to turn on MySQL binary log and then process the binlog using mysqlbinlog application and gather all changes from it on a regular basis. – Shadow Sep 01 '16 at 09:38

Creating a trigger in mysql to save changes made in one table into another

1 Answers1