Triggers

Triggers are database objects that are related to tables. A trigger can fire when a row is inserted into, updated in, or deleted from a related table by using a statement. You can specify whether a trigger fires before or after a triggering event occurs.

For example, you can use the INSERT or LOAD DATA statement to insert rows. The INSERT trigger fires each time a row is inserted. If two rows are inserted at a time, the INSERT trigger fires twice. You can specify whether a trigger fires each time before a row is inserted into a table or after a row in the table is updated.

Trigger types

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

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

• UPDATE: A trigger of this type fires when you update a row by using an UPDATE statement.

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

The INSERT INTO ... ON DUPLICATE KEY UPDATE statement is a special case in which a BEFORE INSERT trigger fires 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 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

• The CREATE privilege on triggers

• Privileges on statements to be executed after the trigger fires

The syntax is as follows:

CREATE
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON table_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:

• IF NOT EXISTS: If you try to create a trigger whose name already exists without using the IF NOT EXISTS option, an error message is returned. If you use the IF NOT EXISTS option, a warning message instead of an error message is returned.

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

• table_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 triggering event, which is the type of the operation that fires the trigger.

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

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.

Example 1: 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

Example 2: Create two triggers named trg2_t and trg3_t that fire after the trg1_t trigger, and create a trigger named trg4_t trigger that fires before the trg1_t trigger.

CREATE TABLE t(c1 INT);
CREATE TABLE msg(c1 INT AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(100));
CREATE TRIGGER trg1_t BEFORE INSERT ON t FOR EACH ROW
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg1_t');
END;
/
CREATE TRIGGER trg2_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg2_t');
END;
/
CREATE TRIGGER trg3_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg3_t');
END;
/

CREATE TRIGGER trg4_t BEFORE INSERT ON t FOR EACH ROW PRECEDES trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg4_t');
END;
/
INSERT INTO t VALUES (1);

obclient> SELECT * FROM msg;

The return result is as follows:

+----+----------------------+
| c1 | c2                   |
+----+----------------------+
|  1 | BEFORE INSERT trg4_t |
|  2 | BEFORE INSERT trg1_t |
|  3 | BEFORE INSERT trg3_t |
|  4 | BEFORE INSERT trg2_t |
+----+----------------------+
4 rows in set

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 syntax of the BEGIN … END statement 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_delemiter

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

Here is an 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 ;

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

View trigger metadata

You can perform any of the following operations to obtain the metadata of a trigger:

• Query the INFORMATION_SCHEMA TRIGGERS view. For more information, see INFORMATION_SCHEMA TRIGGERS.

• Execute the SHOW CREATE TRIGGER statement. For more information, see SHOW CREATE TRIGGER.

• Execute the SHOW TRIGGERS statement. For more information, see SHOW TRIGGERS.