oracle触发器

oracle行级触发器不能能进行select操作，而表级触发器没有new和old。为了在变异表改动时，对变异表进行操作，使用两个触发器。并创建一个包，存储一次触发时所有行触发器的new 和 old 值，随后表触发器使用这些值。

前提：优先级： 行触发器  > 表触发器

 

代码：

/*存储包*/

create or replace package Bsoid2_package is
Type Varchar_Array is varray(100) of varchar2(50);
bsoid_array Varchar_Array := Varchar_Array() ;
num0 number(3) ;
end Bsoid2_package;

create or replace package body Bsoid2_package is
begin
for i in 1 .. 100 loop
Bsoid2_package.bsoid_array.extend;
Bsoid2_package.bsoid_array(i) := 'NULL';
end loop;
num0 := 1;
end Bsoid2_package;

 

/*行触发器 */

create trigger Bsoid1_trigger
after insert or update or delete
on qmfawtechnics
for each row
declare
begin
if inserting then
Bsoid2_package.bsoid_array(Bsoid2_package.num0) := :new.masterbsoid;
-- dbms_output.put_line(Bsoid2_package.bsoid_array(Bsoid2_package.num0));
Bsoid2_package.num0 := Bsoid2_package.num0 + 1;
-- dbms_output.put_line(Bsoid2_package.num0);
end if;
if deleting then
Bsoid2_package.bsoid_array(Bsoid2_package.num0) := :old.masterbsoid;
-- dbms_output.put_line(Bsoid2_package.bsoid_array(Bsoid2_package.num0));
Bsoid2_package.num0 := Bsoid2_package.num0 + 1;
-- dbms_output.put_line(Bsoid2_package.num0);
end if;
if updating then
Bsoid2_package.bsoid_array(Bsoid2_package.num0) := :old.masterbsoid;
-- dbms_output.put_line(Bsoid2_package.bsoid_array(Bsoid2_package.num0));
Bsoid2_package.num0 := Bsoid2_package.num0 + 1;
-- dbms_output.put_line(Bsoid2_package.num0);
Bsoid2_package.bsoid_array(Bsoid2_package.num0) := :new.masterbsoid;
-- dbms_output.put_line(Bsoid2_package.bsoid_array(Bsoid2_package.num0));
Bsoid2_package.num0 := Bsoid2_package.num0 + 1;
-- dbms_output.put_line(Bsoid2_package.num0);
end if;
end;

 

/* 表触发器*/

create trigger Bsoid2_trigger
after insert or update or delete
on qmfawtechnics
begin
if inserting then
for i in 1 .. Bsoid2_package.num0 - 1 loop
update qmfawtechnicsmaxversionvalue
set maxversionvalue = getmaxversionvalue('qmfawtechnics',Bsoid2_package.bsoid_array(i))
where masterbsoid = Bsoid2_package.bsoid_array(i);
-- dbms_output.put_line(Bsoid2_package.bsoid_array(i));
end loop;
end if;
if deleting then
for i in 1 .. Bsoid2_package.num0 - 1 loop
update qmfawtechnicsmaxversionvalue
set maxversionvalue = getmaxversionvalue('qmfawtechnics',Bsoid2_package.bsoid_array(i))
where masterbsoid = Bsoid2_package.bsoid_array(i);
-- dbms_output.put_line(Bsoid2_package.bsoid_array(i));
end loop;
end if;
if updating then
for i in 1 .. Bsoid2_package.num0 - 1 loop
update qmfawtechnicsmaxversionvalue
set maxversionvalue = getmaxversionvalue('qmfawtechnics',Bsoid2_package.bsoid_array(i))
where masterbsoid = Bsoid2_package.bsoid_array(i);
-- dbms_output.put_line(Bsoid2_package.bsoid_array(i));
end loop;
end if;
Bsoid2_package.num0 := 1;
end;