# CREATE TRIGGER

## Syntax

```sql
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](https://mariadb.com/docs/server/server-usage/triggers-events/triggers). 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](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant#table-privileges) 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](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/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](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/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](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant#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

{% tabs %}
{% tab title="Current" %}
Multiple *`trigger_event`* events can be specified.
{% endtab %}

{% tab title="< 12.0" %}
Only one *`trigger_event`* can be specified.
{% endtab %}
{% endtabs %}

`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](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data), [LOAD DATA](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/load-data-into-tables-or-index/load-data-infile), and [REPLACE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/replace) statements.
* `UPDATE`: The trigger is activated whenever a row is modified; for example, through [UPDATE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update) statements.
* `DELETE`: The trigger is activated whenever a row is deleted from the table; for example, through [DELETE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete) and [REPLACE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/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](https://mariadb.com/docs/server/clients-and-utilities/backup-restore-and-import-clients/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](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-triggers-table) table.

```sql
SELECT trigger_name, action_order FROM information_schema.triggers 
  WHERE event_object_table='t1';
```

### Atomic DDL

{% tabs %}
{% tab title="Current" %}
MariaDB supports [Atomic DDL](https://mariadb.com/docs/server/reference/sql-statements/data-definition/atomic-ddl), and `CREATE TRIGGER` is atomic.
{% endtab %}

{% tab title="< 10.6" %}
MariaDB does **not** support [Atomic DDL](https://mariadb.com/docs/server/reference/sql-statements/data-definition/atomic-ddl).
{% endtab %}
{% endtabs %}

## Examples

### Creating a Trigger

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

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

```sql
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 ;
```

## See Also

* [Identifier Names](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-names)
* [Trigger Overview](https://mariadb.com/docs/server/server-usage/triggers-events/triggers/trigger-overview)
* [DROP TRIGGER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-trigger)
* [Information Schema TRIGGERS Table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-triggers-table)
* [SHOW TRIGGERS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-triggers)
* [SHOW CREATE TRIGGER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-trigger)
* [Trigger Limitations](https://mariadb.com/docs/server/server-usage/triggers-events/triggers/trigger-limitations)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}