大大: 改成 :new.dm:=V_ZL.dm;的方式就可以了,但是我想在该触发器trigger中级联更新另一个表,另一个表的数据为什么没有变化耶? IF (:old.zlh03<>:new.zlh03) or (:old.dh02<>:new.dh02) THEN OPEN C_ZL(:new.zlh03); FETCH C_ZL INTO V_ZL; IF C_ZL%FOUND THEN :new.dm:=V_ZL.dm; :new.dw:=V_ZL.dw; :new.zlh03:=V_ZL.zlh03; :new.dh01:=:new.dh02; --更新分户 当dh02或zlh03变化后,希望更新fh表,但是triger后发现fh并没有变化 update fh set dh02=:new.dh02,zlh03=:New.zlh03 where dh02=:old.dh02 and zlh03=:old.zlh03;
这样的触发器该如何写??折腾死人了主表 ZL : dm dw zlh01 zlh02 zlh03 主键 dm, zlh01 从表 FD : dm dw zlh01 zlh02 zlh03 dh02 主键 zlh03, dh02 外键zlh01CREATE OR REPLACE TRIGGER ZL_UPDATE_TRG BEFORE UPDATE ON ZL FOR EACH ROW BEGIN IF :new.dm<>:old.dm or :new.dw<>:old.dw or :new.zlh01<>:old.zlh01 or :new.zlh02<>:old.zlh02 or :new.zlh03<>:old.zlh03 or :new.zdb001<>:old.zdb001 then --ZL表数据变化,同步更新FD表,但是执行fd的更新触发器出错 UPDATE fd SET dm=:new.dm,dw=:new.dw,zlh01=:new.zlh01,zlh02=:new.zlh02,zlh03=:new.zlh03,zdb001=:new.zdb001,zdb002=:new.zdb002 where dm=:old.dm and zlh01=:old.zlh01; END IF; END; CREATE OR REPLACE TRIGGER FD_Insert_Update_Trg BEFORE INSERT OR UPDATE ON FD For EACH ROW BEGIN IF (:old.zlh03<>:new.zlh03) or (:old.dh02<>:new.dh02) THEN--当FD表的zlh03值变化,则需要同时更新dm,dw,zlh01的值(从对应zlh03的ZL表中获得) --为避免ORA-4091错误,我用了一个包 --用包存储全局变量,使用after update语句级触发器处理 FDData.v_NumEntries:=FDData.v_NumEntries+1; FDData.v_zlh03(FDData.v_NumEntries):=:new.zlh03; FDData.v_dh02(FFDData.v_NumEntries):=:new.dh02; FDData.v_old_zlh03(FDData.v_NumEntries):=:old.zlh03; FDData.v_old_dh02(FDData.v_NumEntries):=:old.dh02; END IF; END;Create Or Replace Trigger FD_After_Update_Trg after update on fd begin --如果zlh01,zlh03或dh02发生变化 FOR v_loop IN 1..FDData.v_NumEntries LOOP --获得zl信息 当使用ZL_UPDATE_TRG触发器更新FD表时,执行到这里又抱错了 这类问题怎么解决??急急急SELECT t.dm,t.dw,t.zlh01,t.zlh02 INTO FDData.v_dm(FDData.v_NumEntries),FDData.v_dw(FDData.v_NumEntries), FDData.v_zlh01(FDData.v_NumEntries),FDData.v_zlh02(FDData.v_NumEntries) FROM ZL t <---- WHERE t.zlh03=FDData.v_zlh03(FDData.v_NumEntries); --更新FD表相关字段值dm dw zlh01 zlh02 zlh03 UPDATE FD SET dm= FDData.v_dm(FFDData.v_NumEntries), dw=FDData.v_dw(FDData.v_NumEntries), zlh01=FDData.v_zlh01(FDData.v_NumEntries), zlh02=FDData.v_zlh02(FDData.v_NumEntries), zlh03=FDData.v_zlh03(FDData.v_NumEntries), dh02=FDData.v_dh02(FDData.v_NumEntries) , dh01=FDData.v_dh02(FFDData.v_NumEntries) WHERE zlh03= FDData.v_zlh03(FDData.v_NumEntries) and dh02= FDData.v_dh02(FDData.v_NumEntries); END LOOP; FDData.v_NumEntries:=0; end FD_After_Update_Trg; 包定义如下: create or replace package FDData is TYPE t_dm IS TABLE OF ZL.Dm%type INDEX BY BINARY_INTEGER; TYPE t_dw IS TABLE OF ZL.Dw%type INDEX BY BINARY_INTEGER;
TYPE t_zlh01 IS TABLE OF GK.Zlh01%type INDEX BY BINARY_INTEGER; TYPE t_zlh02 IS TABLE OF GK.Zlh02%type INDEX BY BINARY_INTEGER; TYPE t_zlh03 IS TABLE OF GK.Zlh03%type INDEX BY BINARY_INTEGER; TYPE t_dh02 IS TABLE OF FD.Dh02%type INDEX BY BINARY_INTEGER;
--更新分户 当dh02或zlh03变化后,希望更新fh表,但是triger后发现fh并没有变化--try:update fh set dh02=:new.dh02,zlh03=:New.zlh03 where rtrim(dh02)=:old.dh02 and rtrim(zlh03)=:old.zlh03;
改成 :new.dm:=V_ZL.dm;的方式就可以了,但是我想在该触发器trigger中级联更新另一个表,另一个表的数据为什么没有变化耶? IF (:old.zlh03<>:new.zlh03) or (:old.dh02<>:new.dh02) THEN
OPEN C_ZL(:new.zlh03);
FETCH C_ZL INTO V_ZL;
IF C_ZL%FOUND THEN
:new.dm:=V_ZL.dm;
:new.dw:=V_ZL.dw;
:new.zlh03:=V_ZL.zlh03;
:new.dh01:=:new.dh02;
--更新分户 当dh02或zlh03变化后,希望更新fh表,但是triger后发现fh并没有变化
update fh set dh02=:new.dh02,zlh03=:New.zlh03 where dh02=:old.dh02 and zlh03=:old.zlh03;
从表 FD : dm dw zlh01 zlh02 zlh03 dh02 主键 zlh03, dh02 外键zlh01CREATE OR REPLACE TRIGGER ZL_UPDATE_TRG BEFORE
UPDATE ON ZL
FOR EACH ROW
BEGIN
IF :new.dm<>:old.dm or :new.dw<>:old.dw or :new.zlh01<>:old.zlh01 or :new.zlh02<>:old.zlh02 or :new.zlh03<>:old.zlh03 or :new.zdb001<>:old.zdb001 then
--ZL表数据变化,同步更新FD表,但是执行fd的更新触发器出错
UPDATE fd SET dm=:new.dm,dw=:new.dw,zlh01=:new.zlh01,zlh02=:new.zlh02,zlh03=:new.zlh03,zdb001=:new.zdb001,zdb002=:new.zdb002 where dm=:old.dm and zlh01=:old.zlh01;
END IF; END;
CREATE OR REPLACE TRIGGER FD_Insert_Update_Trg
BEFORE INSERT OR UPDATE ON FD
For EACH ROW
BEGIN
IF (:old.zlh03<>:new.zlh03) or (:old.dh02<>:new.dh02) THEN--当FD表的zlh03值变化,则需要同时更新dm,dw,zlh01的值(从对应zlh03的ZL表中获得)
--为避免ORA-4091错误,我用了一个包
--用包存储全局变量,使用after update语句级触发器处理
FDData.v_NumEntries:=FDData.v_NumEntries+1;
FDData.v_zlh03(FDData.v_NumEntries):=:new.zlh03;
FDData.v_dh02(FFDData.v_NumEntries):=:new.dh02;
FDData.v_old_zlh03(FDData.v_NumEntries):=:old.zlh03;
FDData.v_old_dh02(FDData.v_NumEntries):=:old.dh02;
END IF;
END;Create Or Replace Trigger FD_After_Update_Trg
after update on fd begin
--如果zlh01,zlh03或dh02发生变化
FOR v_loop IN 1..FDData.v_NumEntries LOOP
--获得zl信息
当使用ZL_UPDATE_TRG触发器更新FD表时,执行到这里又抱错了
这类问题怎么解决??急急急SELECT t.dm,t.dw,t.zlh01,t.zlh02 INTO FDData.v_dm(FDData.v_NumEntries),FDData.v_dw(FDData.v_NumEntries),
FDData.v_zlh01(FDData.v_NumEntries),FDData.v_zlh02(FDData.v_NumEntries)
FROM ZL t <----
WHERE t.zlh03=FDData.v_zlh03(FDData.v_NumEntries);
--更新FD表相关字段值dm dw zlh01 zlh02 zlh03
UPDATE FD SET
dm= FDData.v_dm(FFDData.v_NumEntries),
dw=FDData.v_dw(FDData.v_NumEntries),
zlh01=FDData.v_zlh01(FDData.v_NumEntries),
zlh02=FDData.v_zlh02(FDData.v_NumEntries),
zlh03=FDData.v_zlh03(FDData.v_NumEntries),
dh02=FDData.v_dh02(FDData.v_NumEntries) ,
dh01=FDData.v_dh02(FFDData.v_NumEntries)
WHERE zlh03= FDData.v_zlh03(FDData.v_NumEntries)
and dh02= FDData.v_dh02(FDData.v_NumEntries);
END LOOP;
FDData.v_NumEntries:=0;
end FD_After_Update_Trg;
包定义如下:
create or replace package FDData is
TYPE t_dm IS TABLE OF ZL.Dm%type
INDEX BY BINARY_INTEGER;
TYPE t_dw IS TABLE OF ZL.Dw%type
INDEX BY BINARY_INTEGER;
TYPE t_zlh01 IS TABLE OF GK.Zlh01%type
INDEX BY BINARY_INTEGER;
TYPE t_zlh02 IS TABLE OF GK.Zlh02%type
INDEX BY BINARY_INTEGER;
TYPE t_zlh03 IS TABLE OF GK.Zlh03%type
INDEX BY BINARY_INTEGER;
TYPE t_dh02 IS TABLE OF FD.Dh02%type
INDEX BY BINARY_INTEGER;
/*用于防止发生mutationg table变化表错误*/
--变更后的信息
v_dm t_dm;
v_dw t_dw;
v_zlh01 t_zlh01;
v_zlh02 t_zlh02;
v_zlh03 t_zlh03;
v_dh02 t_dh02;--存储更新前的 值
v_old_zlh01 t_zlh01;
v_old_zlh03 t_zlh03;
v_old_dh02 t_dh02;
--用于标识更新的记录数
v_NumEntries BINARY_INTEGER:=0;end FDData;
ZL_UPDATE_TRG行级触发器更新ZL表时,先更新FD表,
触发FD_Insert_Update_Trg行级触发器去做更新FD表的动作,
FD_Insert_Update_Trg行级触发器在语句级触发器 FD_After_Update_Trg中使用了ZL表的select语句,而此时ZL表是(mutating table)变化表,oracle就抱错了,怎么解决???已经用了一个Oracle包,但是不行耶