About triggers

This topic describes the definition and types of triggers in OceanBase Database in MySQL mode.

Definition

OceanBase Database in MySQL mode is compatible with the triggers of MySQL 5.6. Triggers are database objects that are related to tables. A trigger fires when a particular event occurs on a table.

To be specific, a trigger fires when a row is inserted into, updated in, or dropped from a related table by using a statement. For example, you can insert rows by using the INSERT or LOAD DATA statement. The INSERT trigger fires each time a row is inserted. If you insert two rows at the same time, the trigger fires twice.

You can specify whether a trigger fires before or after a triggering event. For example, you can specify that a trigger fires before a row is inserted into a table or after a row in the table is updated.

Types

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

• INSERT: A trigger of this type fires when you insert a row by using an INSERT, LOAD DATA, or REPLACE statement.

• UPDATE: A trigger of this type fires when you update a row by using an UPDATE statement.

• DELETE: A trigger of this type fires when you delete a row by using a DELETE or REPLACE statement.

The INSERT INTO ... ON DUPLICATE KEY UPDATE statement is a special case in which a BEFORE INSERT trigger fires for each row, followed by an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, based on whether the row has a duplicate key.