Create a DML trigger

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

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.

Create a DML trigger

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]
trigger_body;

The following table describes the parameters in the syntax.

Notice

After an INSERT, UPDATE, or DELETE operation modifies a base table, the corresponding stored procedures are automatically triggered. Therefore, this operation is irrelevant to applications. You can use triggers to ensure data consistency and integrity.

Limits

Take note of the following limits when you use triggers:

• The SELECT statement in a trigger must use the SELECT ... INTO ... structure, 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.

Pseudorecords OLD and NEW

When a trigger is executed, it needs to reference the column values of inserted, updated, or deleted records and sometimes need to the column values before or after the operations.

• :NEW indicates the value of a column after the access operation.

• :OLD indicates the value of a column before the access operation.

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

Example: Create a simple trigger.

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

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

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

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 (0.06 sec)

In the preceding example, when data is deleted from the regions table, records with a region_id 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 (0.00 sec)

obclient>SELECT * FROM reg_his;
Empty set (0.00 sec)

obclient>DELETE FROM regions WHERE region_id>2;
Query OK, 2 rows affected (0.29 sec)

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

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