One of my developers has asked me to come up with a way to delete rows based on a timestamp. He wants the row to be deleted after 48 hours. I was thinking a stored procedure with DBMS_JOB running it at certain intervals. Is this overkill could I use a trigger - after insert if list_dt > SysDate + 48 then delete from...I'm not familar with stored procedures or DBMS_JOB so I don't want to bit off more than I have to. Thanks for the help.
01-17-2001, 02:46 PM
From what I understand, you need to delete all records over 48 hours, but do you have to that at a specified time interval ? Because if that is the case, then the trigger during a insert can be ruled out unless you insert records every day.
I am not sure about the dbms_job package. You could write a small daemon in Pro*C that wakes up every day in the morning and does the cleanup job, else on a unix box, you can put an entry in the chron jobs and have it run at a specific time.
I think there is a place in Oracle too, where you can specify it, but I am not sure on the name of the file.
01-17-2001, 06:56 PM
With DBMS_JOBS you can can schedule PL/SQL procedures at a predefined time or a specific interval. With DBMS_JOB you can delete the rows at timed intervals. With triggers you don't know at what time they are fired as tmnath mentioned.
01-17-2001, 08:26 PM
If you are using Unix as the OS then you may call the delete script in a cron job and run the cron job at the desired interval. You can run the cron job at the pre determined interval(like first Monday of the month).