(mysql.info.gz) CREATE TRIGGER
Info Catalog
(mysql.info.gz) Triggers
(mysql.info.gz) Triggers
(mysql.info.gz) DROP TRIGGER
20.1 `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. `BEGIN
END' BEGIN END.
* 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.
Info Catalog
(mysql.info.gz) Triggers
(mysql.info.gz) Triggers
(mysql.info.gz) DROP TRIGGER
automatically generated byinfo2html