Define a trigger on a table. This statement creates a routine that automatically executes in response to INSERT, UPDATE, or DELETE events.
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 privilege for the table associated with the trigger.
You can have multiple triggers for the same trigger_time and trigger_event.
For valid identifiers to use as trigger names, see .
If used and the trigger already exists, instead of an error being returned, the existing trigger is 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. Usage requires the privilege.
If the IF NOT EXISTS clause is used, the trigger is created only if a trigger of the same name does not exist. If the trigger already exists, by default a warning is 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.
Multiple trigger_event events can be specified.
Only one trigger_event can be specified.
trigger_event indicates the kind of statement that activates the trigger. A 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 , , and statements.
UPDATE: The trigger is activated whenever a row is modified; for example, through statements.
DELETE: The trigger is activated whenever a row is deleted from the table; for example, through and statements. However, DROP TABLE
The FOLLOWS`` other_trigger_name and PRECEDES`` other_trigger_name options support multiple triggers per action time.
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. and other backup methods do not change trigger order.
You can verify the trigger order from the ACTION_ORDER column in table.
MariaDB supports , and CREATE TRIGGER is atomic.
MariaDB does not support .
OR REPLACE and IF NOT EXISTS:
This page is licensed: GPLv2, originally from
CREATE [OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
TRIGGER [IF NOT EXISTS]
trigger_name trigger_time {trigger_event [ OR trigger_event] [...]}
ON tbl_name FOR EACH ROW
[{ FOLLOWS | PRECEDES } other_trigger_name ]
trigger_stmt;
trigger time:
BEFORE
| AFTER
trigger_event:
INSERT
| UPDATE [ OF column_name [, colunm_name [, ...]]
| DELETETRUNCATEDELETEDELETESELECT 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;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)