Trigger

Triggers are a feature provided in OceanBase Database. Like stored procedures and functions, triggers are compiled storage units that contain a PL block with declaration, execution, and exception handling sections. Triggers are PL programs written in the PL language.

Triggers are independent objects that automatically run implicitly when a triggering statement is executed. Triggers cannot receive parameters. Here, a triggering statement refers to an INSERT, UPDATE, or DELETE operation on a database table.

Advantages of triggers

Correctly used, triggers can simplify and strengthen application construction and deployment.

You can use triggers to ensure that all client programs execute the underlying business logic. For example, if multiple client applications access the same table, and the table has a trigger that ensures the logic required for inserting data, this business logic does not need to be executed in each client application, because applications cannot bypass triggers and will automatically use the business logic in the triggers.

Use triggers only when necessary to avoid overuse. Overuse of triggers can lead to complex interdependencies, making it difficult to maintain large applications. For example, when a trigger is triggered, the SQL statements it executes can trigger other triggers, leading to cascading triggers and potentially unexpected results.

Types of triggers

OceanBase Database allows you to create the following types of triggers:

• Row triggers

  Row triggers are created on physical tables and are triggered each time the table is affected by a trigger statement. For example, if a statement updates multiple rows of data, the trigger is triggered each time the data is affected. If no rows are affected by the trigger statement, the trigger is not triggered.

• Statement triggers

  Statement triggers are created on physical tables and are automatically triggered each time the trigger statement is executed, regardless of whether the statement affects any rows in the table. For example, if a statement updates 100 rows of data in a table, an UPDATE statement trigger is triggered only once.

• INSTEAD OF triggers

  INSTEAD OF triggers are created on views and are automatically triggered when a trigger statement is executed for the views. INSTEAD OF triggers can be used to modify views that cannot be modified directly through DML statements.

• Compound DML triggers

  Compound DML triggers can be created on tables or views and can be triggered at multiple points. Compound triggers are divided into segments based on each trigger point, with each segment containing an independent executable part and an optional exception handling part.

• System triggers

  System triggers can be divided into two categories based on the triggering events: DDL event triggers and database event triggers. DDL event triggers are those triggered by executing DDL statements, and database event triggers are those triggered by database events. Logon and logoff are database events, so these two types of triggers are database event triggers, and they respectively trigger when a user logs in and before a user logs off.

Trigger points

You can define when a trigger is triggered. This refers to whether the trigger is triggered before or after the trigger statement is executed.

You can specify the following trigger points for statement and row triggers:

• Before the trigger statement is executed

• Before each row of data is affected by the trigger statement

• After each row of data is affected by the trigger statement

• After the trigger statement is executed

For statement and row triggers, BEFORE triggers can enhance security before data is changed and execute business rules. AFTER triggers are suitable for recording operation logs.

The following four trigger points apply to a simple trigger:

• Before the event is executed (a statement-level BEFORE trigger)

• After the event is executed (a statement-level AFTER trigger)

• Before each row of data is affected by the event (a row-level BEFORE trigger)

• After each row of data is affected by the event (a row-level AFTER trigger)

System triggers can be created on schemas (users) and databases. Triggers created on schemas are triggered when a specific user logs in, and triggers created on databases are triggered when all users log in. You can use ON user_name.SCHEMA to specify a trigger to be executed when a specific user logs in. If you do not specify user_name, the trigger is triggered when the user who created it logs in.

The trigger execution sequence is as follows:

• The execution sequence of triggers of the same type cannot be determined. At present, the sequence in which triggers are executed cannot be specified.

• A DML statement may trigger multiple simple triggers. The execution sequence of these triggers is as follows: 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 triggers on tables.

Create a trigger

The basic syntax for creating a trigger is as follows:

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

triggering_statement:
  {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 components:

• Trigger name

  The trigger name must be unique in the same database. For example, the trigger name can be row_trigger_on_employees.

• Triggering statement

  The triggering statement is the SQL statement that invokes the trigger. For example, a user updates a table.

• Trigger restriction

  The trigger restriction is a Boolean expression that you define. The trigger is invoked only when the expression is true. For example, you can define a restriction on the employees table, so that the trigger is invoked only for employees who live in Beijing.

• Trigger action

  The trigger action is a code block that is executed when the triggering statement is executed and the trigger restriction is true. For example, you can insert a data row into the employees table.

Example

Create a trigger on the emp_msg table. The trigger is invoked when an INSERT, UPDATE, or DELETE statement is executed for the table.

When you insert data into the emp_msg table, you also insert a data row into the employees table. When you delete data from the emp_msg table, you delete the data from the employees table where the id column value is the same as that in the emp_msg table. When you update data in the emp_msg table, you update data in the employees table where the id column value is the same as that in the emp_msg table.

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 the trigger is created, execute the following DML statement:

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