Triggers

Triggers are provided in OceanBase Database. Similar to a stored procedure or function, a trigger contains procedural language (PL) blocks for declaration, execution, and exception handling. A trigger is also a PL storage unit.

A trigger is an independent object that is automatically and implicitly activated when a triggering statement is executed. A trigger does not accept parameters. The triggering statement can be an INSERT, UPDATE, or DELETE statement.

Advantages of triggers

Proper use of triggers can simplify and fortify the building and deployment of applications.

You can use triggers to make sure that all client applications execute the underlying business logic. For example, multiple client applications need to access a specific table. If the trigger that is associated with the table executes the business logic for data inserts, the logic does not need to be executed in each client application. This is because no client application can circumvent the trigger and all client applications automatically apply the business logic in the trigger.

We recommend that you use triggers only if your business requires the triggers to avoid excessive use. Overuse of triggers causes complex interdependencies, which may be difficult to maintain in a large application. For example, the SQL statements that are executed when a trigger is activated may activate another trigger. In this case, cascading triggering happens and unexpected results may occur.

Trigger types

OceanBase Database supports the following types of triggers:

• Row-level trigger

  A row-level trigger is created on an entity table and is activated each time the table is affected by a triggering statement. For example, if you use a statement to update multiple rows, the trigger is activated once for each affected row. If the triggering statement does not affect data rows, the trigger is not activated.

• Statement-level trigger

  A statement-level trigger is created on an entity table and is automatically activated each time a triggering statement is executed, regardless of whether the triggering statement affects a row in the table. For example, if you use a statement to update 100 rows in a table, the UPDATE trigger is activated only once.

• INSTEAD OF trigger

  An INSTEAD OF trigger is created on a view and is automatically activated when a triggering statement is executed for the view. You can use an INSTEAD OF trigger to change a view that you cannot directly change by using a DML statement.

• Compound DML trigger

  You can create a compound DML trigger on a table or an editable view. The trigger can fire at multiple timing points. A compound trigger is sectioned based on each timing point. Each timing point section has its own executable part and exception-handling part (optional).

Trigger activation time

You can specify the activation time of a trigger. To be specific, you can specify whether a trigger is activated before or after the triggering statement is executed.

The following list describes the time to activate a row-level trigger or statement-level trigger:

• Before the triggering statement is executed

• Before each row is changed by the triggering statement

• After each row is changed by the triggering statement

• After the triggering statement is executed

For statement-level and row-level triggers, you can use a BEFORE trigger to enhance data security and execute business rules before you change data. You can use an AFTER trigger to record operation logs.

The following list describes the time to activate a simple trigger:

• A statement-level BEFORE trigger is executed before the triggering event executes.

• A statement-level AFTER trigger is executed after the triggering event executes.

• A row-level BEFORE trigger is executed before a specific row is affected.

• A row-level AFTER trigger is executed after a specific row is affected.

The activation order is as follows:

• The execution order for triggers of the same type is random. Currently, you cannot specify the order.

• A DML statement may activate multiple simple triggers. Simple triggers are activated in the following order: statement-level BEFORE trigger -> row-level BEFORE trigger -> row-level AFTER trigger -> statement-level AFTER trigger.

  Notice

  OceanBase Database V2.2.7x and earlier versions support only row-level triggers on tables.

Create a trigger

The basic syntax to create a trigger is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name triggering_statement
  [trigger_restriction]
BEGIN
 triggered_action;
END

triggering_statementL:
  {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

A trigger has the following basic elements:

• Trigger name

  A trigger name must be unique in a database. For example, you can name a trigger as row_trigger_on_employees.

• Triggering statement

  A triggering statement is an SQL statement that activates a trigger. For example, you can use a table update statement as a triggering statement.

• Trigger restriction

  A trigger restriction is defined by a Boolean expression that must be true for a trigger to be activated. For example, you can define a restriction for a trigger on the employee table. In this way, the trigger can be activated only if an employee lives in Beijing.

• Trigger action

  A trigger action refers to a code block that runs in a trigger when a triggering statement is executed and the trigger restriction is true. For example, you can define the insert of a data entry into the employee table as a trigger action.

Example of creating a trigger

Create a trigger on the emp_msg table. The trigger can be activated if an INSERT, UPDATE, or DELETE statement is executed on the table.

To be specific, if you insert a row into the emp_msg table, the row is also inserted into the employees table. If you delete a row from the emp_msg table, the row with the same id in the employees table is also deleted. If you update a row in the emp_msg table, the row with the same id in the employees table is also updated.

CREATE TABLE employees (id INT, name VARCHAR2(20), WORK_YEAR int);

CREATE TABLE emp_msg (id INT PRIMARY KEY, name VARCHAR2(20), address VARCHAR2(100));

CREATE OR REPLACE TRIGGER tri_emp_msg BEFORE INSERT OR UPDATE OR DELETE ON emp_msg
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO employees VALUES (:NEW.id, :NEW.name, 0);
  ELSIF DELETING THEN
    DELETE FROM employees WHERE id = :OLD.id;
  ELSE
    UPDATE employees SET name = :NEW.name WHERE id = :NEW.id;
  END IF;
END;
/

After you create the trigger, execute the following DML statements:

obclient> INSERT INTO emp_msg VALUES (1, 'Curry', 'BeiJing');
Query OK, 1 row affected

obclient> SELECT * FROM emp_msg WHERE id = 1;
+----+-------+---------+
| ID | NAME  | ADDRESS |
+----+-------+---------+
|  1 | Curry | BeiJing |
+----+-------+---------+
1 row in set

obclient> SELECT * FROM  employees WHERE id = 1;
+------+-------+-----------+
| ID   | NAME  | WORK_YEAR |
+------+-------+-----------+
|    1 | Curry |         0 |
+------+-------+-----------+
1 row in set


obclient> UPDATE emp_msg SET name = 'Stephen Curry' WHERE id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT * FROM emp_msg WHERE id = 1;
+----+---------------+---------+
| ID | NAME          | ADDRESS |
+----+---------------+---------+
|  1 | Stephen Curry | BeiJing |
+----+---------------+---------+
1 row in set

obclient> SELECT * FROM  employees WHERE id = 1;
+------+---------------+-----------+
| ID   | NAME          | WORK_YEAR |
+------+---------------+-----------+
|    1 | Stephen Curry |         0 |
+------+---------------+-----------+
1 row in set


obclient> DELETE FROM emp_msg WHERE id = 1;
Query OK, 1 row affected

obclient> SELECT * FROM emp_msg WHERE id = 1;
Empty set

obclient> SELECT * FROM  employees WHERE id = 1;
Empty set