Create a trigger

You can create a DML trigger on a table or a view. A DML 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 fires

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 fires before or after the triggering event. For example, whether the trigger fires 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 and 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
   

Create an INSTEAD OF trigger

You can create an INSTEAD OF trigger on an uneditable view to update the view. An INSTEAD OF trigger can determine the expected operations and execute appropriate DML operations on the base table.

An INSTEAD OF trigger can only be a row-level trigger. An INSTEAD OF DML trigger can read but not modify the OLD and NEW values.

Syntax for creating an INSTEAD OF trigger:

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE}
ON [schema.] view_name
[FOR EACH ROW]
BEGIN
...
END;

Notes:

• OR REPLACE: If the name of the trigger to be created already exists, a new trigger is created based on the specified definition.

• view_name: the name of the view. An INSTEAD OF trigger can be created only on a view.

• You must have the CREATE TRIGGER system privilege to create triggers.

Example: Use an INSTEAD OF trigger to update a view.

obclient> CREATE TABLE customers(
    customer_id             NUMBER(20) NOT NULL ,
    cust_last_name          VARCHAR(25) DEFAULT NULL,
    cust_first_name         VARCHAR(25) DEFAULT NULL,
    cust_address            VARCHAR(40) DEFAULT NULL,
    cust_nationkey          NUMBER(20) DEFAULT NULL,
    cust_phone              CHAR(15) DEFAULT NULL,
    cust_acctbal            DECIMAL(10,2) DEFAULT NULL,
    cust_mktsegment         CHAR(10) DEFAULT NULL,
    cust_comment            VARCHAR(117) DEFAULT NULL,
    PRIMARY KEY(customer_id));
Query OK, 1 row affected

obclient> CREATE TABLE orders(
    order_id                 NUMBER(20) NOT NULL ,
    customer_id              NUMBER(20) NOT NULL ,
    order_status             CHAR(1) DEFAULT NULL,
    total_price              DECIMAL(10,2) DEFAULT NULL,
    order_date               DATE NOT NULL,
    order_priority           CHAR(15) DEFAULT NULL,
    order_clerk              CHAR(15) DEFAULT NULL,
    order_shippriority       NUMBER(20) DEFAULT NULL,
    order_comment            VARCHAR(79) DEFAULT NULL,
    PRIMARY KEY(order_id,order_date,customer_id));
Query OK, 0 rows affected


obclient> CREATE OR REPLACE VIEW order_list AS
     SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
          o.order_id, o.order_date, o.order_status
     FROM customers c, orders o
     WHERE c.customer_id = o.customer_id;
Query OK, 0 rows affected

obclient> delimiter /

obclient> CREATE OR REPLACE TRIGGER order_list_insert
     INSTEAD OF INSERT ON order_list
     DECLARE
       duplicate_info EXCEPTION;
       PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
     BEGIN
       INSERT INTO customers
         (customer_id, cust_last_name, cust_first_name)
     VALUES (
       :new.customer_id,
       :new.cust_last_name,
       :new.cust_first_name);
     INSERT INTO orders (order_id, order_date, customer_id)
     VALUES (
       :new.order_id,
       :new.order_date,
       :new.customer_id);
     EXCEPTION
       WHEN duplicate_info THEN
         RAISE_APPLICATION_ERROR (
           -20107,
           'Duplicate customer or order ID');
     END order_list_insert;
    /
Query OK, 0 rows affected

obclient> delimiter ;

/* Query and display that the row to be inserted does not exist.*/
obclient> SELECT COUNT(*) FROM order_info WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set

/* Insert the row into the view.*/
obclient> INSERT INTO order_info VALUES (111, 'Smith', 'William', 3500,
                                         '23-MAR-2001', 0);
Query OK, 1 row affected

/* Query and display that the row has been inserted into the view.*/
obclient> SELECT COUNT(*) FROM order_info WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set

/* Query and display that the row has been inserted into the customers table.*/
obclient> SELECT COUNT(*) FROM customers WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set

/* Query and display that the row has been inserted into the orders table.*/
obclient> SELECT COUNT(*) FROM orders WHERE customer_id = 111;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set

Create a composite trigger

Overview

A composite trigger created on a table or editable view can fire at multiple points in time. A composite trigger is segmented based on each point in time. Each time segment has its own executable part and exception-handling part (optional). All the parts can access a universal procedural language (PL) state. The universal state is created when the trigger statement starts being executed and destroyed after the trigger statement is executed. It is not affected even if the trigger statement causes errors.

Limits on composite triggers

Composite DML triggers are subject to the following limits:

• OLD, NEW, and PARENT cannot appear in the declarative part, BEFORE STATEMENT part, or AFTER STATEMENT part.

