# 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) *This page is licensed: GPLv2, originally from* [*fill\_help\_tables.sql*](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql) {% @marketo/form formId="4316" %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://mariadb.com/docs/server/server-usage/triggers-events/triggers/create-trigger.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.