Create a 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.

Types of DML row-level triggers

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 use a CREATE TRIGGER statement to create a trigger.

To create a trigger, you must have the following privileges:

• Privileges on the table associated with the trigger, such as SELECT, INSERT, UPDATE, and DELETE privileges

• CREATE TRIGGER privilege

• Privileges on statements to be executed after the trigger is activated

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;

Note

• trigger_name: the name of the trigger. The name must be unique.

• tbl_name: the name of the table for which the trigger is created.

• BEFORE or AFTER: specifies whether the trigger is activated before or after the triggering event. For example, whether the trigger is activated before or after each row is inserted into the associated table.

• INSERT, UPDATE, or DELETE: the type of operation for which the trigger fires.

• FOR EACH ROW: the trigger entity. This statement is executed when the trigger fires. It is executed for each row affected by the trigger event.

OceanBase Database also supports NEW.columnName and OLD.columnName.

• For an INSERT trigger, NEW.columnName specifies new data that is inserted in a BEFORE scenario or was inserted in an AFTER scenario.

  columnName indicates a column name in the corresponding data table.

• For an UPDATE trigger, OLD.columnName specifies existing data that is updated. NEW.columnName specifies new data after the update.

• For a DELETE trigger, OLD.columnName specifies existing data that is deleted.

• Values in OLD.columnName are read-only, whereas values in NEW.columnName can be specified by using SET statements.

Parameters

Limits on triggers

Note the following limits 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.

Examples

When data is dropped from the regions table, use triggers to move records with a region_id value greater than 3 to the reg_his table.

1. Create a table named regions.

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

2. Insert data into the regions table.

   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
   

3. Create a table named reg_his, which has the same structure as the regions table.

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

4. Create a trigger named del_new_region.

   obclient> CREATE 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
   

5. View the regions table.

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

6. View the reg_his table.

   obclient> SELECT * FROM reg_his;
   Empty set
   

7. Delete the data records whose region_id is greater than 2 from the regions table.

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

8. View the regions table again.

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

9. View the reg_his table again.

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