To run TCL or DDL statements, a trigger must be autonomous. To run DDL statements, a trigger must use dynamic SQL.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
One use of triggers is to transparently record events. For example, you can record all insert operations in a table, including those that are rolled back.
In the following example, a trigger inserts the same row into a log table every time a row is inserted into the emp table. Because the trigger is autonomous, it can commit the change to the log table regardless of whether the change is committed to the main table.
obclient> CREATE TABLE emp(
empno NUMBER(4,0),
empname VARCHAR(10),
job VARCHAR(10),
deptno NUMBER(2,0),
salary NUMERIC
);
Query OK, 0 rows affected
obclient> INSERT INTO emp VALUES (200,'Jennifer','AD_ASST',1,15000);
obclient> INSERT INTO emp VALUES (202,'Pat','MK_REP',2,12000);
obclient> INSERT INTO emp VALUES (119,'Karen','PU_CLERK', 4,10000);
obclient> INSERT INTO emp VALUES (118,'Guy','PU_CLERK', 4,10000);
obclient> INSERT INTO emp VALUES (201,'Michael','MK_MAN',3,9000);
obclient> CREATE TABLE log_sal (
log_id NUMBER(8),
upsal_date DATE,
new_sal NUMBER(10,2),
old_sal NUMBER(10,2)
);
Query OK, 0 rows affected
obclient> CREATE OR REPLACE TRIGGER emp_sal
BEFORE UPDATE OF salary ON emp FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log_sal (
log_id,
upsal_date,
new_sal,
old_sal
)
VALUES (
:old.empno,
SYSDATE,
:new.salary,
:old.salary
);
COMMIT;
END;
/
Query OK, 0 rows affected
obclient> UPDATE emp SET salary = salary * 1.08
WHERE empno = 119;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient> COMMIT;
Query OK, 0 rows affected
obclient> UPDATE emp
SET salary = salary * 1.08
WHERE empno = 200;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient> ROLLBACK;
Query OK, 0 rows affected
-- Show that both committed and rolled-back updates
-- add rows to log table
obclient> SELECT * FROM log_sal
WHERE log_id = 119 OR log_id = 200;
+--------+------------+---------+---------+
| LOG_ID | UPSAL_DATE | NEW_SAL | OLD_SAL |
+--------+------------+---------+---------+
| 119 | 08-MAY-21 | 10800 | 10000 |
| 200 | 08-MAY-21 | 16200 | 15000 |
+--------+------------+---------+---------+
2 rows in set