MacLochlainns Weblog

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 (you can find help on MySQL Regular Expressions in this other post). 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 ;

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.