You can create a DML trigger on a table or view. The trigger is activated when a DML statement such as DELETE, INSERT, or UPDATE is executed.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
OceanBase Database supports creating DML row triggers. The following types of triggers are supported:
BEFORE INSERT FOR EACH ROWAFTER INSERT FOR EACH ROWBEFORE UPDATE FOR EACH ROWAFTER UPDATE FOR EACH ROWBEFORE DELETE FOR EACH ROWAFTER DELETE FOR EACH ROW
The names of triggers are independent of the names of stored procedures and packages. Therefore, the names of triggers can be the same as those of tables or stored procedures.
Syntax
The syntax for creating a DML 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]
[WHEN condition]
[FOLLOWS | PRECEDES] other_trigger_name
trigger_body;
The following table describes the parameters.
Parameter |
Description |
Notes |
|---|---|---|
| BEFORE and AFTER | specifies the trigger timing as either before or after the trigger event. | A before trigger is activated before the trigger event is executed. An after trigger is activated after the trigger event is executed. |
| FOR EACH ROW | specifies that the trigger is a row trigger. | A row trigger is activated for each data row affected by a DML statement. |
| REFERENCING | specifies the correlation names. In the PL block of a row trigger and the WHEN clause, you can use the correlation names to reference the current new and old column values. The default correlation names are OLD and NEW. |
When you use a correlation name in the PL block of a trigger, you must add a colon (:) before the name. However, you cannot add a colon before a correlation name in the WHEN clause. |
| FOLLOWS and PRECEDES | specifies the trigger order of triggers that are activated at the same time. The specified triggers must exist and have been successfully compiled, but they do not need to be enabled. |
|
| WHEN | specifies the trigger constraint. | The condition must be a logical expression that contains correlation names but does not contain query statements or PL functions. The WHEN clause can be used only for BEFORE and AFTER row triggers. |
Notice
When a base table is modified by using the INSERT, UPDATE, or DELETE statement, the trigger is automatically activated based on the changes made to the base table. Therefore, the trigger is independent of the application. You can use triggers to ensure data consistency and integrity.
Limitations
The following limitations apply to triggers:
The
SELECTstatement in a trigger can be only of theSELECT ... INTO ...type or theSELECTstatement used to define a cursor.You cannot use the transaction control statements
COMMIT,ROLLBACK, andSAVEPOINTin a trigger.The stored procedures or functions called by a trigger cannot use the transaction control statements.
OLD and NEW pseudorecords
When a trigger is activated, you can use the :OLD and :NEW modifiers to reference the column values of the record that is being inserted (INSERT), updated (UPDATE), or deleted (DELETE). The syntax is as follows:
The
:NEWmodifier indicates the column values after the operation is completed.The
:OLDmodifier indicates the column values before the operation is completed.
The following table describes the validity of the :OLD and :NEW modifiers.
Modifier |
INSERT |
UPDATE |
DELETE |
|---|---|---|---|
| OLD | NULL | Valid | Valid |
| NEW | Valid | Valid | NULL |
Example 1: Create a row 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
Records: 4 Duplicates: 0 Warnings: 0
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 this example, when you delete data from the regions table, the records whose region_id values are 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
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
Example 2: Create a statement trigger.
obclient> create table message (id int, mes varchar2(100));
Query OK, 0 rows affected (0.078 sec)
obclient> create table stmt_t (id int, name varchar2(10), grade int);
Query OK, 0 rows affected (0.064 sec)
obclient> create or replace trigger stmt_tri before insert or delete or update of name, grade
on stmt_t
begin
case
when inserting then
insert into message values (1, 'before inserting...');
when deleting then
insert into message values (2, 'before deleting...');
when updating ('name') then
insert into message values (3, 'before updating name ...');
when updating ('grade') then
insert into message values (4, 'before updating grade...');
end case;
end;
/
Query OK, 0 rows affected (0.151 sec)
In this example, the trigger records the corresponding message to the message table before you insert, delete, or update data in the stmt_t table.