这个问题在采用fk constraint的数据库中比较常见,当对父表进行更新时,我们希望能级联更新子表,oracle并没有提供update cascade的功能,所以这个功能我们自己编码来实现。
通常当使用after each row trigger来实现单条记录的级联更新,但是after each row trigger会在多条记录级联更新时产生错误的结果。
我们来创建2张表
SQL 10G>create table p (p1 number constraint ppk primary key);
Table created.
Table created.
SQL 10G>create table f (f1 number constraint ffk references p deferrable);
Table created.
Table created.
SQL 10G>insert into p values(1);
1 row created.
SQL 10G>insert into p values(2);
1 row created.
SQL 10G>insert into p values(3);
1 row created.
SQL 10G>
SQL 10G>insert into f values(1);
SQL 10G>insert into f values(1);
1 row created.
SQL 10G>insert into f values(2);
1 row created.
SQL 10G>insert into f values(3);
1 row created.
SQL 10G>commit;
Commit complete.
创建trigger
SQL 10G>create or replace trigger pt_after_each after update on p for each row
2 begin
3 update f set f1=:new.p1 where f1=:old.p1;
4 end;
5 /
2 begin
3 update f set f1=:new.p1 where f1=:old.p1;
4 end;
5 /
Trigger created.
更新单条记录
SQL 10G>update p set p1=4 where p1=1;
1 row updated.
SQL 10G>select *from p;
P1
----------
2
3
4
----------
2
3
4
SQL 10G>select * from f;
F1
----------
4
2
3
----------
4
2
3
结果没错,after each row trigger实现了级联更新的功能并给出了正确的结果
我们来更新多条记录
SQL 10G>rollback;
Rollback complete.
SQL 10G>update p set p1=p1+1;
3 rows updated.
SQL 10G>select *from p;
P1
----------
2
3
4
----------
2
3
4
SQL 10G>select * from f;
F1
----------
4
4
4
----------
4
4
4
可以看到f表的记录变成了三4,显然这不是我们想要的结果,单父表这边把1更新成2,子表也相应把1变成2,这时候子表就有2个2了,然后父表把2更新成3,子表更新两个2成3,这时候子表就有三个3了,当父表把3更新成4,子表会把三个3都更新成4,也就是最后我们看到的结果,这显然是不符合需求的。我们除了限制父表的多条记录更新外还有什么办法呢?
我们可以改写trigger,使用before,before row,after trigger联合实现这个功能
首先我们得创建一个包,定义我们需要的数据结构
SQL 10G>create or replace package state_pkg
2 as
3 type myArray is table of f.f1%type ---一个number类型的index table
4 index by binary_integer;
5 type vararray is table of varchar2(100) ---一个varchar类型的index table
6 index by binary_integer;
7 type rowidArray is table of vararray ---一个vararray类型的index table
8 index by binary_integer;
9 todo myArray; ---存储需要更新的值的table
10 rid rowidArray; ---存储需要更新的f表的rowid的table
11 empty myArray; ---空的myarray结构,用来初始化
12 emptyrid rowidArray; ---空的rowidArray结构,用来初始化
13 end;
14 /
Package created.
创建before trigger
SQL 10G>create or replace trigger pt_before before update on p
2 begin
3 state_pkg.todo:=state_pkg.empty;
4 state_pkg.rid:=state_pkg.emptyrid;
5 end;
6 /
2 begin
3 state_pkg.todo:=state_pkg.empty;
4 state_pkg.rid:=state_pkg.emptyrid;
5 end;
6 /
Trigger created.
这个trigger的功能就是当每次更新父表前先把package中的index table值清空
创建before row trigger
SQL 10G>create or replace trigger pt_before_row before update on p for each row
2 declare
3 begin
4 state_pkg.todo(state_pkg.todo.count+1) := :new.p1;
5 select rowid bulk collect into state_pkg.rid(state_pkg.rid.count+1) from f where f1=:old.p1;
6 end;
7 /
2 declare
3 begin
4 state_pkg.todo(state_pkg.todo.count+1) := :new.p1;
5 select rowid bulk collect into state_pkg.rid(state_pkg.rid.count+1) from f where f1=:old.p1;
6 end;
7 /
Trigger created.
这个trigger的功能是保留要更新到子表的值到table,同时查询出子表要更新的记录的rowid并保存在state_pkg.rid结构中,如果父表和子表的记录是1对1的关系,那么state_pkg.rid只需要定义成vararray type而不需要定义成rowidArray type。
最后创建after trigger
SQL 10G>create or replace trigger p_after
2 after update on p
3 declare
4 begin
5 for i in 1 .. state_pkg.todo.count loop
6 for j in state_pkg.rid(i).first..state_pkg.rid(i).last loop
7 update f set f1=state_pkg.todo(i) where rowid=state_pkg.rid(i)(j);
8 end loop;
9 end loop;
10 end;
11 /
2 after update on p
3 declare
4 begin
5 for i in 1 .. state_pkg.todo.count loop
6 for j in state_pkg.rid(i).first..state_pkg.rid(i).last loop
7 update f set f1=state_pkg.todo(i) where rowid=state_pkg.rid(i)(j);
8 end loop;
9 end loop;
10 end;
11 /
这个trigger就是为了实现最终的级联更新,由于after row trigger会带来错误的结果,所以需要使用after table trigger来更新。
离得到正确的结果还有一步
SQL 10G>select * from p;
P1
----------
1
2
3
----------
1
2
3
SQL 10G>select * from f;
F1
----------
1
2
3
----------
1
2
3
SQL 10G>drop trigger pt_after_each;
Trigger dropped.
SQL 10G>update p set p1=p1+1;
update p set p1=p1+1
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.FFK) violated - child record found
update p set p1=p1+1
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.FFK) violated - child record found
由于是after table trigger,所以有fk关联的时候会产生02292的错误,我们需要把fk改成deferred
SQL 10G>alter table f modify constraint ffk initially deferred;
Table altered.
SQL 10G>update p set p1=p1+1;
3 rows updated.
SQL 10G>select * from p;
P1
----------
2
3
4
----------
2
3
4
SQL 10G>select * from f;
F1
----------
2
3
4
----------
2
3
4
SQL 10G>commit;
Commit complete.
SQL 10G>update p set p1=p1-1;
3 rows updated.
SQL 10G>select * from p;
P1
----------
1
2
3
----------
1
2
3
SQL 10G>select * from f;
F1
----------
1
2
3
----------
1
2
3
再来看一下1对多的情况
SQL 10G>insert into f values(1);
1 row created.
SQL 10G>commit;
Commit complete.
SQL 10G>select * from f;
F1
----------
1
1
2
3
----------
1
1
2
3
SQL 10G>update p set p1=p1+1;
3 rows updated.
SQL 10G>select * from p;
P1
----------
2
3
4
----------
2
3
4
SQL 10G>select * from f;
F1
----------
2
2
3
4
----------
2
2
3
4
这样就实现了1对多的级联更新。
不过使用这种trigger方法来实现级联更新效率比较低,特别是更新一大批记录的情况,我们应该在应用程序里面保证完整性,通过deferred fk,使用代码更新才是效率最高的。