Trigger

A trigger is a database object that is related to a table. A trigger is activated when a statement inserts, updates, or deletes a row in the associated table. You can also set a trigger to be activated before or after the triggering event.

For example, you can execute the INSERT or LOAD DATA statement to insert a row, and an INSERT trigger is activated each time a row is inserted. If you insert two rows at a time in batches, the trigger is activated twice. You can activate a trigger before each row is inserted or after each row is updated in the table.

Trigger types

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

• INSERT trigger: The trigger is activated when a row is inserted. You can execute the INSERT, LOAD DATA, or REPLACE statement to insert a row, thereby triggering the trigger.

• UPDATE trigger: The trigger is activated when a row is updated. You can execute the UPDATE statement to update a row, thereby triggering the trigger.

• DELETE trigger: The trigger is activated when a row is deleted. You can execute the DELETE or REPLACE statement to delete a row, thereby triggering the trigger.

A special point about the INSERT INTO ... ON DUPLICATE KEY UPDATE statement is that it will activate one BEFORE INSERT trigger for each row. Then, it will activate either an AFTER INSERT trigger or a pair of BEFORE UPDATE and AFTER UPDATE triggers. Whether an AFTER INSERT trigger or a pair of BEFORE UPDATE and AFTER UPDATE triggers is activated depends on whether the row has a duplicate key.

Trigger creation

You can execute the CREATE TRIGGER statement to create a trigger.

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

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

• The CREATE privilege on the trigger.

• The privileges required to execute the statement to be executed when the trigger is activated.

The syntax of the SQL statement for creating a trigger is as follows:

CREATE
    TRIGGER 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:

• The trigger name trigger_name must be unique.

• table_name indicates the name of the table on which the trigger is created.

• BEFORE or AFTER indicates the time when the triggering action is performed.

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

• FOR EACH ROW defines the body of the trigger. This statement is executed each time the trigger is activated. In FOR EACH ROW triggers, this statement is executed for each row affected by the triggering event.

In OceanBase Database, NEW.columnName and OLD.columnName are also defined:

• In an INSERT trigger, NEW.columnName indicates the new data to be inserted or the new data that has been inserted. columnName is the name of a column in the data table.

• In an UPDATE trigger, OLD.columnName indicates the original data to be modified or the data that has been modified, and NEW.columnName indicates the new data to be inserted or the new data that has been inserted.

• In a DELETE trigger, OLD.columnName indicates the original data to be deleted or the data that has been deleted.

• You can use SET to assign values to NEW.columnName, but you cannot modify the values in OLD.columnName.

Example 1: Create a trigger named test_trg and associate it with the test table to activate the INSERT operation. The trigger serves as an accumulator to sum the values in a column of the table to which the trigger is applied.

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 trg2_t and trg3_t to make them execute after trg1_t and create trg4_t to make it execute before trg1_t.

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

If a trigger has multiple statements to be executed, you can use the BEGIN ... END statements to enclose the statements, which indicates the beginning and end of the code block, respectively.

The syntax of the BEGIN ... END statements is as follows:

BEGIN
[statement_list]
END

In this syntax, statement_list indicates a list of one or more statements. Each statement in the list must be ended with a semicolon (;). In an SQL statement, the semicolon (;) indicates the end of a statement, after which the system starts to execute the statement. This will cause an error because the interpreter cannot find the matching END statement for the BEGIN statement. To avoid this error, you can use the DELIMITER statement to change the statement delimiter.

Here is an example of the DELIMITER statement:

DELIMITER new_delemiter

In this example, new_delemiter can be one or more byte-length symbols. By default, it is a semicolon (;). You can change it to another symbol, such as #.

After you execute the DELIMITER statement, a semicolon (;) in statements following the DELIMITER statement will not cause an error until the statement encounters the specified end symbol (#), which is then considered to be the end of the statement.

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

The following limitations apply to triggers in MySQL mode:

• You can create triggers only on permanent tables, not on temporary tables.

• Triggers cannot use the CALL statement to return data to the client or store procedures that contain dynamic SQL statements. However, a procedure or function can return data to a trigger through OUT or IN OUT parameters.

• You cannot use statements that start or end a transaction in a trigger. For example, you cannot use START TRANSACTION, COMMIT, or ROLLBACK. However, you can roll back to a savepoint because this does not end the transaction.

• Foreign keys do not activate triggers.

• Triggers do not return values and therefore cannot contain a return statement. If you want to stop a trigger immediately, you must use the LEAVE statement.

View trigger metadata

You can perform the following operations to view the metadata related to triggers:

• Query the TRIGGERS table in the INFORMATION_SCHEMA database. 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.