Printable View
Hi All,
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.
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.
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.
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).