• Only the BEFORE EACH ROW part can change the value of NEW.

• One time segment cannot handle the exceptions caused in another time segment.

• If a time segment contains the GOTO statement, the destination of the GOTO statement must be in the same time segment.

Syntax for creating a composite trigger

In the declarative part (optional) of a composite trigger, you can declare the variables and subprograms that can be used by all time segments of the composite trigger. When the composite trigger fires, the declarative part runs before any time segment. The variables and subprograms exist in the time segments to which the trigger statement applies.

A composite trigger created on an uneditable view is not a real composite trigger because it has only one time segment. Syntax for creating a simple composite trigger on an uneditable view:

CREATE TRIGGER FOR dml_event_clause ON view_name
COMPOUND TRIGGER
INSTEAD OF EACH ROW IS BEGIN
  sql_statement;
END INSTEAD OF EACH ROW;

A composite trigger created on a table or editable view contains at least one time segment. If a trigger contains multiple time segments, they can be sorted in any order but the points in time cannot be duplicated. If a time segment does not exist, no operation will be performed at this point in time.

The following table describes the time segments contained in a composite trigger.

A composite trigger has no initialization part. However, BEFORE STATEMENT can perform any initialization as needed because it runs prior to other time segments.

If a composite trigger has neither the BEFORE STATEMENT part nor the AFTER STATEMENT part and its trigger statement does not affect any rows, this composite trigger will never fire.

Example: Use a composite trigger to record the changes in the child table of a table.

  CREATE TABLE emp_salaries (
      emp_id NUMBER NOT NULL,
      change_date DATE   NOT NULL,
      salary NUMBER(8,2) NOT NULL,
      FOREIGN KEY (emp_id)
      REFERENCES employees (employee_id) ON DELETE CASCADE);

  obclient> delimiter /

  obclient> CREATE OR REPLACE TRIGGER maintain_emp_salaries
  FOR UPDATE OF salary ON employees
    COMPOUND TRIGGER

  threshold CONSTANT SIMPLE_INTEGER := 7;

  TYPE salaries_t IS TABLE OF emp_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
  sal  salaries_t;
  idx  SIMPLE_INTEGER := 0;

  PROCEDURE flush_proc IS
    n CONSTANT SIMPLE_INTEGER := sal.count();
  BEGIN
    FORALL j IN 1..n
      INSERT INTO emp_salaries VALUES sal(j);
    sal.delete();
    idx := 0;
    DBMS_OUTPUT.PUT_LINE('Refresh' || n || 'Row');
  END flush_proc;

  -- AFTER EACH ROW Time segment:

  AFTER EACH ROW IS
  BEGIN
    idx := idx + 1;
    sal(idx).emp_id := :NEW.employee_id;
    sal(idx).change_date := SYSTIMESTAMP;
    sal(idx).salary := :NEW.salary;
    IF idx >= threshold THEN
      flush_proc();
    END IF;
  END AFTER EACH ROW;

  -- AFTER STATEMENT Time segment:

  AFTER STATEMENT IS
  BEGIN
    flush_proc();
  END AFTER STATEMENT;
END maintain_emp_salaries;
/
Query OK, 0 rows affected

obclient> SET SERVEROUTPUT ON;
Query OK, 0 rows affected


/* Increase the salary of each employee in department 50 by 8%.*/
obclient> UPDATE employees
       SET salary = salary * 0.8
       WHERE department_id = 50
      /
Query OK, 10 rows affected
Rows matched: 10  Changed: 10  Warnings: 0

Refresh 7 rows
Flushed 7 rows
Refresh 3 rows
Flushed 3 rows


/* Wait for 3 seconds.*/
obclient> BEGIN
      DBMS_LOCK.SLEEP(3);
      END
     /
Query OK, 1 row affected(3.01 sec)


/* Increase the salary of each employee in department 50 by 1.1%.*/
obclient> UPDATE employees
       SET salary = salary * 1.1
       WHERE department_id = 50
     /
Query OK, 10 rows affected
Rows matched: 10  Changed: 10  Warnings: 0

Refresh 7 rows
Flushed 7 rows
Refresh 3 rows
Flushed 3 rows


/* View the changes of the employee table in the emp_salaries table.*/
obclient> SELECT emp_id, count(*) num
       FROM emp_salaries
       GROUP BY emp_id
     /
+--------+------+
| EMP_ID | NUM  |
+--------+------+
|    120 |    1 |
|    121 |    1 |
|    122 |    1 |
|    123 |    1 |
|    124 |    1 |
|    125 |    1 |
|    126 |    1 |
|    127 |    1 |
|    128 |    1 |
|    129 |    1 |
+--------+------+
10 rows in set