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 insert a row by using the INSERT or LOAD DATA statement. Each time a row is inserted, an INSERT trigger is activated. 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 into a table or after each row is updated.

Types of triggers

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 INSERT INTO ... ON DUPLICATE KEY UPDATE statement is special in that it activates one BEFORE INSERT trigger for each row. Then, it activates either AFTER INSERT triggers or BEFORE UPDATE and AFTER UPDATE triggers. Whether AFTER INSERT triggers or BEFORE UPDATE and AFTER UPDATE triggers are 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 SELECT, INSERT, UPDATE, and DELETE privileges on the table associated with the trigger.

• The CREATE privilege on the trigger.

• The privilege 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 when the trigger is activated. For example, it indicates whether the trigger is activated before or after each row is inserted into the table.

• INSERT, UPDATE, or DELETE indicates the trigger 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 and 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. 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 used in the trigger with the SET statement for assignment.

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

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

If you need to define a trigger with multiple statements, you can use the BEGIN ... END statements to enclose the statements, indicating the beginning and end of the code block.

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

BEGIN
[statement_list]
END

Here, statement_list indicates a list of one or more statements, with each statement ending with a semicolon (;). In an SQL statement, the semicolon (;) indicates the end of a statement, signaling the system to start executing that statement. This can lead to an error when the interpreter cannot find a matching END for the BEGIN during execution, because the semicolon is treated as the end of the statement. To avoid this, you can use the DELIMITER statement to change the statement delimiter.

Here is an example of the DELIMITER statement:

DELIMITER new_delemiter

Here, 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 use the DELIMITER statement to change the delimiter, a semicolon (;) in a statement after the DELIMITER statement will not cause an error, until it encounters the specified delimiter (#), which is then considered 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 to store procedures that contain dynamic SQL statements. However, a store procedure or function can return data to a trigger through OUT or IN OUT parameters.

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