Rudimentary support for triggers is included beginning with MySQL 5.0.2.
A trigger is a named database object that is associated with a table, and
that activates when a particular event occurs for the table. For example,
the following statements set up a table, as well as a trigger for INSERT
statements into the table. The trigger sums the values inserted into one of
the table's columns:
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
This chapter describes the syntax for creating and dropping triggers, and show some examples of how to use them.
CREATE TRIGGER Syntax
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.
The trigger becomes associated with the table named tbl_name.
tbl_name must refer to a permanent table. You cannot associate a
trigger with a TEMPORARY table or a view.
trigger_time is the trigger action time. It can be BEFORE or
AFTER to indicate that the trigger activates before or after the
statement that activated it.
trigger_event indicates the kind of statement that activates the
trigger. It can be INSERT, UPDATE, or DELETE.
For example, a BEFORE trigger for INSERT statements could be
used to check the values to be inserted into new rows.
There cannot be two triggers for a given table that have the same trigger
action time and event. For example, you cannot have two BEFORE
UPDATE triggers for a table. But you can have a BEFORE UPDATE
and a BEFORE INSERT trigger, or a BEFORE UPDATE and an
AFTER UPDATE trigger.
trigger_stmt is the statement to execute when the trigger activates.
If you want to execute multiple statements, use the BEGIN ... END
compound statement construct. This also enables you to use the same
statements that are allowable within stored routines.
See section 19.1.4 BEGIN ... END Compound Statement.
Note: Currently, triggers have the same limitation as stored functions that they may not contain direct references to tables by name. This limitation will be lifted as soon as possible.
However, in the triggered statement, you can refer to columns in the table
associated with the trigger by using the names OLD and NEW.
OLD.col_name refers to a column of a an existing row before
it is updated or deleted. NEW.col_name refers to the column
of a new row to be inserted or an existing row after it is updated.
Use of SET NEW.col_name = value requires the UPDATE
privilege on the column. Use of SET value = NEW.col_name
requires the SELECT privilege on the column.
The CREATE TRIGGER statement requires the SUPER privilege.
It was added in MySQL 5.0.2.
DROP TRIGGER SyntaxDROP TRIGGER tbl_name.trigger_name
Drops a trigger. The name of the trigger to drop must include the table name because each trigger is associated with a particular table.
The DROP TRIGGER statement requires the SUPER privilege.
It was added in MySQL 5.0.2.
Support for triggers is included beginning with MySQL 5.0.2. Currently, trigger support is rudimentary, so there are certain limitations on what you can do with them. This section discusses how to use triggers and what the current limitations are.
A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.
A trigger is associated with a table and is defined to activate when an
INSERT, DELETE, or UPDATE statement for the table
executes. A trigger can be set to activate either before or after the
triggering statement. For example, you can have a trigger activate before
each row that is deleted from a table or after each row that is updated.
To create a trigger or drop a trigger, use the CREATE TRIGGER or
DROP TRIGGER statement. The syntax for these statements is described
in section 20.1 CREATE TRIGGER Syntax and
section 20.2 DROP TRIGGER Syntax.
Here is a simple example that associates a trigger with a table for
INSERT statements. It acts as an accumulator to sum the values
inserted into one of the columns of the table.
The following statements create a table and a trigger for it:
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
The CREATE TRIGGER statement creates a trigger named ins_sum
that is associated with the account table. It also includes clauses
that specify the trigger activation time, the triggering event, and what to
do with the trigger activates:
BEFORE indicates the trigger action time. In this case,
the trigger should activate before each row inserted into the table. The
other allowable keyword here is AFTER.
INSERT indicates the event that activates the trigger.
In the example, INSERT statements cause trigger activation. You can
also create triggers for DELETE and UPDATE statements.
FOR EACH ROW defines the statement to execute
each time the trigger activates, which occurs once for each row affected by
the triggering statement In the example, the triggered statement is a
simple SET that accumulates the values inserted into the
amount column. The statement refers to the column as
NEW.amount which means ``the value of the amount column to be
inserted into the new row.''
To use the trigger, set the accumlator variable to zero, execute an
INSERT statement, and then see what value the variable has afterward:
mysql> SET @sum = 0; mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); mysql> SELECT @sum AS 'Total amount inserted'; +-----------------------+ | Total amount inserted | +-----------------------+ | 1852.48 | +-----------------------+
In this case, the value of @sum after the INSERT statement
has executed is 14.98 + 1937.50 - 100 or 1852.48.
To destroy the trigger, use a DROP TRIGGER statement. The
trigger name must include the table name:
mysql> DROP TRIGGER account.ins_sum;
Because a trigger is associated with a particular table, you cannot have multiple triggers for a table that have the same name. You should also be aware that the namespace for triggers might change in the future from table to database. That is, the requirement that trigger names be unique for a given table might be strengthened to the requirement that they be unique within the database. For better forward compatibility with future development, try to use trigger names that do not overlap within a database.
In addition to the requirement that trigger names be unique for a table,
there are other limitations on the types of triggers you can create.
In particular, you cannot have two triggers for a table that have the same
activate time and activation event. For example, you cannot define two
BEFORE INSERT triggers or two AFTER UPDATE triggers for a table.
This should rarely be a significant limitation, because it is possible to
define a trigger that executes multiple statements by using the BEGIN
… END compound statement construct after FOR EACH ROW. (An example
appears later in this section.)
There are also limitations on what can appear in the statement that the trigger executes when activated:
OLD and NEW. OLD refers to an existing row to be
deleted or a row to be updated before the update occurs. NEW refers
to a new row to be inserted or an updated row after the update occurs.
CALL statement.
(This means, for example, that you cannot get around the prohibition on
referring to tables by name by invoking a stored procedure that refers to
the tables.)
START TRANSACTION, COMMIT, or ROLLBACK.
The OLD and NEW keywords enable you to access columns in
the rows affected by a trigger. (OLD and NEW are not case
sensitive.) In an INSERT trigger, only NEW.col_name
can be used; there is no old row. In a DELETE trigger, only
OLD.col_name can be used; there is no new row. In an
UPDATE trigger, you can use OLD.col_name to refer to
the columns of a row before it is updated and NEW.col_name
to refer to the columns of the row after it is updated.
A column named with OLD is read-only. You can refer to it but not
modify it. A column named with NEW can be referred to if you have
the SELECT privilege for it. In a BEFORE trigger, you can also
change its value with SET NEW.col_name = value if you
have the UPDATE privilege for it. This means you can use a trigger to
modify the values to be inserted into a new row or that are used to update a
row.
OLD and NEW are MySQL extensions to triggers.
By using the BEGIN … END construct, you can define a trigger
that executes multiple statements. Within the BEGIN block, you also
can use other syntax that is allowed within stored routines such as
conditionals and loops. However, just as for stored routines, when you
define a trigger that executes multiple statements, it becomes necessary to
redefine the statement delimiter if you are entering the trigger with the
mysql program so that you can use the `;' character within
the trigger definition. The following example illustrates these points. It
defines an UPDATE trigger that checks the new value to be used for
updating each row, and modifies the value to be within the range
from 0 to 100. This must be a BEFORE trigger because the value needs
to be checked before it is used to update the row:
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END//
mysql> delimiter ;
It might occur to you that it would be easier to define a stored procedure
separately and then invoke it from the trigger using a simple CALL
statement. That would also be advantageous if you wanted to invoke the same
routine from within several triggers. However, a limitation on triggers
currently is that CALL cannot be used. So for now you have to write
out the compound statement in each CREATE TRIGGER statement where you
want to use it.
Go to the first, previous, next, last section, table of contents.