不是行级触发器就没问题
例:
SQL> select * from t;PA CH
-- --
a b
a c
a e
b t1
b t2
c t3
f t9已选择7行。
SQL> SET SERVEROUTPUT ON;
SQL> CREATE OR REPLACE TRIGGER delt
2 after delete ON t
3 declare
4 com_num NUMBER;
5
6 BEGIN
7 select count(*) into com_num from t where parent='a';
8 DBMS_OUTPUT.PUT_LINE(com_num);
9 END delt;
10 /触发器已创建SQL> delete from t where parent='f';
3已删除 1 行。行级触发:
SQL> SET SERVEROUTPUT ON;
SQL> CREATE OR REPLACE TRIGGER delt
2 after delete ON t
3 for each row
4 declare
5 com_num NUMBER;
6
7 BEGIN
8 select count(*) into com_num from t where parent='a';
9 DBMS_OUTPUT.PUT_LINE(com_num);
10 END delt;
11 /触发器已创建实际:110
SQL> delete from t where parent='c';
delete from t where parent='c'
*
ERROR 位于第1行:
ORA-04091: 表 TEMP.T 发生了变化,触发器/函数不能读
ORA-06512: 在"TEMP.DELT", line 5
ORA-04088: 触发器 'TEMP.DELT' 执行过程中出错
例:
SQL> select * from t;PA CH
-- --
a b
a c
a e
b t1
b t2
c t3
f t9已选择7行。
SQL> SET SERVEROUTPUT ON;
SQL> CREATE OR REPLACE TRIGGER delt
2 after delete ON t
3 declare
4 com_num NUMBER;
5
6 BEGIN
7 select count(*) into com_num from t where parent='a';
8 DBMS_OUTPUT.PUT_LINE(com_num);
9 END delt;
10 /触发器已创建SQL> delete from t where parent='f';
3已删除 1 行。行级触发:
SQL> SET SERVEROUTPUT ON;
SQL> CREATE OR REPLACE TRIGGER delt
2 after delete ON t
3 for each row
4 declare
5 com_num NUMBER;
6
7 BEGIN
8 select count(*) into com_num from t where parent='a';
9 DBMS_OUTPUT.PUT_LINE(com_num);
10 END delt;
11 /触发器已创建实际:110
SQL> delete from t where parent='c';
delete from t where parent='c'
*
ERROR 位于第1行:
ORA-04091: 表 TEMP.T 发生了变化,触发器/函数不能读
ORA-06512: 在"TEMP.DELT", line 5
ORA-04088: 触发器 'TEMP.DELT' 执行过程中出错
上面同志说的没错,
在ORACLE中,触发器中的语句有一定的限制,尤其是对行级触发器
1)不允许读取和修改任何变化表,包括触发表本身
2)不允许读取和修改限制表中的主键(PRIMARY KEY),唯一值列(UNIQUE),外键(FOREIGN)列.但是可以修改和读取其他列.变化表:被DML语句正在修改的表,对触发器即是定义触发器的表
限制表: 与定义触发器的表有参考完整性约束的表由于以上限制,所以在上面同志的第二个触发器中才会出错!