19.2. Trigger Concepts

A trigger is an object that belongs to a database. Each trigger within the database must have a different name. A trigger is defined to activate when a particular kind of event occurs for a given table. (On occasion, you might see it said that a trigger "fires." This study guide uses the term "activates.") The trigger definition includes a statement to be executed when the trigger activates.

The events for which triggers can be defined are INSERT, DELETE, and UPDATE. A given trigger is defined for only one of these events, but you can define multiple triggers for a table, one trigger per type of event.

Triggers can be defined to activate either before or after the event. This means there can be two triggers per event (for example, one trigger to activate before an UPDATE and one to activate after).

The following example creates a trigger named Capital_bi that activates for inserts into the Capital table:

 CREATE TRIGGER Capital_bi   BEFORE INSERT   ON Capital   FOR EACH ROW     SET NEW.Population =       IF(NEW.Population < 0, 0, TRUNCATE(NEW.Population,-3)); 

The trigger checks the Population value before the insert occurs and "filters" it: Values less than zero are converted to zero, and other values are truncated down to the nearest multiple of 1,000. The result is that the filtered value is inserted, not the value given in the INSERT statement.

When a trigger activates, the triggered statement in the definition executes. The FOR EACH ROW in the syntax means that execution occurs once "for each row inserted, updated, or deleted," not "for each row currently in the table." It also means that a trigger activates multiple times for a statement that affects several rows, such as a multiple-row INSERT statement.

The effect of the Capital_bi trigger for inserts into the Capital table can be seen as follows:

 mysql> INSERT INTO Capital VALUES     -> ('CountryA','CityA',-39),     -> ('CountryB','CityB',123456); Query OK, 2 rows affected (0.00 sec) Records: 2  Duplicates: 0  Warnings: 0 mysql> SELECT * FROM Capital     -> WHERE Country IN ('CountryA','CountryB'); +----------+---------+------------+ | Country  | Capital | Population | +----------+---------+------------+ | CountryA | CityA   |          0 | | CountryB | CityB   |     123000 | +----------+---------+------------+ 2 rows in set (0.00 sec)