Database Trigger - Triggers in MySQL

Triggers in MySQL

MySQL 5.0.2 introduced support for triggers. MySQL supports these trigger types:

• Insert Trigger
• Update Trigger
• Delete Trigger

Note: MySQL allows only one trigger of each type on each table (i.e. one before insert, one after insert, one before update, one after update, one before delete and one after delete).

Note: MySQL does NOT fire triggers outside of a statement (i.e. API's, foreign key cascades)

The SQL:2003 standard mandates that triggers give programmers access to record variables by means of a syntax such as REFERENCING NEW AS n. For example, if a trigger is monitoring for changes to a salary column one could write a trigger like the following:

CREATE TRIGGER salary_trigger BEFORE UPDATE ON employee_table REFERENCING NEW ROW AS n, OLD ROW AS o FOR EACH ROW IF n.salary <> o.salary THEN END IF; ;