oracle 触发器实现多表级联修改

uqhf0b9jw9

应用场景：
table name A
field name：
             yesterday date(昨日日期)
             today date(今日日期)
             yesterdaynum number(昨日数量=totle)
             todaynum number(今日数量)
             totle number（今日总量=yesterdaynum+todaynum）
可见表A昨日数据需要与今日数据相关联，若用户修改了三天前的一个totle字段值，理论上来说应该在这三天发生的数据都要做相应修改。
实现方案：
在table A上做 TRIGGER，每当有update操作时，执行存储过程修改后面相关数据。

CREATE OR REPLACE TRIGGER tr_hyzcupdate
after UPDATE ON A  
FOR  EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SNWZ.TAL_TEST.p_testEmptyCursor(:old.today,:new.totle);
end tr_hyzcupdate;



create or replace package TAL_TEST is
TYPE myCursorType IS REF CURSOR;
PROCEDURE p_testEmptyCursor(updatedate in date,totle in number);
end TAL_TEST;



create or replace package body TAL_TEST is
PROCEDURE p_testEmptyCursor(updatedate in date,totle in number)
is
pragma autonomous_transaction;
maxdate date;
testdate date;
rowRecord A%ROWTYPE;
myCursorFirst myCursorType ;
execSql varchar2(2000);
updateRowRecord A%ROWTYPE;
updateMyCursorFirst myCursorType ;
--disableTrigger varchar2(2000);
--enableTrigger varchar2(2000);
begin
--查询出表中最后一条记录
select max(today) into maxdate from A;
testdate:=updatedate;
--disableTrigger:='alter trigger  tr_hyzcupdate disable';
--enableTrigger:='alter trigger  tr_hyzcupdate enable';
-- execute immediate disableTrigger;
--循环需要修改的记录
while testdate !=maxdate+1 loop
OPEN myCursorFirst FOR
select *  from A t where t.today=testdate;
FETCH myCursorFirst INTO rowRecord;
IF myCursorFirst%notfound THEN
exit;
ELSE
OPEN updateMyCursorFirst FOR
select *  from A t where t.today=(testdate+1);
dbms_output.put_line( testdate+1);
FETCH updateMyCursorFirst INTO updateRowRecord;
IF updateMyCursorFirst%found THEN
-- else
if testdate=updatedate then
dbms_output.put_line('the first update');
execSql:= 'update A t set t.yesterdaynum='||totle||',t.totle='||(totle+updateRowRecord.todaynum) ||' where to_char(t.today,''YYYY-mm-dd'')='''||to_char(updateRowRecord.Today,'YYYY-MM-dd''')||'';
else
execSql:= 'update A t set t.yesterdaynum='||rowRecord.totle||',t.totle='||(rowRecord.totle+updateRowRecord.todaynum) ||' where to_char(t.today,''YYYY-mm-dd'')='''||to_char(updateRowRecord.Today,'YYYY-MM-dd''')||'';
end if;
dbms_output.put_line(execSql);
execute immediate execSql;
commit;
END IF;
end if;
testdate:=testdate+1;
end loop;
--  execute immediate enableTrigger;
end;
end TAL_TEST;


在此遇到几个问题有的解决有的没解决：
问题一：关于变异表问题，因为涉及到修改时候触发动作，而后又操作本表，所以会出现deadlock问题，解决方法--通过设置oracle自治事务
问题二：例如today是2015-03-13 其实上面的代码做了两次update a where today=2015-03-13，为什么这样做呢？因为触发update时候，在执行存储过程期间，被触发的那条记录是没有提交的，所以我们修改后面的记录虽然有commit ，但是数据是不会发生变化的，解决方法，在存储过程中单独执行update a where today=2015-03-13；
问题三：因为触发器是update，而存储过程也是循环每条数据执行update，所以每次update都会调用触发器，所以如果修改n条记录，其实会触发n次触发器，就是这个问题不知如何解决，试过的方案有：在执行存储过程期间将trigger set disable，不成功。