Triggers

A trigger is a database object associated with a table. A trigger can be activated when a statement inserts, updates, or deletes a row in the associated table, or it can be activated before or after the trigger event.

For example, you can insert a row by using the INSERT or LOAD DATA statement. Each time you insert a row, the INSERT trigger is activated. If you insert two rows in a batch, the trigger is activated twice. You can also activate the trigger before inserting each row into the table or after updating each row.

Trigger types

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

• INSERT triggers: These triggers are activated when a row is inserted. You can use the INSERT, LOAD DATA, and REPLACE statements to activate an INSERT trigger.

• UPDATE triggers: These triggers are activated when a row is updated. You can use the UPDATE statement to activate an UPDATE trigger.

• DELETE triggers: These triggers are activated when a row is deleted. You can use the DELETE and REPLACE statements to activate a DELETE trigger.

The INSERT INTO ... ON DUPLICATE KEY UPDATE statement is special. It activates a BEFORE INSERT trigger for each row, followed by an AFTER INSERT trigger or a BEFORE UPDATE and AFTER UPDATE trigger. Whether an AFTER INSERT trigger or a BEFORE UPDATE and AFTER UPDATE trigger is activated depends on whether the row has a duplicate key.

Creating triggers

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

The user who creates a trigger must have the following privileges:

• Privileges on the table associated with the trigger, including SELECT, INSERT, UPDATE, and DELETE.

• The CREATE privilege.

• Privileges on the statements to be executed after the trigger is activated.

The syntax for creating a trigger is as follows:

CREATE
    TRIGGER [IF NOT EXISTS] 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:

• IF NOT EXISTS: If the trigger name already exists and the IF NOT EXISTS option is not specified, an error message is returned. If the IF NOT EXISTS option is specified, a warning is returned instead.

• The trigger_name must be unique.

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

• BEFORE or AFTER specifies the time when the trigger is activated. For example, the trigger is activated before or after each row is inserted into the table.

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

• FOR EACH ROW defines the body of the trigger. The statement is executed each time the trigger is activated. The statement is executed once for each row affected by the trigger event.

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

• In an INSERT trigger, NEW.columnName specifies the new data to be inserted (BEFORE) or already inserted (AFTER). columnName is the name of a column in the corresponding table.

• In an UPDATE trigger, OLD.columnName specifies the original data to be modified or already modified, and NEW.columnName specifies the new data to be inserted (BEFORE) or already inserted (AFTER).

• In a DELETE trigger, OLD.columnName specifies the original data to be deleted or already deleted.

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

Example 1: Create a trigger named test_trg and associate it with the test table. The trigger is activated when an INSERT operation is performed. The trigger acts as an accumulator that sums 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 be executed after trigger trg1_t, and create trigger trg4_t to be executed before trigger 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 contains multiple statements, you can use the BEGIN ... END statement to specify the start and end of the code block.

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

BEGIN
[statement_list]
END

statement_list specifies a list of one or more statements. Each statement in the list must end with a semicolon (;). In SQL statements, a semicolon (;) is used to indicate the end of a statement. When a semicolon is encountered, the system starts executing the statement. However, during execution, the interpreter may encounter an error because it cannot find the END statement that matches the BEGIN statement. To avoid this error, you can use the DELIMITER command to change the statement delimiter.

Here is an example of the DELIMITER command:

DELIMITER new_delemiter

new_delimiter can be a symbol of one or more bytes. By default, it is a semicolon (;). You can change it to another symbol, such as #.

After you add the DELIMITER command, statements following the DELIMITER command do not return an error when they use a semicolon (;) until they encounter the specified delimiter (#), which indicates 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

In MySQL mode, triggers have the following limitations:

• You can create a trigger only on a permanent table, not on a temporary table.

• A trigger cannot use the CALL statement to return data to a client or a stored procedure that contains dynamic SQL. However, a stored procedure or function can return data to a trigger by using an OUT or IN OUT parameter.

• A trigger cannot contain a statement segment that starts or ends a transaction. For example, you cannot use the START TRANSACTION, COMMIT, or ROLLBACK statement. However, you can use the ROLLBACK TO SAVEPOINT statement. This is because rolling back to a savepoint does not end the transaction.

• Foreign keys do not activate triggers.

• A trigger cannot return a value. Therefore, a trigger cannot contain a RETURN statement. If you want to stop a trigger immediately, you must use the LEAVE statement.

Viewing trigger metadata

To obtain metadata about a trigger, perform the following operations:

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