Oracle Trigger实例

最新推荐文章于 2021-04-16 02:24:52 发布

原创最新推荐文章于 2021-04-16 02:24:52 发布 · 708 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #application #insert #each

Sql 专栏收录该内容

19 篇文章

订阅专栏

本文介绍了一个Oracle数据库触发器的实现方式，该触发器在插入新记录前检查最近的数据记录，并依据参数设置的时间间隔来决定是否将旧数据备份并删除。

create or replace trigger trigger_Twprecord_useDateTime before INSERT ON T_WPPRECORD for each row declare last_datetime date; param_num number; BEGIN select RECTIME into last_datetime from T_WPPRECORD where rownum<=1 order by RECID desc; select PARAMNUMNAME into param_num from t_param_num where PARAMNUMID=2; --RAISE_APPLICATION_ERROR(-20001, to_char(:new.RECTIME,'yyyy-mm-dd hh24:mi:ss')||'test'||to_char(last_datetime,'yyyy-mm-dd hh24:mi:ss')); If ROUND(TO_NUMBER(sysdate - last_datetime) * 24)>=param_num THEN insert into T_WPPRECORD_backup(RECID,RECTIME,STATUS,TOTALACTENERGY,TOTALREENERGY,TOTALACTLOAD,TOTALRELOAD,ACTPLANT,REPLANT,AREAACTENERGY,AREAREENERGY,RESERVED1,RESERVED2,WPPID) select RECID,RECTIME,STATUS,TOTALACTENERGY,TOTALREENERGY,TOTALACTLOAD,TOTALRELOAD,ACTPLANT,REPLANT,AREAACTENERGY,AREAREENERGY,RESERVED1,RESERVED2,WPPID from T_WPPRECORD where RECTIME<=last_datetime; delete from T_WPPRECORD where RECTIME<=last_datetime; END IF; END trigger_Twprecord_useDateTime;