Requires Free Membership to View
A trigger is a piece of code that fires on a given event. Triggers can be based at the system level, on a table or, in 8i and 9i, on a specific column within a table. You say you want the trigger to initiate on the change of system date, and transfer records from one table to another. Triggers normally operate on events like inserting, updating or deleting information in a column, row or table. Changing the date across the system does not qualify as that kind of event.
I think you would be better off to write a stored procedure in PL/SQL, and use another mechanism to start the procedure based on system time. The DBMS_JOB package in PL/SQL is designed to do what you appear to want. It allows a user to schedule a job to run at a specific time, or specific intervals. A job consists of PL/SQL code (ie, a stored procedure). The user also indicates via job parameter how often the code should be run.
The following example shows how to schedule a job to execute at midnight every night:
DBMS_JOB.SUBMIT (:v_jobnum, 'my_procedure;', trunc(sysdate) + 1, 'sysdate + 1');
The 'trunc(sysdate) + 1' se>ts the time back to the current day, midnight and informs the scheduler to start the job at the following midnight. The 'sysdate + 1' sets the interval to 1 day. This job would run the first time at midnight, then every midnight afterwards.
To schedule the job at 8 a.m., specify the next_date parameter as trunc(sysdate) + 1 + 8/24.
You may want to examine the following Notes on Oracle MetaLink:
Note 61730.1 Using the DBMS_JOB Package details how to use this package when scheduling a job.
Note 1068369.6 Example: Using DBMS_JOB.SUBMIT to Execute Jobs at Regular Intervals details examples of using the DBMS_JOB.SUBMIT.
This was first published in January 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation