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 raises an error. Non-critical behavior means the trigger observes a DML statement and logs it or implements a change during the context of the DML activity.
DELIMITER $$ DROP TRIGGER IF EXISTS contact_insert$$ CREATE TRIGGER contact_insert BEFORE INSERT ON contact FOR EACH ROW BEGIN IF new.last_name REGEXP '^.* .*$' THEN SET new.last_name := REPLACE(new.last_name,' ','-'); END IF; END; $$ DELIMITER ;
The problem with implementing a non-critical trigger is that the database performs the work but clerks entering the data don’t learn the business rule. A critical trigger simply disallows non-conforming data entry. The next program shows a critical behavior with an
UPDATE statement row-level trigger. After all, won’t a data entry clerk update the entry with a white space after the
INSERT statement didn’t?
Yes, that was a rhetorical question. Spelling out the business rule in the
UPDATE statement row-level trigger should educate the persistent errant behavior. While letting the
INSERT statement row-level trigger manage the behavior probably saves time for most end-users who make a casual mistake.
DELIMITER $$ DROP TRIGGER IF EXISTS contact_update$$ CREATE TRIGGER contact_update BEFORE UPDATE ON contact FOR EACH ROW BEGIN IF new.last_name REGEXP '^.* .*$' THEN SIGNAL SQLSTATE '42000'; END IF; END; $$ DELIMITER ;
Somebody wanted to know why I choose
SQLSTATE 42000. That’s because it signals an error in the SQL statement, and that’s the closest existing
SQLSTATE to the actual behavior that exists. Moreover, the error identified by the critical trigger signals non-compliance with the application’s SQL standards that protects the data.
Hopefully, this helps somebody looking for a MySQL database trigger example that raises an exception. The example works with MySQL 5.5 forward because the critical trigger uses the
SIGNAL feature, which was introduced in MySQL 5.5. Another article shows you how to leverage MyISAM tables to create a logging mechanism for critical event triggers, and you click this link to the MySQL Triggers with Logging blog entry.