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 into a table. An INSERT trigger is activated each time a row is inserted. If you insert two rows at a time in a batch, the trigger is activated twice. You can activate a trigger before each row is inserted into a table, or after each row is updated.

Types of triggers

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

• INSERT triggers are activated when a row is inserted. You can activate them by using the INSERT, LOAD DATA, or REPLACE statement.

• UPDATE triggers are activated when a row is updated. You can activate them by using the UPDATE statement.

• DELETE triggers are activated when a row is deleted. You can activate them by using the DELETE or REPLACE statement.

A special case is the INSERT INTO ... ON DUPLICATE KEY UPDATE statement. It activates one BEFORE INSERT trigger for each row and then activates 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.

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

Example 1: Create a trigger named test_trg and associate it with the test table to activate the INSERT operation. The trigger acts 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 trigger has multiple statements to be executed, you can use the BEGIN ... END statements to enclose the statements. The BEGIN statement indicates the beginning of a code block, and the END statement 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, which means that the system starts to execute the statement. This causes the interpreter to report an error because it cannot find the matching END statement that matches the BEGIN statement. To avoid this error, you can use the DELIMITER statement to change the statement terminator.

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 the BEGIN ... END statements will not be terminated when it encounters a semicolon (;). The statement is terminated only when it encounters the statement terminator 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 to store procedures that contain dynamic SQL statements. However, a store procedure or function can return data to a trigger through an OUT or IN OUT parameter.

• You cannot use statements for starting or ending a transaction in a trigger. For example, you cannot use START TRANSACTION, COMMIT, or ROLLBACK. However, you can roll back to a savepoint because a rollback to a savepoint does not end a transaction.

• Foreign keys do not activate triggers.

• Triggers do not return values, so triggers 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.