在行级触发器中,不能查询自身表
YANG@yangdb-rac3> create or replace trigger t_1
2 after insert or update on t1
3 for each row
4 declare
5 a number;
6 begin
7 select count(*) into a from t1;
8 update t_count set count =a;
9 end t_1;
10 /
Trigger created.
YANG@yangdb-rac3> insert into t1 values (1,'yangql');
insert into t1 values (1,'yangql')
*
ERROR at line 1:
ORA-04091: table YANG.T1 is mutating, trigger/function may not see it
ORA-06512: at "YANG.T_1", line 4
ORA-04088: error during execution of trigger 'YANG.T_1'
在触发器中操作触发此触发器的表,用PRAGMA AUTONOMOUS_TRANSACTION选项,将操作设置为自治事务.
DECLARE整个块都是属于父事务的,自治事务从离PRAGMA后的第一个BEGIN开始,只要此BEGIN块仍在作用域,则都属于自治事务。例如在DECLARE模块中声明一个写数据库的函数,则此函数虽然在自治事务所在存储过程执行,但其属于父事务;而自治事务中调用的任何函数和存储过程、激发的任何触发器等均为此自治事务的一部分,保持与父事务的隔离。
YANG@yangdb-rac3> create or replace trigger t_1
2 after insert or update on t1
3 for each row
4 declare
5 a number;
6 pragma autonomous_transaction;
7 begin
8 select count(*) into a from t1;
9 update t_count set count =a;
10 commit;
11 end t_1;
11 /
Trigger created.
YANG@yangdb-rac3> insert into t1 values (1,'yangql');
1 row created.
YANG@yangdb-rac3>