触发器里的删除语句应该是delete from DEVICECOM where DEVID=:old.DEVID;
这样啊。但从你写的错误提示来看,可以肯定是由于数据不存在造成的。你是对两张表的操作呢,你测试的时候是否是先执行了insert的,然后测试delete?insert into 要commit的吧?
create or replace trigger tr_af_DEVBATTERY after insert or update or delete on DEVBATTERY for each row declare V_DEVID CHAR(18); V_PARENTID CHAR(18); V_PTYPEID CHAR(4); V_DEVTYPEID CHAR(4); V_PSYSID CHAR(18); V_BATTNAME VARCHAR2(50); V_CAPITALCARD CHAR(20); begin if deleting then delete from DEVICECOM where DEVID=:old.V_DEVID; else V_DEVID:=:new.DEVID; V_PARENTID:=:new.PARENTID; V_PTYPEID:=:new.PTYPEID; V_DEVTYPEID:=:new.DEVTYPEID; V_BATTNAME:=:new.MBATTNAME; V_CAPITALCARD:=:new.CAPITALCARD; if inserting then select SYSID into V_PSYSID from DEVICECOM where DEVID=V_PARENTID; insert into DEVICECOM(DEVID,PARENTID,PTYPEID,DEVTYPEID,DEVNAME,CAPITALCARD,SYSID) values(V_DEVID,V_PARENTID,V_PTYPEID,V_DEVTYPEID,V_BATTNAME,V_CAPITALCARD,V_PSYSID); else update DEVICECOM set DEVNAME=V_BATTNAME,CAPITALCARD=V_CAPITALCARD where DEVID=V_DEVID; end if; end if; end tr_af_DEVBATTERY;
ORA-06512: 在"POWUSER.TR_AF_DEVBATTERY", line 16
ORA-04088: 触发器 'POWUSER.TR_AF_DEVBATTERY' 执行过程中出错
抱的是这个错,
after改成before了,也不行
after insert or update or delete on DEVBATTERY
for each row
declare
V_DEVID CHAR(18);
V_PARENTID CHAR(18);
V_PTYPEID CHAR(4);
V_DEVTYPEID CHAR(4);
V_PSYSID CHAR(18);
V_BATTNAME VARCHAR2(50);
V_CAPITALCARD CHAR(20);
begin
if deleting then
delete from DEVICECOM where DEVID=:old.V_DEVID;
else
V_DEVID:=:new.DEVID;
V_PARENTID:=:new.PARENTID;
V_PTYPEID:=:new.PTYPEID;
V_DEVTYPEID:=:new.DEVTYPEID;
V_BATTNAME:=:new.MBATTNAME;
V_CAPITALCARD:=:new.CAPITALCARD;
if inserting then
select SYSID into V_PSYSID from DEVICECOM where DEVID=V_PARENTID;
insert into DEVICECOM(DEVID,PARENTID,PTYPEID,DEVTYPEID,DEVNAME,CAPITALCARD,SYSID) values(V_DEVID,V_PARENTID,V_PTYPEID,V_DEVTYPEID,V_BATTNAME,V_CAPITALCARD,V_PSYSID);
else
update DEVICECOM set DEVNAME=V_BATTNAME,CAPITALCARD=V_CAPITALCARD where DEVID=V_DEVID;
end if;
end if;
end tr_af_DEVBATTERY;