How to fire a Trigger when a variable surpasses/exceed a specific Date

Question

I have a table with an atributte of type "DATE" and I want to fire a Trigger when the atributte achieve a specific date.

For example:

Table A:
        a int primary key;
        d Date;

A(1, '15-05-2015');

When this date arrives I want to fire a specific trigger.

Thanks.

Answer 1

According to Oracle's trigger documentation:

You can write triggers that fire whenever one of the following operations occurs:

1. DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
2. DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
3. Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

The arrival of a date is not one of these unfortunately.

You could, however create an Oracle job that would run at a specific date and execute an Oracle procedure

Oracle's job creation documentation illustrates how to do it:

BEGIN DBMS_SCHEDULER.CREATE_JOB (    job_name           => 
'update_sales',    job_type           =>  'STORED_PROCEDURE',   
job_action         =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',   
start_date         =>  '28-APR-08 07.00.00 PM Australia/Sydney',   
repeat_interval    =>  'FREQ=DAILY;INTERVAL=2', /* every other day */ 
end_date           =>  '20-NOV-08 07.00.00 PM Australia/Sydney',   
job_class          =>  'batch_update_jobs',    
comments           =>  'My new job'); 
END; 
/

The "job_action" field receive the name of the procedure to be executed and "repeat_interval" is to be left blank for a one-of execution.