Understanding Triggers

Note

Requires MySQL 5 Support for triggers was added to MySQL 5. As such, this chapter is applicable to MySQL 5 or later only.

MySQL statements are executed when needed, as are stored procedures. But what if you wanted a statement (or statements) to be executed automatically when events occur? For example:

• Every time a customer is added to a database table, check that the phone number is formatted correctly and that the state abbreviation is in uppercase.

• Every time a product is ordered, subtract the ordered quantity from the number in stock.

• Whenever a row is deleted, save a copy in an archive table.

What all these examples have in common is that they need to be processed automatically whenever a table change occurs. And that is exactly what triggers are. A trigger is a MySQL statement (or a group of statements enclosed within BEGIN and END statements) that are automatically executed by MySQL in response to any of these statements:

• DELETE

• INSERT

• UPDATE

No other MySQL statements support triggers.