How to fire a trigger when a variable exceeds / exceeds a specific date
I have a table with an attribute of type "DATE" and I want to fire a trigger when the atributte reaches a certain date.
For example:
Table A:
a int primary key;
d Date;
A(1, '15-05-2015');
When that date arrives, I want to fire a specific trigger.
Thank.
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.
You can create before insert / update for every row trigger that fires every time you insert / update this table. In the trigger body, you can test
if :new.d = to_date('2015-05-15','yyyy-mm-dd') then
--put you code here
end if;
You can try this
CREATE OR replace TRIGGER trg_ex
AFTER UPDATE OR DELETE ON ex1
FOR EACH ROW
WHEN ( NEW.DATE='--' )
BEGIN
dbms_output.Put_line('Trigger is fired');
END;
/
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;