Create Or Replace Trigger Hvm_cfg_normalUpdateAfter
after insert or update or delete
on hvm_cfg_normal
for each row
declare
deviceNum number; --判断装置表(device)中是否存在监测装置的个数
begin
if inserting then --新增
insert into test values('','','',:new.objid);
update Hvm_ObjAllSts o set o.devexist=1 where o.sbbm=:new.objid;
elsif deleting then --删除
select count(0) into deviceNum from Hvm_ObjAllSts o where o.sbbm=:old.objid;
if deviceNum<0 then
update Hvm_ObjAllSts o set o.devexist=0 where o.sbbm=:old.objid;
end if;
elsif updating then
select count(0) into deviceNum from Hvm_ObjAllSts o where o.sbbm=:old.objid;
if :old.objid!=:new.objid and deviceNum<0 then
update Hvm_ObjAllSts o set o.devexist=0 where o.sbbm=:old.objid;
update Hvm_ObjAllSts o set o.devexist=1 where o.sbbm=:new.objid;
end if;
end if;
end;这个是我的触发器,当hvm_cfg_normal表有新增的时候 修改 表Hvm_ObjAllSts 的字段devexist为1。不能修改
insert into test values('','','',:new.objid);
这个语句测试时候得到了objid,当我新增一条记录时在test表中却是看到了这个objid,但是
update Hvm_ObjAllSts o set o.devexist=1 where o.sbbm=:new.objid;
这个语句不能修改Hvm_ObjAllSts表中的devexist。大家帮我看看是什么原因了。谢谢各位了……
after insert or update or delete
on hvm_cfg_normal
for each row
declare
deviceNum number; --判断装置表(device)中是否存在监测装置的个数
begin
if inserting then --新增
insert into test values('','','',:new.objid);
update Hvm_ObjAllSts o set o.devexist=1 where o.sbbm=:new.objid;
elsif deleting then --删除
select count(0) into deviceNum from Hvm_ObjAllSts o where o.sbbm=:old.objid;
if deviceNum<0 then
update Hvm_ObjAllSts o set o.devexist=0 where o.sbbm=:old.objid;
end if;
elsif updating then
select count(0) into deviceNum from Hvm_ObjAllSts o where o.sbbm=:old.objid;
if :old.objid!=:new.objid and deviceNum<0 then
update Hvm_ObjAllSts o set o.devexist=0 where o.sbbm=:old.objid;
update Hvm_ObjAllSts o set o.devexist=1 where o.sbbm=:new.objid;
end if;
end if;
end;这个是我的触发器,当hvm_cfg_normal表有新增的时候 修改 表Hvm_ObjAllSts 的字段devexist为1。不能修改
insert into test values('','','',:new.objid);
这个语句测试时候得到了objid,当我新增一条记录时在test表中却是看到了这个objid,但是
update Hvm_ObjAllSts o set o.devexist=1 where o.sbbm=:new.objid;
这个语句不能修改Hvm_ObjAllSts表中的devexist。大家帮我看看是什么原因了。谢谢各位了……
referencing old as old_value
然后把
update Hvm_ObjAllSts o set o.devexist=0 where o.sbbm=:old.objid;
改成
update Hvm_ObjAllSts o set o.devexist=0 where o.sbbm=:old_value.objid;
:= . ( @ % ;
行:10
文本:referencing old as new_value错误:提示: Variable 'sbbm' 已被声明但从来没有被用于 'Hvm_cfg_normalUpdateAfter'
行:6
文本:sbbm CHAR(32);错误:提示: Variable 'stationid' 已被声明但从来没有被用于 'Hvm_cfg_normalUpdateAfter'
行:7
文本:stationid CHAR(32);