Syntax
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body
This statement creates a new trigger. 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, which must refer to a
permanent table. You cannot associate a trigger with a
TEMPORARY table or a view.
CREATE TRIGGER was added in MySQL
5.0.2.
In MySQL 5.0
CREATE
TRIGGERrequires the
SUPER privilege.
The
DEFINER clause determines the security
context to be used when checking access privileges at trigger
activation time. It was added in MySQL 5.0.17. See later in this
section for more information.
trigger_time is the trigger action
time. It can be
BEFORE or
AFTER to indicate that the trigger activates
before or after each row to be modified.
trigger_event indicates the kind of
statement that activates the trigger. The
trigger_event can be one of the
following:
It is important to understand that the
trigger_event does not represent a
literal type of SQL statement that activates the trigger so much
as it represents a type of table operation. For example, an
INSERT trigger is activated by not
only
INSERT statements but also
LOAD DATA statements because both
statements insert rows into a table.
A potentially confusing example of this is the
INSERT
INTO ... ON DUPLICATE KEY UPDATE ... syntax: a
BEFORE INSERT trigger will activate for every
row, followed by either an
AFTER INSERT trigger
or both the
BEFORE UPDATE and
AFTER
UPDATE triggers, depending on whether there was a
duplicate key for the row.
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_body 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 permissible within stored routines. See
Section 13.6.1, “BEGIN ... END
Compound-Statement Syntax”. Some statements are not permitted in
triggers; see
Section E.1, “Restrictions on Stored Programs”.
You can refer to columns in the subject table (the table
associated with the trigger) by using the aliases
OLD and
NEW.
OLD.col_name refers
to a column of 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.
MySQL stores the
sql_mode system
variable setting that is in effect at the time a trigger is
created, and always executes the trigger with this setting in
force,
regardless of the current server SQL
mode.
Note
Currently, cascaded foreign key actions do not activate
triggers.
The
DEFINER clause specifies the MySQL account
to be used when checking access privileges at trigger activation
time. If a
user value is given, it
should be a MySQL account specified as
'user_name'@'host_name'
(the same format used in the
GRANT
statement),
CURRENT_USER, or
CURRENT_USER(). The default
DEFINER value is the user who executes the
CREATE TRIGGER statement. This is
the same as specifying
DEFINER = CURRENT_USER
explicitly.
If you specify the
DEFINER clause, these rules
determine the legal
DEFINER user values:
- If you do not have the
SUPER
privilege, the only legal user
value is your own account, either specified literally or by
using CURRENT_USER. You cannot
set the definer to some other account.
- If you have the
SUPER
privilege, you can specify any syntactically legal account
name. If the account does not actually exist, a warning is
generated.
- Although it is possible to create a trigger with a nonexistent
DEFINER account, it is not a good idea for
such triggers to be activated until the account actually does
exist. Otherwise, the behavior with respect to privilege
checking is undefined.
Note: Because MySQL currently requires the
SUPER privilege for the use of
CREATE TRIGGER, only the second of
the preceding rules applies. (MySQL 5.1.6 implements the
TRIGGER privilege and requires that
privilege for trigger creation, so at that point both rules come
into play and
SUPER is required
only for specifying a
DEFINER value other than
your own account.)
From MySQL 5.0.17 on, MySQL takes the
DEFINER
user into account when checking trigger privileges as follows:
- At
CREATE TRIGGER time, the
user who issues the statement must have the
SUPER privilege.
-
At trigger activation time, privileges are checked against the
DEFINER user. This user must have these
privileges:
- The
SUPER privilege.
- The
SELECT privilege for
the subject table if references to table columns occur
using
OLD.col_name
or
NEW.col_name
in the trigger definition.
- The
UPDATE privilege for
the subject table if table columns are targets of
SET NEW.col_name =
value assignments in
the trigger definition.
- Whatever other privileges normally are required for the
statements executed by the trigger.
Before MySQL 5.0.17,
DEFINER is not available
and MySQL checks trigger privileges like this:
- At
CREATE TRIGGER time, the
user who issues the statement must have the
SUPER privilege.
- At trigger activation time, privileges are checked against the
user whose actions cause the trigger to be activated. This
user must have whatever privileges normally are required for
the statements executed by the trigger.
For more information about trigger security, see
Section 18.5, “Access Control for Stored Programs and Views”.
Within a trigger, the
CURRENT_USER() function returns the
account used to check privileges at trigger activation time.
Consistent with the privilege-checking rules just given,
CURRENT_USER() returns the
DEFINER user from MySQL 5.0.17 on. Before
5.0.17,
CURRENT_USER() returns the
user whose actions caused the trigger to be activated. For
information about user auditing within triggers, see
Section 6.3.8, “Auditing MySQL Account Activity”.
If you use
LOCK TABLES to lock a
table that has triggers, the tables used within the trigger are
also locked, as described in
Section 13.3.5.2, “LOCK TABLES and Triggers”.
Note
Before MySQL 5.0.10, triggers cannot contain direct references
to tables by name. Beginning with MySQL 5.0.10, you can write
triggers such as the one named testref shown
in this example:
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|
delimiter ;
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
Suppose that you insert the following values into table
test1 as shown here:
mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
As a result, the data in the four tables will be as follows:
mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> SELECT * FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)
Ref :
Dev MYSQL
