| 本来，这是一个没有必要的，但是，对于oracle来说，也算一个技巧，本文讲述触发器如何自己修改自己 在实际应用中，有时候触发器需要修改触发自己的表，这时，我们可以采用如下的方式：
1. Rename your table with another name;
2. Create a view for select * from the table with the original name of the table;
3. Create a instead of trigger on the view, in the view you can access and update yur base table.
如下
建立一个表
create table t1(
id number(6) primary key
,pid number(6)
,value number(15,2)
,f1 varchar2(10)
,f2 varchar2(20)
);
建一个视图
create or replace view t1_v as select * from t1;
建触发器
create or replace trigger bug_t1_v
instead of update on t1_v
for each row
declare
procedure update_parents(i_id in number, i_value in number);
procedure update_parents(i_id in number, i_value in number) is
begin
declare
l_pid t1.pid%type;
begin
select pid into l_pid
from t1
where id = i_id;
if l_pid <> 0 then
update t1 set value = nvl(value,0) + nvl(i_value,0)
where id = l_pid;
update_parents(l_pid, i_value);
end if;
exception
when no_data_found then
null;
end;
end update_parents;
begin
--
-- Update Value Field for current record and Parent records
--
if nvl(:new.value,0) - nvl(:old.value,0) <> 0 then
update t1 set value = value + nvl(:new.value,0) - nvl(:old.value,0)
where id = :new.id;
update_parents(:new.id, nvl(:new.value,0) - nvl(:old.value,0));
end if;
--
-- Update Others Fields
--
update t1 set f1 = :new.f1
,f2 = :new.f2
where id = :new.id;
end;
最后可以测试
--
-- Testing
--
-- With this view: t1_v
--
begin
for i in 1..50 loop
Insert into t1_v values(i, i-1, 0, \"\", \"\");
end loop;
end;
/
delete from t1_v;
begin
for i in 1..50 loop
Insert into t1_v values(i, i-1, 0, \"\", \"\");
end loop;
end;
/
update t1_v set f1 = \"TEST\" where id = 49;
update t1_v set value = value + 5 , f1 = \"AA\", F2 = \"BB\"
where id = 50;
|
