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.

1. 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 logger table.

CREATE TABLE logger
( logger_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, logger_event      VARCHAR(50)
, logger_table      VARCHAR(50)
, logger_instring   VARCHAR(100)
, …

One of the students wanted an equivalent example to an Oracle DML trigger sample that replaces a white space in a last name with a dash for an INSERT statement. Apparently, the MySQL trigger example in the Oracle Database 11g and MySQL 5.6 Developer Handbook was a bit long. I have to agree with that because the MySQL DML trigger demonstrated cursors and loops in the trigger code.

Triggers can be statement- or row-level actions. Although some databases let you define statement-level triggers, MySQL doesn’t support them. MySQL only supports row-level triggers. Row-level triggers support critical or non-critical behaviors. Critical behavior means the trigger observes an insert, update, or delete that must be stopped, which means it …