Create a trigger

You can create a DML trigger on a table or a view. A DML trigger event consists of the DELETE, INSERT, and UPDATE DML statements.

Syntax

You can use a CREATE TRIGGER statement to create a trigger.

To create a trigger, you must have the following privileges:

• Privileges on the table associated with the trigger, such as SELECT, INSERT, UPDATE, and DELETE privileges

• CREATE TRIGGER privilege

• Privileges on statements to be executed after the trigger fires

Syntax:

CREATE
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

Note

• trigger_name: the name of the trigger. The name must be unique.

• tbl_name: the name of the table for which the trigger is created.

• BEFORE or AFTER: specifies whether the trigger fires before or after the triggering event. For example, whether the trigger fires before or after each row is inserted into the associated table.

• INSERT, UPDATE, or DELETE: the type of operation for which the trigger fires.

• FOR EACH ROW: the trigger entity. This statement is executed when the trigger fires, and is executed for each row affected by the trigger event.

OceanBase Database also supports NEW.columnName and OLD.columnName.

• For an INSERT trigger, NEW.columnName specifies new data that is inserted in a BEFORE scenario or was inserted in an AFTER scenario.

  columnName indicates a column name in the corresponding data table.

• For an UPDATE trigger, OLD.columnName specifies existing data that is updated. NEW.columnName specifies new data after the update.

• For a DELETE trigger, OLD.columnName specifies existing data that is deleted.

• Values in OLD.columnName are read-only, whereas values in NEW.columnName can be specified by using SET statements.

Example

Create a simple trigger.

1. Create a table named regions.

   obclient> CREATE TABLE regions(
                region_id    NUMBER(5,0),
               region_name  VARCHAR(50)
              );
   Query OK, 0 rows affected
   

2. Insert data into the regions table.

   obclient> INSERT INTO regions VALUES(1,'Europe'),(4,'Middle East and Africa'),(3,'Asia'),(2,'Americas');
   d Query OK, 4 rows affected 
   Records: 4  Duplicates: 0  Warnings: 0
   

3. Create a table named reg_his.

   obclient> CREATE TABLE reg_his AS SELECT * FROM regions WHERE 1=2;
   Query OK, 0 rows affected
   

4. Create a trigger named del_new_region.

   obclient> CREATE TRIGGER del_new_region
               BEFORE DELETE ON regions
               FOR EACH ROW
               BEGIN
               INSERT INTO reg_his(region_id , region_name )
               VALUES( old.region_id, old.region_name );
             END;/
   Query OK, 0 rows affected 
   

   Note

   Before creating the trigger, use the Delimiter function to specify slashes (/) as the delimiter, so that semicolons (;) will not be taken as the delimiter and statements before semicolons (;) will not be executed until the actual delimiter is detected.

5. View the del_new_region trigger.

   obclient> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME= 'del_new_region'\G;
   *************************** 1. row ***************************
              TRIGGER_CATALOG: def
               TRIGGER_SCHEMA: ny
                 TRIGGER_NAME: del_new_region
           EVENT_MANIPULATION: DELETE
         EVENT_OBJECT_CATALOG: def
          EVENT_OBJECT_SCHEMA: ny
           EVENT_OBJECT_TABLE: regions
                 ACTION_ORDER: 0
             ACTION_CONDITION: NULL
             ACTION_STATEMENT: TRIGGER del_new_region
            BEFORE DELETE ON regions
          FOR EACH ROW
      BEGIN
         INSERT INTO reg_his(region_id , region_name )
              VALUES( old.region_id, old.region_name );
      END
           ACTION_ORIENTATION: ROW
                ACTION_TIMING: BEFORE
   ACTION_REFERENCE_OLD_TABLE: NULL
   ACTION_REFERENCE_NEW_TABLE: NULL
     ACTION_REFERENCE_OLD_ROW: OLD
     ACTION_REFERENCE_NEW_ROW: NEW
                      CREATED: NULL
                     SQL_MODE: STRICT_ALL_TABLES,NO_ZERO_IN_DATE
                      DEFINER: 'root'@'%'
         CHARACTER_SET_CLIENT: utf8mb4
         COLLATION_CONNECTION: utf8mb4
           DATABASE_COLLATION: utf8mb4
   1 row in set
   

   Note

   Parameters in the preceding statement:

   • TRIGGER_SCHEMA: the database where the trigger resides.

   • TRIGGER_NAME: the name of the trigger.

   • EVENT_OBJECT_TABLE: the data table where the trigger fires.

   • ACTION_STATEMENT: the action performed when the trigger fires.

   • ACTION_ORIENTATION: The value ROW indicates that the trigger fires on each record.

   • ACTION_TIMING: the time when the trigger fires.

Limits

Triggers in MySQL mode have the following limits:

• Multiple triggers that are associated with the same table cannot share the triggering event or firing time.

• You can create a trigger only for a permanent table, but not for a temporary table.

• Triggers cannot use the CALL statement to return data to clients or use the stored procedures of dynamic SQL statements. However, stored procedures and functions can use OUT or INOUT to return data to triggers.

• Triggers cannot use clauses to start or end transactions, such as START TRANSACTION for starting a transaction, COMMIT for committing a transaction, and ROLLBACK for rolling back a transaction. However, triggers can roll back a transaction to a specific savepoint because this operation does not end the transaction.

• A foreign key cannot activate a trigger.

• A trigger cannot return a value. Make sure that the trigger does not contain a statement that is used to return values. If you want to instantly stop a trigger, use a LEAVE statement.