Triggers

OceanBase Database in MySQL mode is compatible with the triggers of MySQL 5.6. A trigger is a table-related database object that you can activate when you use a statement to insert a row into, update a row of, or delete a row from a table that is associated with the trigger. For example, you can activate an INSERT trigger each time you insert a row by using an INSERT or LOAD DATA statement. If you insert two rows at the same time, the trigger is activated twice.

You can specify whether a trigger is activated before or after a triggering event. For example, you can specify that a trigger is activated before a row is inserted into a table or after a row in the table is updated.

Trigger types

OceanBase Database in MySQL mode supports the following types of triggers:

• INSERT: A trigger of this type is activated when you insert a row by using the INSERT, LOAD DATA, or REPLACE statement.

• UPDATE: A trigger of this type is activated when you update a row by using the UPDATE statement.

• DELETE: A trigger of this type is activated when you delete a row by using the DELETE or REPLACE statement.

The INSERT INTO ... ON DUPLICATE KEY UPDATE statement is a special case in which a BEFORE INSERT trigger is activated for each row, followed by an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, based on whether the row has a duplicate key.

Create a trigger

You can use the 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 is activated

SQL syntax for creating a trigger:

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 }

Syntax description:

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

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

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

• INSERT, UPDATE, or DELETE: the triggering event, which specifies the type of the operation that activates the trigger.

• FOR EACH ROW: specifies whether a trigger is a row trigger. When you activate a row trigger, statements in the trigger are executed once for each row that is affected by the triggering 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 washes inserted in an AFTER scenario. columnName refers to a column name in the corresponding table.

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

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

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

For example, you can use the following syntax to create a trigger named test_trg and then associate the trigger with a table named test to activate INSERT operations. In this example, the trigger is also an accumulator, which can sum the values that are inserted into the columns of 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

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.

BEGIN ... END syntax:

BEGIN
[statement_list]
END

In the preceding syntax, statement_list specifies one or a list of 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.

Sample DELIMITER command:

DELIMITER new_delimiter

new_delimiter 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 (#).

Notice

If you use the DELIMITER command to change the delimiter, make sure that you reset the delimiter to the semicolon (;) after the statements are executed.

Example:

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 ;

Limits on triggers

Triggers in MySQL mode have the following limits:

• Multiple triggers that are associated with one table are not allowed to share the same triggering event and activation time at the same time.

• 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.

• A statement used to start or end a transaction is not allowed in a trigger. For example, the following statements are not allowed: START TRANSACTION, COMMIT, and ROLLBACK. However, rolling back a transaction to a savepoint is allowed because this process 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 the LEAVE statement.