MySQL Database Triggers

MySQL Database Triggers:
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.
The first example shows you non-critical behavior. It observes an attempt to enter a two-part last name, and replaces the white space with a dash. This means the trigger ensures compliance on how names are entered in the database, which should be protected in the web form (through JQuery or JavaScript) and the database.

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 ;

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.

PlanetMySQL Voting:
Vote UP /
Vote DOWN

DIGITAL JUICE