Trigger overview

The MySQL-compatible mode of OceanBase Database is compatible with the triggers of MySQL 5.6. Triggers are database objects that are associated with tables and are activated when specific events occur on a table.

To be specific, a trigger can be activated when a row is inserted into, updated in, or deleted from an associated table by using a statement. For example, you can activate an INSERT trigger for each row inserted using the INSERT or LOAD DATA statement. If you insert two rows of data in a batch, the trigger will be activated twice.

Triggers can be set to activate before or after a triggering event. For example, you can activate a trigger before each row is inserted into the table or after each row is updated.

The MySQL-compatible mode of OceanBase Database supports the following types of triggers:

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

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

• DELETE: A trigger of this type is activated 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 is activated 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.