I want to know if it is possible to activate a trigger based on a change in the system date.
The event is the change of date, rather than an update of a variable in a table.


So basically I want to achieve something like

Create or replace trigger after update OF sysdate

Or something along those lines.

Is there anyway possible????

Thanks for any help

but is the date not consistently changing away?

are you referring to changing the date for a session

or

changing the date for an entiry database?


regards

edwin

The Oracle DBMS_JOB package provides scheduling capabilities.

yes but if you schedule a job to see if the date has changed with trigger everytime as the date / time is always changing.

- You should put system date into a table column each evening.

- When you run the job next day check the sysdate against the fixed stored date and this will allow you to trigger your procedure.

e.g. trunc(sysdate) != trunc(p_stored_date)

-At the end of that day then put system date into a table column again


Catch my drift?

If the DBMS was not shut down the night before, there would be no method to determine the next execution time of the procedure. Of course the user can run a procedure manually that checks the current SYSDATE agains't the stored date, however the problem is still how the SYSDATE is inserted into the table.

By implementing the DBMS_JOB package, each day at midnight the PL/SQL procedure can be executed to determine if a new month has commenced. If so then execute the relevant code. The interval between each execution of the procedure can be set to 24hrs.

Thanks everyone for the idea, the DBMS_JOB package notion does work but the assignment forbids using that functionality.

Thanks 'edwinjames' I got your drift, and have implemented it successfully.

Again thanks to everyone