How to fire a trigger when a variable exceeds / exceeds a specific date
According to Oracle the trigger documentation :
You can write triggers that fire when one of the following Operations is performed:
- DML statements (INSERT, UPDATE, DELETE) on a specific table or view issued by any user.
- DDL (CREATE or ALTER in the first place) issued by either a specific schema / user or any schema / user in the database
- Database events such as login / logout, errors or startup / shutdown are also issued by either a specific schema / user or some schema / user to the database
Arrival date is not one of them, unfortunately.
However, you could create an Oracle Job that will run on a specific date and execute the Oracle procedure
Oracle documentation on creating jobs shows how to do this:
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 is given the name of the procedure to be executed and the " repeat_interval " must be empty for a one-time execution.
source to share
CREATE OR REPLACE TRIGGER yourtable_before_insupd BEFORE INSERT OR UPDATE ON yourtable FOR EACH ROW WHEN (NEW.date = '15-05-2015') DECLARE -- your declarations here - v_date is just an example v_date DATE; BEGIN -- your computations here -- Find date of the item - just as example SELECT date INTO v_date FROM dual; -- update the date of the item - just as example :new.date := sysdate; END;
source to share