Skip navigation links
13.1.20 CREATE VIEW Syntax »
Section Navigation      [Toggle]

13.1.19. CREATE TRIGGER 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 requires the TRIGGER privilege for the table associated with the trigger. The statement might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as described in Section 19.7, “Binary Logging of Stored Programs”.

The DEFINER clause determines the security context to be used when checking access privileges at trigger activation time. 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 server SQL mode in effect when the event begins executing.

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 valid DEFINER user values:

  • If you do not have the SUPER privilege, the only permitted 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 valid 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.

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 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.

For more information about trigger security, see Section 19.6, “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. 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 6.3.12, “SQL-Based MySQL Account Activity Auditing”.

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”.

In MySQL 5.6, 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)
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Kai Baku on December 3 2005 11:59pm[Delete] [Edit]

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
;

Posted by Steve Doyle on March 21 2006 1:06am[Delete] [Edit]

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.

Posted by Jonathan Haddad on September 15 2006 10:59pm[Delete] [Edit]

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/

Posted by Scott White on November 28 2006 9:31pm[Delete] [Edit]

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.

Posted by Luciano Fantuzzi on April 16 2007 8:47pm[Delete] [Edit]

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.

Posted by Philip Mather on April 22 2007 10:58pm[Delete] [Edit]

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.

Posted by Nicolas LESCURE on August 13 2007 1:37pm[Delete] [Edit]

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').

Posted by Anony Mous on June 20 2008 8:50pm[Delete] [Edit]

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.

Posted by Martijn Korse on August 8 2008 7:54am[Delete] [Edit]

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

Posted by S Roberts on June 2 2009 2:34pm[Delete] [Edit]

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;

Posted by Jiju Thomas Mathew on June 20 2009 6:21pm[Delete] [Edit]

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]

Posted by Pete Wilson on March 1 2010 10:25am[Delete] [Edit]

The up-to-date link for the above is:

http://www.php-trivandrum.org/code-snippets/invoke-shell-from-mysql-trigger/

-- pete

Posted by Tim McCormack on November 14 2010 2:40am[Delete] [Edit]

Continuing on the theme of how to emulate STOP ACTION, I wrote this code:

delimiter $$

DROP TABLE IF EXISTS blocked_insert_message $$
CREATE TABLE blocked_insert_message (
unique_error_msg VARCHAR(330) NOT NULL,
UNIQUE KEY `unique_error_msg` (`unique_error_msg`)
) $$

DROP PROCEDURE IF EXISTS die_with_error $$
CREATE PROCEDURE die_with_error(msg varchar(300))
COMMENT 'Call this to STOP ACTION with a message.'
MODIFIES SQL DATA BEGIN
DECLARE ts DATETIME DEFAULT NOW();
DECLARE txt VARCHAR(300) DEFAULT msg;
DECLARE uniq VARCHAR(330) DEFAULT (SELECT CONCAT(ts, ': ', txt));
-- Run it twice to throw as an error.
INSERT INTO blocked_insert_message VALUES (uniq);
INSERT INTO blocked_insert_message VALUES (uniq);
END $$

delimiter ;

When called from a trigger with "call die_with_error('test test test test');" the result is an error like this:

ERROR 1062 (23000) at line 1: Duplicate entry '2010-11-13 21:30:02: test test test test' for key 'unique_error_msg'

The clear benefit is that the error message will be thrown reliably and contain arbitrary (though short) free text.

Posted by Rui Da-Costa on August 25 2011 11:02am[Delete] [Edit]

Here's how I got it working using signals for raising the error:
delimiter //
use test//
create table trigger_test
(
id int not null
)//
drop trigger if exists trg_trigger_test_ins //
create trigger trg_trigger_test_ins before insert on trigger_test
for each row
begin
declare msg varchar(255);
if new.id < 0 then
set msg = concat('MyTriggerError: Trying to insert a negative value in trigger_test: ', cast(new.id as char));
signal sqlstate '45000' set message_text = msg;
end if;
end
//

delimiter ;
-- run the following as seperate statements:
insert into trigger_test values (1), (-1), (2); -- everything fails as one row is bad
select * from trigger_test;
insert into trigger_test values (1); -- succeeds as expected
insert into trigger_test values (-1); -- fails as expected
select * from trigger_test;

Posted by Jon Vance on November 1 2012 8:10pm[Delete] [Edit]

I have discovered something that can be VERY important if you don't know about it. When using INSERT IGNORE, insert triggers are STILL FIRED when a duplicate key constraint prevents new rows from being inserted.

Add your own comment.