Create a DML trigger

This topic describes how to create a trigger.

Prerequisites

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

• The TRIGGER privilege

• Privileges on statements to be executed after the trigger fires

Syntax

You can use a CREATE statement to create a trigger.

The syntax is as follows:

CREATE
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body;

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

The syntax is described as follows:

• trigger_name: the name of the trigger, which must be unique.

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

• BEFORE | 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 | DELETE: the triggering event.

• FOR EACH ROW: specifies whether a trigger is a row trigger. When a row trigger fires, statements in the trigger are executed once for each row that is affected by the triggering event.

• FOLLOWS | PRECEDES: the order of triggers. OceanBase Database allows you to associate multiple triggers of the same triggering event and firing time to the same table. By default, triggers with the same triggering event and firing time fire in the order of their creation time. You can use FOLLOWS and PRECEDES to specify the order of the triggers. If you specify FOLLOWS, a new trigger fires after the current trigger. If you specify PRECEDES, a new trigger fires before the current trigger.

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

• For an INSERT trigger, NEW.columnName specifies the new data that is to be inserted in a BEFORE scenario or has been inserted in an AFTER scenario.

  Here, columnName refers to a column name in the corresponding table.

• For an UPDATE trigger, OLD.columnName specifies the original data that is to be updated or has been updated, while NEW.columnName specifies the new data that is to be or has been updated to.

• For a DELETE trigger, OLD.columnName specifies the original data that is to be deleted or has been deleted.

• OLD.columnName is read-only, whereas NEW.columnName can be specified by using a SET statement.

In addition, if you want to create a trigger that contains multiple statements, you can use the BEGIN ... END statement to specify the start and end of the entire code block.

The BEGIN ... END syntax is as follows:

BEGIN
[statement_list]
END

In the preceding syntax, statement_list specifies a list of one or more statements to be executed. Each statement in the list must end with a semicolon (;). A semicolon (;) also indicates the end of an SQL statement. If the system detects a semicolon (;), the system determines it as the end of a statement and starts to execute the statement. In this case, an error is reported because the interpreter fails to find the END keyword that matches the BEGIN keyword during SQL execution. To prevent the error, you can use a DELIMITER command to change the delimiter of a statement.

Here is a sample DELIMITER command:

DELIMITER new_delimiter

Here, new_delemiter specifies the delimiter of a statement. A delimiter can be a sign of one or more bytes in length. The default delimiter is a semicolon (;). You can change the semicolon (;) to another delimiter, such as a number sign (#).

After the DELIMITER command is added to the syntax, statements with semicolons (;) following the command can be executed without an error reported. This is because the system does not consider that a statement ends until it detects the specified delimiter, such as a number sign (#).

Examples

• Create a trigger named test_trg, associate it with a table named test to trigger an INSERT operation, and use the trigger as an accumulator to return the sum of column values inserted into the table.

  obclient>CREATE TABLE test (user_id INT, user_num DECIMAL(10,2));
  Query OK, 0 rows affected
  
  obclient> CREATE TRIGGER test_trg BEFORE INSERT ON test
        FOR EACH ROW SET @sum = @sum + NEW.user_num;
  Query OK, 0 rows affected
  

• Create a trigger with multiple statements.

  obclient>CREATE TABLE test (user_id INT, user_num DECIMAL(10,2));
  Query OK, 0 rows affected
  
  obclient>DELIMITER #
  
  obclient>CREATE TRIGGER test_trg BEFORE UPDATE ON test
                FOR EACH ROW
                BEGIN
                IF NEW.user_num < 1 THEN
                SET NEW.user_num  = 1;
                ELSEIF NEW.user_num > 45 THEN
                SET NEW.user_num= 45;
              END IF;
              END;#
  Query OK, 0 rows affected
  
  obclient>DELIMITER;
  

Limitations on triggers

Triggers in MySQL mode have the following limitations:

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

• A trigger cannot use the CALL statement to call a stored procedure that returns data to the client or uses a dynamic SQL statement. However, a stored procedure or function can use an OUT or IN OUT parameter to return data to a trigger.

• 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 action cannot fire 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.