CREATE TRIGGER

Complete CREATE TRIGGER reference: OR REPLACE, DEFINER, IF NOT EXISTS, FOLLOWS/PRECEDES options for BEFORE/AFTER INSERT, UPDATE, or DELETE triggers rules.

Syntax

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 [, ...]]
  | DELETE

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.

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

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.

DEFINER

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

IF NOT EXISTS

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

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

Current

Multiple trigger_event events can be specified.

< 12.0

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

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. mariadb-dump and other backup methods do 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

Current

MariaDB supports Atomic DDL, and CREATE TRIGGER is atomic.

< 10.6

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)

See Also

• Identifier Names

• Trigger Overview

• DROP TRIGGER

• Information Schema TRIGGERS Table

• SHOW TRIGGERS

• SHOW CREATE TRIGGER

• Trigger Limitations

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}