Create a DML trigger|V4.2.0| docs|Distributed Database

Create a DML trigger

Last Updated：2023-10-31 11:17:11 Updated

You can create a DML trigger on a table or a view. A triggering event consists of the DELETE, INSERT, and UPDATE DML statements.

Applicability

This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.

The current version of OceanBase Database supports the following types of DML row-level triggers:

BEFORE INSERT FOR EACH ROW
AFTER INSERT FOR EACH ROW
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE FOR EACH ROW
BEFORE DELETE FOR EACH ROW
AFTER DELETE FOR EACH ROW

Unlike stored procedures and packages, a trigger has a separate namespace. Therefore, the name of a trigger can be identical to the name of a table or stored procedure.

Syntax

You can create a DML trigger by using the following syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column ...]]}
ON [schema.] table_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
FOR EACH ROW
[WHEN condition]
[FOLLOWS | PRECEDES] other_trigger_name
trigger_body;

The following table describes the parameters in the syntax.

Parameter	Description	Remarks
BEFORE and AFTER	Specifies whether the trigger fires before or after the triggering event.	A BEFORE trigger fires before the triggering event is executed. An AFTER trigger fires after the triggering event is executed.
FOR EACH ROW	Specifies the trigger as a row-level trigger.	If a DML statement affects multiple rows of data in a table, the row-level trigger fires for each row provided that the rows meet the trigger condition.
REFERENCING	Specifies the correlation name. The correlation name can be used to reference the current new and old column values in the Procedural Language (PL) block and `WHEN` clause of the row-level trigger. The default correlation names are `OLD` and `NEW`.	When you use the correlation names in the PL block of the trigger, you must add a colon (:) before them. In the `WHEN` clause, you must not add the colon (:).
FOLLOWS and PRECEDES	Specifies the firing sequence of triggers with the same point in time. The specified triggers must exist and be correctly compiled. However, you do not need to enable the specified triggers.	FOLLOWS: indicates that the new trigger fires after the specified trigger. PRECEDES: indicates that the new trigger fires before the specified trigger.
WHEN	Specifies the trigger condition.	If the condition is a logical expression, it must contain the correlation name, and cannot contain a query statement or call a PL function. You can use the `WHEN` clause to specify a trigger condition only for row-level `BEFORE` and `AFTER` triggers.

Notice

When an INSERT, UPDATE, or DELETE operation is performed on a base table, the corresponding stored procedure must be executed by the application. However, a trigger associated with the base table automatically fires upon any DML operation on the table, which is irrelevant to the application, to ensure data consistency and integrity.

Limitations on triggers

Note the following limitations when you use triggers:

The SELECT statement in a trigger must use the SELECT ... INTO ... syntax, or be the SELECT statement used to define a cursor.
Transaction control statements such as COMMIT, ROLLBACK, and SAVEPOINT cannot be used in a trigger.
A stored procedure or function called by a trigger cannot contain database transaction control statements either.

OLD and NEW pseudorecords

When a trigger is executed, it needs to reference the column values in records for INSERT, UPDATE, or DELETE operations and sometimes needs to reference the column values before or after the operations.

:NEW indicates the value of a column after an operation.
:OLD indicates the value of a column before an operation.

The following table lists the validity of NEW and OLD records in different operations.

Feature	INSERT	UPDATE	DELETE
OLD	NULL	Valid	Valid
NEW	Valid	Valid	NULL

Example: Create a simple trigger.

obclient> CREATE TABLE regions(
        region_id     NUMBER(5,0),  
        region_name  VARCHAR(50)
    );
Query OK, 0 rows affected

obclient> INSERT INTO regions VALUES(1,'Europe'),(4,'Middle East and Africa'),(3,'Asia'),(2,'Americas');
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

obclient> CREATE TABLE reg_his AS SELECT * FROM regions WHERE 1=2;
Query OK, 0 rows affected

obclient> CREATE OR REPLACE TRIGGER del_new_region
      BEFORE DELETE ON regions
      FOR EACH ROW
      WHEN (old.region_id >3)
   BEGIN
      INSERT INTO reg_his(region_id , region_name )
           VALUES( :old.region_id, :old.region_name );
   END;
 /
Query OK, 0 rows affected

In the preceding example, when data is deleted from the regions table, records with a region_id value greater than 3 are moved to the reg_his table.

obclient> SELECT * FROM regions;
+-----------+------------------------+
| REGION_ID | REGION_NAME            |
+-----------+------------------------+
|         1 | Europe                 |
|         4 | Middle East and Africa |
|         3 | Asia                   |
|         2 | Americas               |
+-----------+------------------------+
4 rows in set

obclient> SELECT * FROM reg_his;
Empty set

obclient> DELETE FROM regions WHERE region_id>2;
Query OK, 2 rows affected

obclient> SELECT * FROM regions;
+-----------+-------------+
| REGION_ID | REGION_NAME |
+-----------+-------------+
|         1 | Europe      |
|         2 | Americas    |
+-----------+-------------+
2 rows in set

obclient> SELECT * FROM reg_his;
+-----------+------------------------+
| REGION_ID | REGION_NAME            |
+-----------+------------------------+
|         4 | Middle East and Africa |
+-----------+------------------------+
1 row in set