Support for triggers is included beginning with MySQL 5.0.2. This section discusses how to use triggers and some limitations regarding their use. Additional information about trigger limitations is given in Section I.1, “Restrictions on Stored Routines and Triggers”.
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
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
DROP TRIGGER statement.
The syntax for these statements is described in
Section 18.1, “
CREATE TRIGGER Syntax”, and
Section 18.2, “
DROP TRIGGER Syntax”.
Here is a simple example that associates a trigger with a table
INSERT statements. It acts as an
accumulator to sum the values inserted into one of the columns of
The following statements create a table and a trigger for it:
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;
CREATE TRIGGER statement creates a trigger
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
INSERT indicates the event that
activates the trigger. In the example,
INSERT statements cause trigger activation.
You can also create triggers for
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
amount column to be inserted into the
To use the trigger, set the accumulator variable to zero, execute
INSERT statement, and then see what value
the variable has afterward:
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
+ 1937.50 - 100, or
To destroy the trigger, use a
statement. You must specify the schema name if the trigger is not
in the default schema:
DROP TRIGGER test.ins_sum;
Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.
In addition to the requirement that trigger names be unique for a
schema, 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 activation time and activation event. For
example, you cannot define two
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
FOR EACH ROW. (An example
appears later in this section.)
enable you to access columns in the rows affected by a trigger.
NEW are not case
sensitive.) In an
INSERT trigger, only
NEW. can be
used; there is no old row. In a
can be used; there is no new row. In an
trigger, you can use
refer to the columns of a row before it is updated and
refer to the columns of the row after it is updated.
A column named with
OLD is read-only. You can
refer to it (if you have the
but not modify it. A column named with
be referred to if you have the
for it. In a
BEFORE trigger, you can also
change its value with
NEW. 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.
BEFORE trigger, the
value for an
AUTO_INCREMENT column is 0, not
the automatically generated sequence number that will be generated
when the new record actually is inserted.
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, if you use the
mysql program to define a trigger that executes
multiple statements, it is necessary to redefine the
mysql statement delimiter so that you can use
; statement delimiter within the trigger
definition. The following example illustrates these points. It
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:
CREATE TRIGGER upd_check BEFORE UPDATE ON account->
FOR EACH ROW->
IF NEW.amount < 0 THEN->
SET NEW.amount = 0;->
ELSEIF NEW.amount > 100 THEN->
SET NEW.amount = 100;->
It can be easier to define a stored procedure separately and then
invoke it from the trigger using a simple
statement. This is also advantageous if you want to invoke the
same routine from within several triggers.
There are some limitations on what can appear in statements that a trigger executes when activated:
The trigger cannot use the
to invoke stored procedures that return data to the client or
that use dynamic SQL. (Stored procedures are allowed to return
data to the trigger through
The trigger cannot use statements that explicitly or
implicitly begin or end a transaction such as
Prior to MySQL 5.0.10, triggers cannot contain direct references to tables by name.
MySQL handles errors during trigger execution as follows:
BEFORE trigger fails, the operation on
the corresponding row is not performed.
AFTER trigger is executed only if the
BEFORE trigger (if any) and the row
operation both execute successfully.
An error during either a
AFTER trigger results in failure of the
entire statement that caused trigger invocation.
For transactional tables, failure of a trigger (and thus the whole statement) should cause rollback of all changes performed by the statement. For non-transactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.