The Ultimate Guide to High Availability with MariaDB

CREATE TRIGGER

You are viewing an old version of this article. View the current version here.

Syntax

CREATE [OR REPLACE]
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW
   [{ FOLLOWS | PRECEDES } other_trigger_name ]
   trigger_stmt

Contents

  1. Syntax
  2. Description
    1. OR REPLACE
    2. DEFINER
    3. IF NOT EXISTS
    4. trigger_time
    5. trigger_event
      1. FOLLOWS/PRECEDES other_trigger_name
  3. Example
  4. See also

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. (Before MySQL 5.1.6, this statement requires the SUPER privilege.)

MariaDB starting with 10.2.3

You can have multiple triggers for the same trigger_time and trigger_event.

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.

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

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.

Example

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;

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.