(mysql.info) using-triggers
Info Catalog
(mysql.info) drop-trigger
(mysql.info) triggers
18.3 Using Triggers
===================
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
routine-restrictions.
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 create-trigger, and drop-trigger.
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 accumulator 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. You must
specify the schema name if the trigger is not in the default schema:
mysql> 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 `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.)
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 (if you
have the `SELECT' privilege), 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.
In a `BEFORE' trigger, the `NEW' 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.
`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, 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 the `;' statement delimiter 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 can be easier to define a stored procedure separately and then
invoke it from the trigger using a simple `CALL' 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 `CALL' statement 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 `OUT' or `INOUT' parameters.)
* The trigger cannot use statements that explicitly or implicitly
begin or end a transaction such as `START TRANSACTION', `COMMIT',
or `ROLLBACK'.
* Prior to MySQL 5.0.10, triggers cannot contain direct references
to tables by name.
MySQL handles errors during trigger execution as follows:
* If a `BEFORE' trigger fails, the operation on the corresponding
row is not performed.
* An `AFTER' trigger is executed only if the `BEFORE' trigger (if
any) and the row operation both execute successfully.
* An error during either a `BEFORE' or `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.
Info Catalog
(mysql.info) drop-trigger
(mysql.info) triggers
automatically generated byinfo2html