Trigger

A trigger is a database object related to a table. A trigger can be activated when a statement inserts, updates, or deletes a row in the associated table, and can also be set 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 into the 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.

A special point about the INSERT INTO ... ON DUPLICATE KEY UPDATE statement is that it activates a BEFORE INSERT trigger for each row. Then, it 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 on 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. For example, the trigger is activated before or after each row is inserted into the table.

• 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, 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 (BEFORE) or the new data that has been inserted (AFTER). Here, columnName is the name of a column in the relevant data table.

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

• In a DELETE trigger, OLD.columnName indicates the original data to be deleted or 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 works as an accumulator to sum 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;

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, you can use the BEGIN ... END statement to define multiple statements for a trigger. The BEGIN keyword indicates the start of the code block, and the END keyword indicates the end of the code block.

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

BEGIN
[statement_list]
END

In this statement, 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 causes the interpreter to report an error because it fails to find the matching END keyword when it encounters the BEGIN keyword. To avoid such errors, you can use the DELIMITER command to change the statement delimiter.

Here is an example of the DELIMITER command:

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 command, a semicolon (;) in a statement after the DELIMITER command will no longer cause an error, until the statement delimiter (#) is encountered, 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 an OUT or IN OUT parameter.

• You cannot use statements for starting or ending a transaction in triggers. 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.