触发器编译正确:
CREATE OR REPLACE TRIGGER "GACB"."MONITOR_INS_UPD_DEL" AFTER
INSERT OR
DELETE OR
UPDATE OF "DISPOSEMAN", "EXUBERANCE_CARD", "SHIPNAME"
ON "GACB"."MONITOR"
FOR EACH ROW begin
if :new.disposeman='1' then
update ship_info
set disposeman='1',
rat_forecast=(select rat from monitor where shipname='hondy' and regtime=(select max(regtime) from monitor where shipname='hondy' and disposeman='1'))
where shipname=:new.shipname;
elsif :new.disposeman='0' then
update ship_info
set rat_forecast='0',
sani_forecast='0',
disposeman='0',
exuberance=:new.exuberance_card
where shipname=:new.shipname;
end if;
END;
提示出错:
表 GACB.MONITOR 发生了变化,触发器/函数不能读 ORA-06512: 在"GACB.MONITOR_INS_UPD_DEL", line 3 ORA-04088: 触发器 'GACB.MONITOR_INS_UPD_DEL' 执行过程中出错
CREATE OR REPLACE TRIGGER "GACB"."MONITOR_INS_UPD_DEL" AFTER
INSERT OR
DELETE OR
UPDATE OF "DISPOSEMAN", "EXUBERANCE_CARD", "SHIPNAME"
ON "GACB"."MONITOR"
FOR EACH ROW begin
if :new.disposeman='1' then
update ship_info
set disposeman='1',
rat_forecast=(select rat from monitor where shipname='hondy' and regtime=(select max(regtime) from monitor where shipname='hondy' and disposeman='1'))
where shipname=:new.shipname;
elsif :new.disposeman='0' then
update ship_info
set rat_forecast='0',
sani_forecast='0',
disposeman='0',
exuberance=:new.exuberance_card
where shipname=:new.shipname;
end if;
END;
提示出错:
表 GACB.MONITOR 发生了变化,触发器/函数不能读 ORA-06512: 在"GACB.MONITOR_INS_UPD_DEL", line 3 ORA-04088: 触发器 'GACB.MONITOR_INS_UPD_DEL' 执行过程中出错
将DELETE OR删除,或在if :new.disposeman='1' then 前加上一个判断
if updating or inserting then
if :new.disposeman='1' then
.....
else if ...
.....
end if;
end if ;
把AFTER 改为before
CREATE OR REPLACE TRIGGER "GACB"."MONITOR_INS_UPD_DEL"
AFTER INSERT OR DELETE OR UPDATE OF "DISPOSEMAN", "EXUBERANCE_CARD", "SHIPNAME"
ON "GACB"."MONITOR"
FOR EACH ROW
begin
if updating or inserting then
if :new.disposeman='1' then
update ship_info
set (disposeman,rat_forecast)=(select '1',rat from monitor where shipname='hondy'),regtime=(select max(regtime) from monitor where shipname='hondy' and disposeman='1') --看这里是否出错
where shipname=:new.shipname;
elsif :new.disposeman='0' then
update ship_info
set rat_forecast='0',
sani_forecast='0',
disposeman='0',
exuberance=:new.exuberance_card
where shipname=:new.shipname;
end if;
END;
/
也就是,记录修改后进程的操作。看下我做的例子。参考下:
DROP TRIGGER cw_gdzcb_trigger_update;
CREATE TRIGGER cw_gdzcb_trigger_update
BEFORE UPDATE OF SBH,ZTH,GDZCBH ON CW_GDZCB FOR EACH ROW
BEGIN
if :new.gdzcbh <> :old.gdzcbh then
begin
UPDATE CW_PZMXB SET GDZCBH = :new.gdzcbh
WHERE SBH = :old.sbh AND ZTH = :old.zth AND GDZCBH = :old.gdzcbh ;
exception
when others then
raise_application_error( -20021, '更改固定资产明细帐不成功' );
end;
begin
UPDATE CW_GDZCMXB_JZQ SET GDZCBH = :new.gdzcbh
WHERE SBH = :old.sbh AND ZTH = :old.zth AND GDZCBH = :old.gdzcbh ;
exception
when others then
raise_application_error( -20021, '更改建帐前固定资产明细表不成功' );
end;end if;END;
/
rat_forecast=(select rat from monitor where shipname='hondy' and regtime=(select max(regtime) from monitor where shipname='hondy' and disposeman='1'))
事实上下句出错:表 GACB.MONITOR 发生了变化,触发器/函数不能读 ORA-06512: 在"GACB.MONITOR_INS_UPD", line 3 ORA-04088: 触发器 'GACB.MONITOR_INS_UPD' 执行过程中出错 所以必须(select rat from monitor where shipname='hondy' and regtime=(select max(regtime) from monitor where shipname='hondy' and disposeman='1'))应该怎样写才是准确的.
难道触发器不用对monitor的几千条数据重新统计吗
(select rat from monitor where shipname='hondy' and regtime=(select max(regtime) from monitor where shipname='hondy' and disposeman='1'))中的表monitor,
你只能考虑采用:new.column或:old.column;
另外要取:new.column的值,只能before insert or update or delete。
SQL> update test set a1=(select id from aa),a2=(select a1 from t1) where a1 is null;0 rows updatedrat_forecast=(select rat from monitor where shipname='hondy'), regtime=(select max(regtime) from monitor where shipname='hondy' and disposeman='1')