19.3. Creating a Trigger

To define a trigger for a table, use the CREATE TRIGGER statement, which has the following syntax:

 CREATE TRIGGER trigger_name   { BEFORE | AFTER }   { INSERT | UPDATE | DELETE }   ON table_name   FOR EACH ROW   triggered_statement 

TRigger_name is the name to give the trigger, and table_name is the table with which to associate the trigger. BEFORE or AFTER indicates whether the trigger activates before or after the triggering event, and INSERT, UPDATE, or DELETE indicates what that event is.

When creating triggers, you might find it helpful to use a naming convention so that you can easily tell from a trigger name the table that it is associated with and what type of action it is for. In this study guide, trigger names are based on the table name and have a suffix composed of b or a for an activation time of BEFORE or AFTER, and i, u, or d for an activation event of INSERT, UPDATE, or DELETE. Using this convention, a BEFORE TRigger for INSERT statements on the Capital table is named Capital_bi.

TRiggered_statement is the statement to be executed for each row when the trigger activates. The triggered statement must be a single statement, but if necessary you can use a BEGIN/END compound statement to create a block and include multiple statements within the block. This is similar to the use of compound statements in stored routines. Within a BEGIN/END block, other compound statement syntax can be used, such as variable definitions, flow of control statements, and conditional statements.

When you define a trigger using a compound statement that consists of multiple statements, individual statements must be terminated by semicolon characters (';'), just as when defining stored procedures and functions. If you are using the mysql client to create such a trigger, you must redefine the statement delimiter. The following example demonstrates this technique. The trigger shown in the example monitors updates to the Capital table. It records the original Country and City values from the updated row and the new value to which the City column is updated:

 mysql> delimiter // mysql> CREATE TRIGGER Capital_bu     ->   BEFORE UPDATE     ->   ON Capital     ->   FOR EACH ROW     ->   BEGIN     ->     SET @country = OLD.Country;     ->     SET @capital_old = OLD.Capital;     ->     SET @capital_new = NEW.Capital;     ->  END;     -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> UPDATE Capital SET Capital = 'Washington D.C.'     -> WHERE Country = 'United States'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT @country AS Country, @capital_old AS 'Old capital',     -> @capital_new AS 'New capital'; +---------------+-------------+-----------------+ | Country       | Old capital | New capital     | +---------------+-------------+-----------------+ | United States | Washington  | Washington D.C. | +---------------+-------------+-----------------+ 1 row in set (0.00 sec) 

For information about compound statement syntax and statement delimiter redefinition, see Chapter 18, "Stored Procedures and Functions."

A table cannot have two triggers for the same combination of activation time and event. For example, you can have a BEFORE UPDATE and AFTER UPDATE trigger for a table, but not two BEFORE UPDATE or AFTER UPDATE triggers. This property does not pose a limitation. If it's necessary to perform multiple actions with a given type of trigger, they can all be included within a compound statement.