上面的trigger 错了,重发:
create or replace trigger tr_af_DEVMODULE1
before insert or update or delete on DEVMODULE
for each row
declare
v_count integer;
v_kind char(10);
v_volume number;
v_oldvolume number;
v_edmodel number;
v_sjmodel number;
v_jijiaid char(18);
v_shebeiid char(18);
v_sysid char(18);
begin
if inserting then
select MODUTYPENAME into v_kind from MODUTYPE where MODUTYPEID=:new.MODUTYPEID;
else
select MODUTYPENAME into v_kind from MODUTYPE where MODUTYPEID=:old.MODUTYPEID;
end if;
if (v_kind = '整流模块') then
if deleting then
v_jijiaid:=:old.PARENTID;
v_oldvolume:=:old.VOLUME;
select PARENTID into v_shebeiid from DEVRESOUSE where EEID=v_jijiaid;
select PARENTID into v_sysid from DEVICECOM where DEVID=v_shebeiid;
select CONVOLUME into v_sjmodel from SYSDC where SYSID=v_sysid;
if (v_sjmodel > v_oldvolume) then
v_sjmodel := v_sjmodel - v_oldvolume;
else
v_sjmodel := 0;
end if;
update SYSDC set CONVOLUME = v_sjmodel where SYSID=v_sysid;
select count(*) into v_count from DEVMODULE where PARENTID=v_jijiaid;
if (v_count = 1) then
update SYSDC set OUTVOLUME = 0 where SYSID=v_sysid;
update DEVICECOM set VOLUME = 0 where DEVID=v_shebeiid;
end if;
else
if inserting then
v_jijiaid:=:new.PARENTID;
v_volume:=:new.VOLUME;
select CONFNUMBER into v_edmodel from DEVRESOUSE where EEID=v_jijiaid;
select PARENTID into v_shebeiid from DEVRESOUSE where EEID=v_jijiaid;
select PARENTID into v_sysid from DEVICECOM where DEVID=v_shebeiid;
select CONVOLUME into v_sjmodel from SYSDC where SYSID=v_sysid;
v_sjmodel := v_sjmodel + v_volume;
update SYSDC set CONVOLUME = v_sjmodel,OUTVOLUME = v_edmodel * v_volume where SYSID=v_sysid;
update DEVICECOM set VOLUME = v_edmodel * v_volume where DEVID=v_shebeiid;
else
v_jijiaid:=:old.PARENTID;
v_volume:=:new.VOLUME;
v_oldvolume:=:old.VOLUME;
select CONFNUMBER into v_edmodel from DEVRESOUSE where EEID=v_jijiaid;
select PARENTID into v_shebeiid from DEVRESOUSE where EEID=v_jijiaid;
select PARENTID into v_sysid from DEVICECOM where DEVID=v_shebeiid;
select CONVOLUME into v_sjmodel from SYSDC where SYSID=v_sysid;
v_sjmodel := v_sjmodel + v_volume - v_oldvolume;
if (v_sjmodel < 0) then
v_sjmodel := 0;
end if;
update SYSDC set CONVOLUME = v_sjmodel,OUTVOLUME = v_edmodel * v_volume where SYSID=v_sysid;
end if;
end if;
end if;
end tr_af_DEVMODULE1;
create or replace trigger tr_af_DEVMODULE1
before insert or update or delete on DEVMODULE
for each row
declare
v_count integer;
v_kind char(10);
v_volume number;
v_oldvolume number;
v_edmodel number;
v_sjmodel number;
v_jijiaid char(18);
v_shebeiid char(18);
v_sysid char(18);
begin
if inserting then
select MODUTYPENAME into v_kind from MODUTYPE where MODUTYPEID=:new.MODUTYPEID;
else
select MODUTYPENAME into v_kind from MODUTYPE where MODUTYPEID=:old.MODUTYPEID;
end if;
if (v_kind = '整流模块') then
if deleting then
v_jijiaid:=:old.PARENTID;
v_oldvolume:=:old.VOLUME;
select PARENTID into v_shebeiid from DEVRESOUSE where EEID=v_jijiaid;
select PARENTID into v_sysid from DEVICECOM where DEVID=v_shebeiid;
select CONVOLUME into v_sjmodel from SYSDC where SYSID=v_sysid;
if (v_sjmodel > v_oldvolume) then
v_sjmodel := v_sjmodel - v_oldvolume;
else
v_sjmodel := 0;
end if;
update SYSDC set CONVOLUME = v_sjmodel where SYSID=v_sysid;
select count(*) into v_count from DEVMODULE where PARENTID=v_jijiaid;
if (v_count = 1) then
update SYSDC set OUTVOLUME = 0 where SYSID=v_sysid;
update DEVICECOM set VOLUME = 0 where DEVID=v_shebeiid;
end if;
else
if inserting then
v_jijiaid:=:new.PARENTID;
v_volume:=:new.VOLUME;
select CONFNUMBER into v_edmodel from DEVRESOUSE where EEID=v_jijiaid;
select PARENTID into v_shebeiid from DEVRESOUSE where EEID=v_jijiaid;
select PARENTID into v_sysid from DEVICECOM where DEVID=v_shebeiid;
select CONVOLUME into v_sjmodel from SYSDC where SYSID=v_sysid;
v_sjmodel := v_sjmodel + v_volume;
update SYSDC set CONVOLUME = v_sjmodel,OUTVOLUME = v_edmodel * v_volume where SYSID=v_sysid;
update DEVICECOM set VOLUME = v_edmodel * v_volume where DEVID=v_shebeiid;
else
v_jijiaid:=:old.PARENTID;
v_volume:=:new.VOLUME;
v_oldvolume:=:old.VOLUME;
select CONFNUMBER into v_edmodel from DEVRESOUSE where EEID=v_jijiaid;
select PARENTID into v_shebeiid from DEVRESOUSE where EEID=v_jijiaid;
select PARENTID into v_sysid from DEVICECOM where DEVID=v_shebeiid;
select CONVOLUME into v_sjmodel from SYSDC where SYSID=v_sysid;
v_sjmodel := v_sjmodel + v_volume - v_oldvolume;
if (v_sjmodel < 0) then
v_sjmodel := 0;
end if;
update SYSDC set CONVOLUME = v_sjmodel,OUTVOLUME = v_edmodel * v_volume where SYSID=v_sysid;
end if;
end if;
end if;
end tr_af_DEVMODULE1;
解决方案 »
- 让数据库中的一列能在表中平均分配个数
- plSql问题
- 如何把mssql 的数据库移植到oracle数据库中?
- 如何写一个符合此条件的SQL语句
- oracle9i,字符串很长的情况下,用什么数据类型?
- ORA-00932: 不一致的数据类型: 要求 - 得到的却是 CLOB
- 求助;sql server数据库导入到oracle数据库的方法
- ORACLE数据库连接故障
- select 语句的from 中 能用函数吗?
- SI object browser 管理oracle 数据库
- sql语句高手来帮帮忙!!看看这个语句怎么写
- 本人谦虚求教:1:oracle9i过程中,如果客户端没有安装,只安装了服务器端,那么管理服务器是否能正常安装。2:如果要建立一个数据库,是
不能对触发的表进行操作