trigger如何实现级联更新

这个问题在采用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.

 

SQL 10G>create table f (f1 number constraint ffk references p deferrable);
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);

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  /

Trigger created.

 

 

更新单条记录

 

SQL 10G>update p set p1=4 where p1=1;

1 row updated.

SQL 10G>select *from p;

        P1
----------
         2
         3
         4

SQL 10G>select * from f;

        F1
----------
         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

SQL 10G>select * from f;

        F1
----------
         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  /

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  /

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  /

 

这个trigger就是为了实现最终的级联更新，由于after row trigger会带来错误的结果，所以需要使用after table trigger来更新。

 

 

离得到正确的结果还有一步

 

SQL 10G>select * from p;   

        P1
----------
         1
         2
         3

 

SQL 10G>select * from f;

        F1
----------
         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

 

由于是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

SQL 10G>select * from f;

        F1
----------
         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

 

SQL 10G>select * from f;

        F1
----------
         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

 

 

SQL 10G>update p set p1=p1+1;

3 rows updated.

 

SQL 10G>select * from p;

        P1
----------
         2
         3
         4

 

SQL 10G>select * from f;

        F1
----------
         2
         2
         3
         4

 

 

这样就实现了1对多的级联更新。

 

 

不过使用这种trigger方法来实现级联更新效率比较低，特别是更新一大批记录的情况，我们应该在应用程序里面保证完整性，通过deferred fk,使用代码更新才是效率最高的。