Want to write a trigger after UPDATE

Question

I have writen a trigger which will log every data updation and will log previous_value, new_value, field_name and many more. But the problem is i have 77 fields in my table. So, it's difficult to write IF ENDIF for each field so i want to know is it possible to write using loop?

I have tried so far::

BEGIN

IF(OLD.company_name != NEW.company_name) THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_name", OLD.company_name, NEW.company_name, CURRENT_TIMESTAMP(), NEW.company_id);
END IF;
IF(OLD.company_first_name != NEW.company_first_name) THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_first_name", OLD.company_first_name, NEW.company_first_name, CURRENT_TIMESTAMP(), NEW.company_id);
END IF;

END

Please help, or any suggestion will be very helpfull.

Thanks in advance.

Answer 1

Since dynamic sql is not allowed in the trigger body,you can use a statement which runs through all the columns and builds the IF statements

SELECT CONCAT('IF(OLD.',COLUMN_NAME, '=NEW.',COLUMN_NAME,' THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_name", OLD.' ,COLUMN_NAME,', NEW.',COLUMN_NAME,', CURRENT_TIMESTAMP(), NEW.company_id; END IF;')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='yourTable' AND column_key!='PRI';

You can narrow the results even more, take a look at COLUMNS table if you want to filter based on column type or remove columns with keys and so on.I removed the primary key columns in the last condition for example.