Only autonomous triggers can execute Tool Command Language (TCL) and Data Definition Language (DDL) statements. To execute a DDL statement, a trigger must use a dynamic SQL statement.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
One purpose of triggers is to transparently record events, for example, record all insert operations as well as rollback operations in a table.
In the following example, each time a row is inserted into the emp table, the trigger inserts the same row into the log table. An autonomous trigger can commit changes to the log table regardless of whether the changes are committed to the primary 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