MySQL Triggers with Logging
Somebody asked why you can’t implement MySQL triggers that write information when you want to stop the DML statement, like autonomous procedures in Oracle. The question was a surprise but I didn’t find anything on it, so here’s how you can do it. This is more or less like an autonomous process by leveraging both the InnoDB and MyISAM engine’s behaviors. This post leverages an earlier explanation of MySQL Triggers.
- First you create a MyISAM table, which is a persistent store that auto commits when you’re other InnoDB tables can be transactionally dependent. Here’s a simple MyISAM
loggertable.
CREATE TABLE logger ( logger_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY , logger_event VARCHAR(50) , logger_table VARCHAR(50) , logger_instring VARCHAR(100) , logger_outstring VARCHAR(100) , created_by INT UNSIGNED , creation_date DATE , last_updated_by INT UNSIGNED , last_update_date DATE) ENGINE=MyISAM; |
- Next, you create an on-insert trigger that changes an input but doesn’t stop the transaction. It also writes to the logger MyISAM table in the scope of the transaction.
CREATE TRIGGER contact_insert BEFORE INSERT ON contact FOR EACH ROW BEGIN /* Check if last name contains a white space. */ IF new.last_name REGEXP '^.* .*$' THEN /* Insert into an MyISAM table, which auto commits in the scope of a transaction. */ INSERT INTO logger VALUES ( null ,'insert' ,'contact' , new.last_name , REPLACE(new.last_name,' ','-') , new.created_by , new.creation_date , new.last_updated_by , new.last_update_date ); /* Replace the name for the INSERT INTO the CONTACT table. */ SET new.last_name := REPLACE(new.last_name,' ','-'); END IF; END; $$ |
- Next, you create an on-update trigger that changes an update while aborting the transaction. It also writes to the logger MyISAM table because its outside the InnoDB scope of a transaction and auto committed on insert.
CREATE TRIGGER contact_update BEFORE UPDATE ON contact FOR EACH ROW BEGIN /* Check if last name contains a white space. */ IF new.last_name REGEXP '^.* .*$' THEN /* Insert into an MyISAM table, which auto commits in the scope of a transaction. */ INSERT INTO logger VALUES ( null ,'update' ,'contact' , new.last_name , null , old.created_by , old.creation_date , new.last_updated_by , new.last_update_date ); /* Throw an exception to force the business user to see they can't update a last name with a white space. */ SIGNAL SQLSTATE '42000'; END IF; END; $$ |
- Next, you create a test case with an
INSERTandUPDATEstatement that meets the condition of the triggers.
/* Insert a row meeting the trigger condition. */ INSERT INTO contact VALUES ( null, 1001, 1003,'Catherine', null,'Zeta Jones', 1001, UTC_DATE(), 1001, UTC_DATE()); /* Update a row meeting the trigger condition. */ UPDATE contact SET last_name = 'Zeta Jones' , last_updated_by = 1003 , last_update_date = UTC_DATE() WHERE last_name = 'Zeta-Jones'; |
- Last, query the logger table. You have a record inserted for both the allowed behavior and the aborted behavior. This means you have the ability to capture material that should never be inserted or updated into a table and who did it by leveraging the who-audit columns of the table.
SELECT * FROM logger; |
It returns:
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+ | logger_id | logger_event | logger_table | logger_instring | logger_outstring | created_by | creation_date | last_updated_by | last_update_date | +-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+ | 1 | insert | contact | Zeta Jones | Zeta-Jones | 1001 | 2013-04-26 | 1001 | 2013-04-26 | | 2 | update | contact | Zeta Jones | NULL | 1001 | 2013-04-26 | 1003 | 2013-04-26 | +-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+ 2 rows in set (0.00 sec) |
This effectively delivers in MySQL the equivalent of an autonomous transaction in Oracle. The result from the non-critical trigger records the before and after value, while the results from the critical update trigger only record the before values because the event is aborted by raising an error in the trigger. As always, I hope this helps somebody looking for a solution.
Subscribe via RSS