How to pass dynamic table name and column name to keep in log table in MySQL trigger

Shahab Alam 0
Viewed 21

If a record is altered in a database table, I want to keep a record of all changes. To do this, I want to enter the table name, column name, old value, and new value....... in the log table whenever an MYSQL table is changed via a trigger. my log table look like

enter image description here

triggers mysql
1 Answers

You must define a trigger for a specific table, therefore you already know which table to use. You can then specify it as a string literal when you write the trigger.

CREATE TRIGGER t AFTER UPDATE ON MyTable
FOR EACH ROW BEGIN
  IF NEW.column1 <=> OLD.column1 THEN
    INSERT INTO Log 
    SET table_name='MyTable', -- string literal
        field_name='column1', -- string literal
        old_val=OLD.column1,
        new_val=NEW.coumn1,
        action='UPDATE'; -- string literal
  END IF;
  IF NEW.column2 <=> OLD.column2 THEN
    INSERT INTO Log 
    SET table_name='MyTable', -- string literal
        field_name='column2', -- string literal
        old_val=OLD.column2,
        new_val=NEW.coumn2,
        action='UPDATE' -- string literal;
  END IF;
END

You'll have to continue and add another IF block for each column.

If you change the columns in your table, you'll have to rewrite the trigger.

You might ask if you can make one trigger that automatically does this for each column in a loop. No you can't do that. It would require PREPARE/EXECUTE, but you can't use dynamic SQL in a trigger.

If this seems like a lot of work, you might want to consider using an audit plugin instead. You can write your own, or you can use one of the open source audit plugins for MySQL, or if you're a customer of MySQL Enterprise, they offer an audit plugin to paying customers.

Bill Karwin0
Related