CREATE OR REPLACE TRIGGER RAINFALL_UPDATE_AFTER BEFORE UPDATE
ON WDS.TB_P02_RAINFALL
FOR EACH ROW
DECLARE
VSENID VARCHAR2(32 byte);
VSENNAME VARCHAR2(32 byte);
VTIME DATE;
VONE NUMBER(10,1);
VTWO NUMBER(10,1);
VTHREE NUMBER(10,1);
VFOUR NUMBER(10,1);
VHOUR NUMBER(10,1);
BEGIN
select decode(sign(-(:new.v_one-(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24)))),-1,(:new.v_one-(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24))),0) into VONE from dual;
select decode(sign(-(:new.v_two - :new.v_one)),-1,(:new.v_two - :new.v_one),0) into VTWO from dual;
select decode(sign(-(:new.v_three - :new.v_two)),-1,(:new.v_three - :new.v_two),0) into VTHREE from dual;
select decode(sign(-(:new.v_four - :new.v_three)),-1,(:new.v_four - :new.v_three),0) into VFOUR from dual;
select decode(sign(-(:new.v_four -(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24)))),-1,(:new.v_four -(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24))),0) into VHOUR from dual; UPDATE WDS.TB_P02_RAINFALL_TEMP SET V_ONE=VONE,V_TWO=VTWO,V_THREE=VTHREE,V_FOUR=VFOUR,HOUR_VALUE=VHOUR WHERE
SEN_NAME=:old.SEN_ID AND DT_TIME=:old.DT_TIME;
end;
/
触发器语句如上,主要是要在TB_P02_RAINFALL这个表发生update事件时,同时进行一些操作,update TB_P02_RAINFALL_TEMP这个表,但在这些计算操作中,因为有需要对原TB_P02_RAINFALL表进行查询,所以会报‘表发生变化,触发器无法访问’这样的错误。为什么before update也不行呢?
应该怎么改?谢谢
ON WDS.TB_P02_RAINFALL
FOR EACH ROW
DECLARE
VSENID VARCHAR2(32 byte);
VSENNAME VARCHAR2(32 byte);
VTIME DATE;
VONE NUMBER(10,1);
VTWO NUMBER(10,1);
VTHREE NUMBER(10,1);
VFOUR NUMBER(10,1);
VHOUR NUMBER(10,1);
BEGIN
select decode(sign(-(:new.v_one-(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24)))),-1,(:new.v_one-(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24))),0) into VONE from dual;
select decode(sign(-(:new.v_two - :new.v_one)),-1,(:new.v_two - :new.v_one),0) into VTWO from dual;
select decode(sign(-(:new.v_three - :new.v_two)),-1,(:new.v_three - :new.v_two),0) into VTHREE from dual;
select decode(sign(-(:new.v_four - :new.v_three)),-1,(:new.v_four - :new.v_three),0) into VFOUR from dual;
select decode(sign(-(:new.v_four -(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24)))),-1,(:new.v_four -(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24))),0) into VHOUR from dual; UPDATE WDS.TB_P02_RAINFALL_TEMP SET V_ONE=VONE,V_TWO=VTWO,V_THREE=VTHREE,V_FOUR=VFOUR,HOUR_VALUE=VHOUR WHERE
SEN_NAME=:old.SEN_ID AND DT_TIME=:old.DT_TIME;
end;
/
触发器语句如上,主要是要在TB_P02_RAINFALL这个表发生update事件时,同时进行一些操作,update TB_P02_RAINFALL_TEMP这个表,但在这些计算操作中,因为有需要对原TB_P02_RAINFALL表进行查询,所以会报‘表发生变化,触发器无法访问’这样的错误。为什么before update也不行呢?
应该怎么改?谢谢
解决方案 »
- 求指点:VMware中xp3死活装不上Oracle 10g
- JDBC连接ORACLE时怎么样在代码中修改连接用户名的登陆身份 如SYS必须以SYSDBA身份登陆
- Oracle把记录导出成文件
- vs2005+oracle数据库 select带参数的sql语句出现"非法的变量名/编号”问题
- 如何在数据中察看表的内容
- 通过dmp文件,来重新建一个数据库的问题(100分)
- 最初级问题,请帮帮我..................急................谢了
- oracle的常规优化?
- Oracle复制管理问题,有谁知道oracle 817标准版又没有高级复制功能?有没有表分区功能?
- 各位,我在java中连接oracle数据库,出现了点问题,请问是什么原因,谢谢!
- oracle 10G如何管理HWM
- SOS,oracle创建库内存分配
select decode(sign(-(:new.v_one-(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24)))),-1,(:new.v_one-(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24))),0) into VONE from dual; select decode(sign(-(:new.v_four - :new.v_three)),-1,(:new.v_four - :new.v_three),0) into VFOUR from dual;
select decode(sign(-(:new.v_four -(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24)))),-1,(:new.v_four -(select s.v_one from tb_p02_rainfall s where s.sen_id = :old.sen_id and s.dt_time=(:old.dt_time-1/24))),0) into VHOUR from dual; 这两句,都是要查询原表update后同sen_id一个小时前的那条数据,然后作计算,要怎么解决呢?