CREATE [DEFINER = {user
| CURRENT_USER }] TRIGGERtrigger_name
trigger_time
trigger_event
ONtbl_name
FOR EACH ROWtrigger_stmt
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.
MySQL Enterprise. For expert advice on creating triggers, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
CREATE TRIGGER
requires the
TRIGGER
privilege for the table
associated with the trigger. If binary logging is enabled, the
CREATE TRIGGER
statement might also
require the SUPER
privilege, as
described in Section 18.6, “Binary Logging of Stored Programs”.
SUPER
may also be required
depending on the DEFINER
value, as described
later.
The DEFINER
clause determines the security
context to be used when checking access privileges at trigger
activation time.
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:
INSERT
: The trigger is
activated whenever a new row is inserted into the table; for
example, through INSERT
,
LOAD DATA
, and
REPLACE
statements.
UPDATE
: The trigger is
activated whenever a row is modified; for example, through
UPDATE
statements.
DELETE
: The trigger is
activated whenever a row is deleted from the table; for
example, through DELETE
and
REPLACE
statements. However,
DROP TABLE
and
TRUNCATE TABLE
statements on
the table do not activate this trigger,
because they do not use DELETE
.
Dropping a partition does not activate
DELETE
triggers, either. See
Section 12.2.11, “TRUNCATE TABLE
Syntax”.
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_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 12.7.1, “BEGIN ... END
Compound Statement Syntax”. Some statements are not allowed in
triggers; see Section D.1, “Restrictions on Stored Routines, Triggers, and Events”.
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 server SQL mode in effect when
the event begins executing.
Currently, triggers are not activated by cascaded foreign key actions. This limitation will be lifted as soon as possible.
In MySQL 5.5, you can write triggers containing
direct references to tables by name, such as the trigger 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)
You can refer to columns in the subject table (the table
associated with the trigger) by using the aliases
OLD
and NEW
.
OLD.
refers
to a column of an existing row before it is updated or deleted.
col_name
NEW.
refers
to the column of a new row to be inserted or an existing row after
it is updated.
col_name
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 in
'
format (the same format used in the
user_name
'@'host_name
'GRANT
statement). The
user_name
and
host_name
values both are required. The
definer can also be given as
CURRENT_USER
or
CURRENT_USER()
. The default
DEFINER
value is the user who executes the
CREATE TRIGGER
statement. (This is
the same as DEFINER = CURRENT_USER
.)
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 triggers with a nonexistent
DEFINER
value, it is not a good idea for
such triggers to be activated until the definer actually does
exist. Otherwise, the behavior with respect to privilege
checking is undefined.
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
TRIGGER
privilege.
At trigger activation time, privileges are checked against the
DEFINER
user. This user must have these
privileges:
The TRIGGER
privilege.
The SELECT
privilege for
the subject table if references to table columns occur via
OLD.
or
col_name
NEW.
in the trigger definition.
col_name
The UPDATE
privilege for
the subject table if table columns are targets of
SET NEW.
assignments in
the trigger definition.
col_name
=
value
Whatever other privileges normally are required for the statements executed by the trigger.
Within a trigger, the
CURRENT_USER()
function returns the
account used to check privileges at trigger activation time. This
is the DEFINER
user, not the user whose actions
caused the trigger to be activated. For information about user
auditing within triggers, see
Section 5.5.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 12.3.5.2, “LOCK TABLES
and Triggers”.
User Comments
When you want to use
SET NEW.col_name = value
in your trigger, please note that you CANNOT use this with the AFTER the action, and must use it BEFORE the action.
Therefore, this will work:
CREATE TRIGGER sdata_insert BEFORE INSERT ON `sometable`
FOR EACH ROW
BEGIN
SET NEW.guid = UUID();
END
;
And this will NOT work:
CREATE TRIGGER sdata_insert AFTER INSERT ON `sometable`
FOR EACH ROW
BEGIN
SET NEW.guid = UUID();
END
;
If you have a statement such as:
INSERT INTO foo VALUES (bar, baz)
ON DUPLICATE KEY UPDATE field = value;
This will cause a BEFORE INSERT trigger to run with every execution of the above statement, where the AFTER INSERT trigger will run only when the duplicate key condition does not occur.
I posted a breakdown of the above trigger statements, when I learned them before I could have used the example above in a format like this. I hope it helps someone.
http://www.rustyrazorblade.com/index.php/2006/09/14/mysql-triggers-tutorial/
Be careful with BEFORE triggers. Constraints may occur, specifically if you are using InnoDB engine, where an insert will fail, but actions from your BEFORE trigger will succeed.
Use BEFORE triggers primarily for constraints or rules, not transactions, tweaking the NEW.* columns should be fine.
Stick with AFTER triggers for most other operations, such as inserting into a history table or updating a denormalization.
It's not possible to perform a "STOP ACTION" into a TRIGGER. For example, if you're deleting a row and this action activates a trigger, is not possible to abort the proccess of DELETE of the row. A way to abort the current operation, is to cause a deliberated error.
If you've implemented SSL, see...
http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html
...you can use Triggers and DES_ENCRYPT to move your password encryption to the database level and enforce it in a way that stops developers forgeting to use it (or bypassing it) with the following triggers...
CREATE TRIGGER user_insert BEFORE INSERT ON `user` FOR EACH ROW SET NEW.TimeStampCreated = NOW(), NEW.Password = DES_ENCRYPT(NEW.Password);
CREATE TRIGGER user_update BEFORE UPDATE ON `user` FOR EACH ROW SET NEW.Password = DES_ENCRYPT(NEW.Password);
...you'll also notice the first one enforces auditing in a way that saves you from relying on developers getting that right as well.
You could give your dev's a nice stored proc to retrieve or comapre their submitted password but hopefully they can remember either DES_ENCRYPT/_DECRYPT or your phone number ;^).
Whilst bearing in mind that this doesn't magically make your entire system "secure" by some magic wave of a wand, given that you've implemented SSL it should be trivial to secure the link between web and database server (if there even is a gap) and then you can use HTTPS and only a little more careful thought to implement a system that is secure from submission page through to backup system in such a way that only someone physically stood at the server with the server's and Mysql's root password could decrypt the password/data.
Another way to "STOP ACTION" is to create a table (stop_action) with just a primary key(reason_to_stop). Then you pre-fill this table with some text ('do not do that', 'or that either')=> to stop action, just do an insert into this table (stop_action) with any of the pre-filled value ('do not do that').
And the stop_action table could even use the BLACKHOLE storage engine so you're not accumulating extra records for nothing.
The BLACKHOLE engine is suitable for doing this (STOP ACTION), because no rows are stored. Ergo there can not be any duplicates...
TIP: to create a simple trigger that initializes multiple columns such as DATE type columns when a record is created:
CREATE TRIGGER mytrigger BEFORE INSERT ON TABLE_1 FOR EACH ROW SET NEW.MY_DATETIME_COLUMN = NOW(), NEW.MY_DATE_COLUMN = CURDATE()
Note no END statement nor ending delimiter.
In response to the STOP ACTION simulations:
Luciano Fantuzzi suggested to cause a deliberate error; this might cause problems though.
It was my first approach too in a sanity-check like trigger. It calls a procedure which does the actual check and then assigns either 1 or 0 to @resultBool - 0 meaning it did not pass. In that case i wanted to prevent the INSERT by causing a deliberate error in the form of updating a non-existing table. This is what my attempt looked like:
CREATE TRIGGER sanityCheck
BEFORE INSERT ON someTable
FOR EACH ROW
BEGIN
CALL doSanityCheck(@resultBool, @resultMessage);
IF @resultBool = 0 THEN
UPDATE ThereWasAnError_Call_privilegeSanityCheck_ToViewTheError SET ThereWas='an error';
END IF;
END;//
While mysql allows the trigger even though the table doesn't exist, it will _always_ complain (throw an error) when it executes the trigger, even if @resultBool = 1. So, this will not work.
The suggestion of Nicolas LESCURE does work in combination with the IF-statement
On Stop Action problems.
My solution was to create a unique/primary key on the table I want to insert into (in this case on S_ID), then if my sanity check fails I change the s_id to 0. This method will only ever create one duff record with an s_id of 0. Obviously you need to INSERT ignore!
CREATE TRIGGER sanityCheck
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
IF something THEN
SET NEW.S_ID = 0 ;
END IF;
END;
To invoke a shell command from a trigger I used a roundabout approach, write to a predefined folder using trigger code 'select into outfile', and famd to monitor that folder, which triggers a php script.
[http://www.php-trivandrum.org/code-snippets/invoke-shell-from-mysql-trigger.html Invoke shell from MySQL trigger]
The up-to-date link for the above is:
http://www.php-trivandrum.org/code-snippets/invoke-shell-from-mysql-trigger/
-- pete
Add your own comment.