- 论坛徽章:
- 0
|
能否在表的触发器中当一记录变化修改另一记录
You can do:
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.
Example of instead of trigger for this case:
[ Code Start ]
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(ld.value,0) <>; 0 then
update t1 set value = value + nvl(:new.value,0) - nvl(ld.value,0)
where id = :new.id;
update_parents(:new.id, nvl(:new.value,0) - nvl(ld.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;
[ Code End ] |
|