Create a DML trigger

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

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. You must have the CREATE TRIGGER system privilege to create triggers.

Create a row-level trigger

The current version of OceanBase Database supports the following types of 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

The syntax for creating a row-level trigger is as follows:

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
[{ FOLLOWS | PRECEDES } other_trigger_name]
[WHEN condition]
BEGIN
...
END;

The following table describes the parameters in the syntax.

Limitations on row-level triggers

When you use row-level triggers, note that:

• The SELECT statement in a row-level trigger must use the SELECT ... INTO ... syntax, or be the SELECT statement used to define a cursor.

• You cannot use transaction control statements, such as COMMIT, ROLLBACK, and SAVEPOINT, in a row-level trigger.

• A stored procedure or function called by a row-level trigger cannot contain transaction control statements either.

OLD and NEW pseudorecords

When a row-level trigger is executed, it may reference the values of a column before or after an INSERT, UPDATE, or DELETE operation.

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

Example

Create a simple row-level 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

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 dropped 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 (0.00 sec)

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

Create a statement-level trigger

A statement-level trigger is also a table-level trigger. A statement-level trigger is activated once when a specified statement is executed. In other words, the statement operation is a triggering event. If you create a statement-level trigger for a DML operation on a table, the system automatically executes the statement specified in the trigger when the DML operation is executed.

The syntax for creating a statement-level trigger is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column ...]]}
ON [schema.] table_name
BEGIN
...
END;

Limitations on statement-level triggers

• A statement-level trigger cannot access data before or after an operation by using the OLD or NEW keyword.

• A WHEN clause cannot be specified in a statement-level trigger.

Example

Create a table to record the deleted data of a new table.

1. Create a table named student and a table named old_student, and then use the old_student table to record the deleted data of the student table.

   obclient> CREATE TABLE student(
   id  VARCHAR2(4) NOT NULL,
   name VARCHAR2(15) NOT NULL,
   age NUMBER(2)  NOT NULL,
   sex VARCHAR2(15) NOT NULL
   );
   Query OK, 0 rows affected
   
   obclient> CREATE TABLE old_student AS SELECT * FROM student;
   Query OK, 0 rows affected
   
   obclient> INSERT INTO student VALUES('01','a',6,'Male'),('02','b',7,'Female');
   Query OK, 2 rows affected
   
   obclient> SELECT * FROM student;
   +----+------+-----+--------+
   | ID | NAME | AGE | SEX    |
   +----+------+-----+--------+
   | 01 | a    |   6 | Male   |
   | 02 | b    |   7 | Female |
   +----+------+-----+--------+
   2 rows in set
   
   obclient> SELECT * FROM old_student;
   Empty set
   

2. Create a statement-level TIG_OLD_STU trigger.

   obclient> delimiter /
   
   obclient> CREATE OR REPLACE TRIGGER TIG_OLD_STU
   BEFORE DELETE ON student
   BEGIN
   INSERT INTO old_student SELECT * FROM student;
   END;
   /
   Query OK, 0 rows affected
   
   obclient> delimiter;
   

3. Delete the data in the student table, and view the data of the two tables.

   obclient> DELETE student;
   Query OK, 0 rows affected
   
   obclient> SELECT * FROM student;
   Empty set
   
   obclient> SELECT * FROM old_student;
   +----+------+-----+--------+
   | ID | NAME | AGE | SEX    |
   +----+------+-----+--------+
   | 01 | a    |   6 | Male   |
   | 02 | b    |   7 | Female |
   +----+------+-----+--------+
   2 rows in set