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 [, column_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.

trigger_stmt is the statement executed when the trigger activates. Here, you can refer to columns in the table associated with the trigger 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.

OLD and NEW are MariaDB (and MySQL) extensions to triggers. They are not case-sensitive.

Triggers cannot use NEW.col_name or use OLD.col_name to refer to generated columns. For information about generated columns, see Generated Columns.

In an INSERT trigger, only NEW.col_name can be used, because there is no old row. In a DELETE trigger, only OLD.col_name can be used, because there is no new row. In an UPDATE trigger, use OLD.col_name to refer to the columns of a row before it is updated, and NEW.col_name to refer to the columns of the row after it is updated.

A column referenced with OLD is read-only. If you have the SELECT privilege, this means you can refer to it, but not modify it. You can refer to a column named with NEW if you have the SELECT privilege. In a BEFORE trigger, you can also change its value with SET NEW.col_name``=``value if you have the UPDATE privilege. This means you can use a trigger to modify the values to be inserted as a new row or to update a row. In an AFTER trigger, a SET statement has no effect, because the row change has already occurred.

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

Multiple trigger_event events 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 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.

`INSERTING`, `UPDATING`, and `DELETING` Predicates

When a trigger is defined for multiple events, the INSERTING, UPDATING, and DELETING predicates can be used within the trigger body to determine which event fired the trigger:

INSERTING: Evaluates to TRUE when the trigger_event is INSERT
UPDATING: Evaluates to TRUE when the trigger_event is UPDATE
DELETING: Evaluates to TRUE when the trigger_event is DELETE

Example

CREATE TRIGGER t1_b_any BEFORE INSERT OR UPDATE OR DELETE ON t1 FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO t2 VALUES (NEW.a, 'INSERTING');
  ELSEIF UPDATING THEN
    INSERT INTO t2 VALUES (NEW.a, 'UPDATING');
  ELSEIF DELETING THEN
    INSERT INTO t2 VALUES (OLD.a, 'DELETING');
  END IF;
END

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

MariaDB supports Atomic DDL, and CREATE TRIGGER is atomic.

Examples

Creating a Trigger

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)

Referencing NEW Column Values

DELIMITER //

CREATE TRIGGER trg_limit_population BEFORE UPDATE ON country_stats
FOR EACH ROW
BEGIN
    -- Ensure population is at least 1
    IF NEW.population < 1 THEN
        SET NEW.population = 1;
    -- Cap population at 2 billion for data integrity
    ELSEIF NEW.population > 2000000000 THEN
        SET NEW.population = 2000000000;
    END IF;
END;
//

DELIMITER ;