Can anybody help please.....

[ramadas]

Hi,
Can anybody tell me whether an time based database trigger be written.
Is it possible...what i want to know is that
If i have one table with fields (say)id,date,flag.Initially flag is set to N.Now is it possible to write a database trigger which fires every 1 hour and what it does is that it checks the date field---(date+7) >sysdate then it erases the id filed of that row and then sets the flag field to Y in that row.It checks for each row...in this way.
This is not to be related with the insert/update/delete actions in that table.What ever DML operations are done it does not fires this trigger..this trigger should fire for every 1 hour on its own and check the status.......
Please tell me whether this kind of time based database trigger is possible or not..if yes then how it can be done.

Thanking u in advance
Ram

[sudip]

hi Ram!
I guess you cannot do it through triggers. You can schedule triggers and you cannot fire a commit from a trigger. What you can do is write a package which does the updation and commits it. Then schedule the package to fire by using dbms_job package. Please go through Oracle help the exact syntax on dbms_job package.
Regards
Sudip

[sudip]

hi Ram!
Sorry I made a mistage in the last post.
the change is in caps this time. Sorry


I guess you cannot do it through triggers. You CANNOT schedule triggers and you cannot fire a commit from a trigger. What you can do is write a package which does the updation and commits it. Then schedule the package to fire by using dbms_job package. Please go through Oracle help the exact syntax on dbms_job package.
Regards
Sudip

[ramadas]

thanks Sudip for the help
Now atleast i have a direction in which i can proceed to get this job done....
Thank you very much.
Ram