This manual is provided as a courtesy.
It is not an official source.
Please check
dev.mysql.com for updated
information.
This document was last updated
March 19 2005 02:56:07 PM
Go to the first,
previous,
next,
last
section, table of
contents.
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 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 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:
- The keyword
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.
- The keyword
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.
- The statement following
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:
- The trigger cannot refer directly to tables by name, including the
table with which the table is associated. You can, however, use the
keywords
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.
- The trigger cannot invoke stored procedures using the
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.)
- The trigger cannot use statements that begin or end
a transaction such as
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.
Articles