Tutorial :Need an abstract trigger in MySQL 5.1 to update an audit log



Question:

I need a way to check for and pass entries into an audit log for any entries in a table that have been changed. It needs to be abstracted away from the table structure.

For example:

CREATE TRIGGER table1_update   BEFORE UPDATE ON table1   FOR EACH ROW BEGIN    DECLARE i_column_name varchar(32);    DECLARE done INT;    DECLARE cursor1 CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = 'table1';    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;      OPEN cursor1;    REPEAT      FETCH cursor1 INTO i_column_name;      IF NOT done THEN          --pass the variable column_name and its old.i_column_name and new.i_column_name values to the audit table        END IF;    UNTIL done END REPEAT;    CLOSE cursor1;  END$$  

We have too many tables that need to be audited to custom build every single INSERT, UPDATE, and DELETE trigger. I've tried a number of things and I'm thinking I'm out of luck. Anyone have any ideas?


Solution:1

You can't have an abstract trigger, it must be defined on a specific table. The closest you can get is to put the code for the trigger into a stored procedure, and then the triggers for each table will just call the procedure.

CREATE PROCEDURE audit_update (IN tablename VARCHAR(64))  BEGIN    DECLARE  i_column_name varchar(32);    DECLARE done INT;    DECLARE cursor1 CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = tablename;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;      OPEN cursor1;    REPEAT      FETCH cursor1 INTO i_column_name;      IF NOT done THEN          --pass the variable column_name and its old.i_column_name and new.i_column_name values to the audit table        END IF;    UNTIL done END REPEAT;    CLOSE cursor1;  END      CREATE TRIGGER table1_update   BEFORE UPDATE ON table1   FOR EACH ROW BEGIN    CALL audit_update('table1');  END  

You should be able to easily script up something that will create the triggers for all of your tables using the information_schema or something along those lines.

SELECT CONCAT('CREATE TRIGGER ', table_name, '_update BEFORE UPDATE ON ', table_name, ...) FROM information_schema...  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »