Creating Triggers

Triggers are created with theyou guessed itCREATE 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_statements

Let'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_USER indicates that the trigger statements will run with the authority of the account that issued the CREATE TRIGGER statement, rather than the account that issued the DML that caused the trigger to fire.

 

trigger_name

The 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 BEFORE or AFTER and UPDATE, INSERT, or DELETE (for example, there can be only one BEFORE UPDATE trigger on a table), so a sensible convention might result in triggers being given names such as table_name_bu (for a BEFORE UPDATE TRigger) or table_name_ai (for an AFTER INSERT trigger).

 

BEFORE|AFTER

Specifies whether the trigger fires before or after the DML statement itself has been executed. We'll discuss the implications of this shortly.

 

UPDATE|INSERT|DELETE

Defines the DML statement to which the trigger is associated.

 

ON table_name

Associates the trigger with a specific table.

 

FOR EACH ROW

This clause is mandatory in the initial MySQL implementation. It indicates that the trigger will be executed once for every row affected by the DML statement. The ANSI standard also provides for a FOR EACH STATEMENT mode, which might be supported in an upcoming version of MySQL.

 

trigger_statements

Define the statements that will be executed when the trigger is invoked. If there is more than one statement, then the statements need to be enclosed in a BEGIN-END block.

Prior to MySQL 5.1.6, you needed the SUPER privilege to create a trigger. In 5.1.6 and above, the TRIGGER privilege is required.

11.1.1. Referring to Column Values Within the Trigger

Trigger statements can include references to the values of the columns being affected by the trigger. You can access and sometimes modify the values of these columns.

To distinguish between the values of the columns "before" and "after" the relevant DML has fired, you use the NEW and OLD modifiers. For instance, in a BEFORE UPDATE trigger, the value of the column mycolumn before the update is applied is OLD.mycolumn, and the value after modification is NEW.mycolumn.

If the trigger is an INSERT trigger, only the NEW value is available (there is no OLD value). Within a DELETE trigger, only the OLD value is available (there is no NEW value).

Within BEFORE TRiggers you can modify a NEW value with a SET statementthus changing the effect of the DML.

11.1.2. Triggering Actions

Triggers will normally execute in response to the DML statements matching their specificationfor instance, BEFORE INSERT will always be invoked in response to an INSERT statement.

However, triggers also fire in response to implicitas well as explicitDML. Some statements are capable of generating DML as a side effect of their primary activity. For instance, an INSERT statement that contains an ON DUPLICATE KEY UPDATE clause can issue an implicit UPDATE statement causing BEFORE UPDATE or AFTER UPDATE TRiggers to fire. Likewise, the REPLACE statement can cause both INSERT and DELETE triggers to fire (since, for an existing row, REPLACE issues a DELETE followed by an INSERT).

11.1.3. BEFORE and AFTER Triggers

The BEFORE and AFTER clauses determine when your trigger code executes: either before or after the DML statement that causes the trigger to be invoked.

The most significant difference between BEFORE and AFTER TRiggers is that in an AFTER TRigger you are not able to modify the values about to be inserted into or updated with the table in questionthe DML has executed, and it is too late to try to change what the DML is going to do.

IF you try to modify a NEW value in an AFTER trigger, you will encounter an error, as shown in Example 11-1.

Example 11-1. AFTER triggers cannot modify NEW values

mysql> CREATE TRIGGER account_balance_au
 AFTER UPDATE ON account_balance FOR EACH ROW
BEGIN
 DECLARE dummy INT;

 IF NEW.balance<0 THEN
 SET NEW.balance=NULL;
 END IF;

END
$$

ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger

Although you can do pretty much anything you need to do in a BEFORE trigger, you still may wish to use AFTER TRiggers for activities that logically should occur in a transaction after a DML has successfully executed. Auditing activities, for example, are best executed in an AFTER TRigger, since you will first want to make sure that the DML succeeded.