触发器如下:
CREATE OR REPLACE TRIGGER "UPDATE_H004H003" AFTER
UPDATE ON "SM_DEPARTMENT" FOR EACH ROW
declare
flag varchar(2);
sjjg varchar(20);
bm varchar(50);
BEGIN
if (:old.depflag='0')
then
update h003 set h00300=rpad(:new.depbm,20,'0'),h00301=:new.depbm,H00348=:new.pptr,H00303=:new.depname where rtrim(h00300,'0')=:new.depbm;
end if; if (:old.depflag='1')
then
--select depbm into bm from sm_department where depbm=:new.depbm;
--select depflag into flag from sm_department where depbm=bm;
bm:=:new.depbm;
flag:=:new.depflag;
while (flag='1') loop
begin
select pptr into sjjg from sm_department where depbm=bm;
select depbm into bm from sm_department where depbm=sjjg;
select depflag into flag from sm_department where depbm=bm;
end;
end loop;
update h004 set h00400=rpad(:new.depbm,20,'0'),H00402=:new.depbm,H00414=:new.pptr,H00401=bm,H00404=:new.depname where rtrim(h00400,'0')=:new.depbm;
end if;
end; 执行后,结果报错,如下:
ora-04091:表EHR.SM_DEPARTMENT发生了变化,触发器/函数不能读
ORA-06512:在“EHR.UPDATE_H004H003”,LINE 19
ORA-04088:触发器'EHR.UPDATE_H004H003'执行过程中出错 出错的行为:"select pptr into sjjg from sm_department where depbm=bm;"
CREATE OR REPLACE TRIGGER "UPDATE_H004H003" AFTER
UPDATE ON "SM_DEPARTMENT" FOR EACH ROW
declare
flag varchar(2);
sjjg varchar(20);
bm varchar(50);
BEGIN
if (:old.depflag='0')
then
update h003 set h00300=rpad(:new.depbm,20,'0'),h00301=:new.depbm,H00348=:new.pptr,H00303=:new.depname where rtrim(h00300,'0')=:new.depbm;
end if; if (:old.depflag='1')
then
--select depbm into bm from sm_department where depbm=:new.depbm;
--select depflag into flag from sm_department where depbm=bm;
bm:=:new.depbm;
flag:=:new.depflag;
while (flag='1') loop
begin
select pptr into sjjg from sm_department where depbm=bm;
select depbm into bm from sm_department where depbm=sjjg;
select depflag into flag from sm_department where depbm=bm;
end;
end loop;
update h004 set h00400=rpad(:new.depbm,20,'0'),H00402=:new.depbm,H00414=:new.pptr,H00401=bm,H00404=:new.depname where rtrim(h00400,'0')=:new.depbm;
end if;
end; 执行后,结果报错,如下:
ora-04091:表EHR.SM_DEPARTMENT发生了变化,触发器/函数不能读
ORA-06512:在“EHR.UPDATE_H004H003”,LINE 19
ORA-04088:触发器'EHR.UPDATE_H004H003'执行过程中出错 出错的行为:"select pptr into sjjg from sm_department where depbm=bm;"
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货