Syntax
------

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;

Description
-----------

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.

MariaDB starting with 10.2.3
----------------------------
You can have multiple triggers for the same trigger_time and trigger_event.

For valid identifiers to use as trigger names, see Identifier Names.

OR REPLACE
----------

MariaDB starting with 10.1.4
----------------------------
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.

DEFINER
-------

The DEFINER clause determines the security context to be used when checking
access privileges at trigger activation time. Usage requires the SUPER
privilege, or, from MariaDB 10.5.2, the SET USER privilege.

IF NOT EXISTS
-------------

MariaDB starting with 10.1.4
----------------------------
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
------------

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

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.

FOLLOWS/PRECEDES other_trigger_name
-----------------------------------

MariaDB starting with 10.2.3
----------------------------
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. mariadb-dump/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';

Atomic DDL
----------

MariaDB starting with 10.6.1
----------------------------
MariaDB 10.6.1 supports Atomic DDL and CREATE TRIGGER is atomic.

Examples
--------

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)

URL: https://mariadb.com/kb/en/create-trigger/