CREATE [OR REPLACE] [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }] TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [{ FOLLOWS | PRECEDES } other_trigger_name ] trigger_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.
CREATE TRIGGER
requires the TRIGGER
privilege for the table associated with the trigger. (Before MySQL 5.1.6, this statement requires the SUPER
privilege.)
You can have multiple triggers for the same trigger_time
and trigger_event
.
For valid identifiers to use as trigger names, see Identifier Names.
If used and the trigger already exists, instead of an error being returned, the existing trigger will be dropped and replaced by the newly defined trigger.
The DEFINER
clause determines the security context to be used when checking access privileges at trigger activation time.
If the IF NOT EXISTS
clause is used, the trigger will only be created if a trigger of the same name does not exist. If the trigger already exists, by default a warning will be returned.
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
statements on the table do not activate this trigger, because they do not use DELETE
. Dropping a partition does not activate DELETE
triggers, either. The FOLLOWS other_trigger_name
and PRECEDES other_trigger_name
options were added in MariaDB 10.2.3 as part of supporting multiple triggers per action time. This is the same syntax used by MySQL 5.7, although MySQL 5.7 does not have multi-trigger support.
FOLLOWS
adds the new trigger after another trigger while PRECEDES
adds the new trigger before another trigger. If neither option is used, the new trigger is added last for the given action and time.
FOLLOWS
and PRECEDES
are not stored in the trigger definition. However the trigger order is guaranteed to not change over time. mysqldump and other backup methods will not change trigger order. You can verify the trigger order from the ACTION_ORDER
column in INFORMATION_SCHEMA.TRIGGERS table.
SELECT trigger_name, action_order FROM information_schema.triggers WHERE event_object_table='t1';
CREATE DEFINER=`root`@`localhost` TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
OR REPLACE and IF NOT EXISTS
CREATE DEFINER=`root`@`localhost` TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.animals = animal_count.animals+1; ERROR 1359 (HY000): Trigger already exists CREATE OR REPLACE DEFINER=`root`@`localhost` TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.animals = animal_count.animals+1; Query OK, 0 rows affected (0.12 sec) CREATE DEFINER=`root`@`localhost` TRIGGER IF NOT EXISTS increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.animals = animal_count.animals+1; Query OK, 0 rows affected, 1 warning (0.00 sec) SHOW WARNINGS; +-------+------+------------------------+ | Level | Code | Message | +-------+------+------------------------+ | Note | 1359 | Trigger already exists | +-------+------+------------------------+ 1 row in set (0.00 sec)
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/create-trigger/