这篇文章用实验讨论Oracle触发器的触发级别问题。
1.创建测试环境:
create table a(id number);
create table b(id number);
begin
for i in 1 .. 100 loop
insert into a values (i);
commit;
end loop;
end;
/
create or replace trigger a_t_b
after insert or update or delete on a
for each row
declare
-- local variables here
begin
insert into b values (1);
end a_t_b;
2.测试每行触发:
SQL> begin
2 update a set id=1 where id in(1,2,3,4,5,6,7,8,9);
3 delete from a where id>90;
4 end;
5 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select * from b;
ID
----------
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
19 rows selected
虽然只有两条SQL语句,但是两条语句操作了19行数据,所以这里产生了19个触发事件。
3.测试语句触发:
SQL> truncate table b;
Table truncated
修改触发器,注释for each row行。
create or replace trigger a_t_b
after insert or update or delete on a
-- for each row
declare
-- local variables here
begin
insert into b values (1);
end a_t_b;
SQL> begin
2 update a set id=2 where id in (11,12,13,14,15,16);
3 delete from a where id>80;
4 end;
5 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select * from b;
ID
----------
1
1
虽然同样修改了多行记录,但只有两条SQL语句,所以只产生了两个触发事件。还需要注意的是触发事件发生在操作行记录和SQL语句级别,而非发生在事务级别,跟事务没关系。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23135684/viewspace-712450/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23135684/viewspace-712450/