Trigger

A trigger is a database object that is related to a table. A trigger can be activated when a statement inserts, updates, or deletes a row in the associated table, or when a triggering event occurs before or after the 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 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.

The special INSERT INTO ... ON DUPLICATE KEY UPDATE statement activates a BEFORE INSERT trigger for each row. Then, it activates either an AFTER INSERT trigger or BEFORE UPDATE and AFTER UPDATE triggers. Whether an AFTER INSERT trigger or BEFORE UPDATE and AFTER UPDATE triggers are activated depends on whether the row has a duplicate key.

Create a trigger

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 trigger is activated, that is, before or after each row is inserted into the table.

• INSERT, UPDATE, or DELETE indicates the type of operation that activates the trigger.

• FOR EACH ROW defines the body of the trigger. The statements in the body are executed every time the trigger is activated. For each row affected by the triggering operation, the statements in the body are also executed.

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. Here, columnName is the name of a column in the relevant 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.

• OLD.columnName is read-only, while NEW.columnName can be assigned values by using the SET statement in the trigger.

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 up the values of the columns in 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 triggers trg2_t and trg3_t to make them execute after trigger trg1_t is activated, and create trigger trg4_t to make it execute before trigger trg1_t is activated.

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;
+----+----------------------+
| 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 a defined trigger has multiple statements to be executed, you can use the BEGIN ... END statements to enclose the statements. BEGIN indicates the start of the code block, and END indicates the end of the code block.

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 terminated 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 results in an error because the interpreter cannot find the matching END statement to match 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 statement enclosed in BEGIN ... END is not terminated when it encounters a semicolon (;) but is terminated only when it encounters the delimiter specified in the DELIMITER 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 contain stored procedures with dynamic SQL statements. However, stored procedures or functions can return data to triggers through OUT or IN OUT parameters.

• You cannot use statements that start or end a transaction in triggers. For example, you cannot use START TRANSACTION, COMMIT, or ROLLBACK, but you can roll back to a savepoint because rolling back to a savepoint 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.