Table of Contents for

MySQL in a Nutshell, 2nd Edition

Only one of each trigger timing and trigger event combination is allowed for each table. For example, a table cannot have two BEFORE INSERT triggers, but it can have a BEFORE INSERT and an AFTER INSERT trigger.

To specify that the trigger be executed immediately before the associated user statement, use the parameter BEFORE; to indicate that the trigger should be executed immediately afterward, use AFTER.

At this time, only three types of SQL statements can cause the server to execute a trigger: insertions, deletions, and updates. Specifying INSERT, however, applies the trigger to INSERT statements, LOAD DATA statements, and REPLACE statements—all statements that are designed to insert data into a table. Similarly, specifying DELETE includes both DELETE and REPLACE statements because REPLACE potentially deletes rows as well as inserting them.

Triggers are actions to be taken when a user requests a change to data. Each trigger is associated with a particular table and includes definitions related to timing and event. A trigger timing indicates when a trigger is to be performed (i.e., BEFORE or AFTER). A trigger event is the action that causes the trigger to be executed (i.e., a DELETE, INSERT, or UPDATE on a specified table).

After specifying the trigger event, give the keyword ON followed by the table name. This is followed by FOR EACH ROW and the SQL statement to be executed when the trigger event occurs. Multiple SQL statements to execute may be given in the form of a compound statement using BEGIN...END, which is described earlier in this chapter.

There is no ALTER TRIGGER statement at this time. Instead, use the DROP TRIGGER statement and then reissue CREATE TRIGGER with the new trigger.

To show how a trigger may be created, suppose that for a college database, whenever a student record is deleted from the students table, we want to write the data to another table to preserve that information. Here is an example of how that might be done with a trigger:

DELIMITER |

CREATE TRIGGER students_deletion
BEFORE DELETE
ON students FOR EACH ROW

BEGIN
INSERT INTO students_deleted
(student_id, name_first, name_last)
VALUES(OLD.student_id, OLD.name_first, OLD.name_last);
END|

DELIMITER ;

The first statement changes the terminating character for an SQL statement from its default, a semicolon, to a vertical bar. See the BEGIN...END statement earlier in this chapter for the reasons this is necessary.

Next, we create a trigger to stipulate that, before making a deletion in the students table, the server must perform the compound SQL statement given. The statements between BEGIN and END will write the data to be deleted to another table with the same schema.

To capture that data and pass it to the INSERT statement, we use the OLD table alias provided by MySQL coupled with the column names of the table where the row is to be deleted. OLD refers to the table in the trigger’s ON clause, before any changes are made by the trigger or the statement causing the trigger. To save space, in this example we’re capturing the data from only three of the columns. OLD.* is not allowed, so we have to specify each column. To specify the columns after they are inserted or updated, use NEW as the table alias.

The statement to be executed by the trigger in the previous example is a compound statement. It starts with BEGIN and ends with END and is followed by the vertical bar (|) that we specified as the delimiter. The delimiter is then reset in the last line back to a semicolon.