Chapter 11. Triggers
Database triggers are stored programs that are executed in response to some
kind of event that occurs within the database. In the current MySQL
implementation of triggers, triggers fire in response to a DML statement
(INSERT, UPDATE, DELETE) on a specified table.
Triggers are a powerful mechanism for ensuring the integrity of your data, as well as a useful means of automating certain operations in the database, such as denormalization and audit logging.
Creating Triggers
Triggers are created with the—you guessed it—CREATE TRIGGER statement, which has the following syntax:
CREATE [DEFINER={user|CURRENT_USER}] TRIGGER trigger_name
{BEFORE|AFTER}
{UPDATE|INSERT|DELETE}
ON table_name
FOR EACH ROW
trigger_statementsLet's look at each part of the CREATE
TRIGGER statement in turn:
DEFINER={user|CURRENT_USER}Controls the account that will be used to check privileges when the trigger is invoked. The default of
CURRENT_USERindicates that the trigger statements will run with the authority of the account that issued theCREATE TRIGGERstatement, rather than the account that issued the DML that caused the trigger to fire.trigger_nameThe trigger name follows the normal conventions for MySQL's naming of database objects. While you can call your trigger virtually anything, we recommend that you adopt a predictable naming convention. There can be only one trigger for any combination of
BEFOREorAFTERandUPDATE,INSERT, orDELETE(for example, there can be only oneBEFORE ...