我想做一个触发器,让数据库自动删除一个星期以前的数据CREATE OR REPLACE TRIGGER TR_WARNING
AFTER INSERT ON T_WARNING
BEGIN
DELETE FROM T_WARNING WHERE WARNIN_DATE+interval'7' <=:NEW.SYSDATE
END IF;
END;但是这样好像不行,大侠给改进一下
AFTER INSERT ON T_WARNING
BEGIN
DELETE FROM T_WARNING WHERE WARNIN_DATE+interval'7' <=:NEW.SYSDATE
END IF;
END;但是这样好像不行,大侠给改进一下
CREATE OR REPLACE TRIGGER TR_WARNING
AFTER INSERT ON T_WARNING
BEGIN
execute immediate 'DELETE FROM T_WARNING WHERE WARNIN_DATE+interval'7' <=:NEW.SYSDATE';
END IF;
END;
AFTER INSERT ON T_WARNING
BEGIN
execute immediate 'DELETE FROM T_WARNING WHERE WARNIN_DATE+interval'7' <=:NEW.SYSDATE';
END IF;
END;
WARNIN_DATE+interval'7'? 这个是什么语法?,7天直接加7就可以了
注:如果是年月日的一周前,要注意WARNIN_DATE可能包含时间的(使用TRUNC(WARNIN_DATE,'DD')清除时间)另:感觉楼主的意思应该用JOB(定时任务)来完成,而不是触发器
create or replace procedure test_pro is
begin
delete from t where createtime <= sysdate - 7
commit;
end test_pro;
SQL> var job1 number;
SQL> begin
2 dbms_job.submit(:job1,'test_pro;',sysdate,'sysdate+1');
4 end;
6 /