CREATE TRIGGER

Triggers are actions to be taken when a user requests a change to data in MySQL.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE
[DEFINER = {'user'@'host'|CURRENT_USER}]
TRIGGER trigger {AFTER|BEFORE}
{DELETE|INSERT|UPDATE}
ON table FOR EACH ROW statement

Explanation

Triggers are actions to be taken when a user requests a change to data. Each trigger is associated with a particular table.

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: deletions, insertions, 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 potential deletes rows as well as inserting them.

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.

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

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

Examples

As an example of how a trigger may be created, suppose that for a college database, whenever a student record is deleted in the students table, we want to write the data to another table to preserve that information. An example follows 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 semi-colon, to a vertical bar. See the BEGIN...END statement for the reasons this is necessary.

Next, we create a trigger to stipulate that, before making a deletion is made 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 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 followed by the vertical bar (|) that we specified as the delimiter. The delimiter is then reset in the last line back to a semi-colon.