Clever Geek Handbook

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.

+3


source to share


4 answers


According to Oracle the trigger documentation :

You can write triggers that fire when one of the following Operations is performed:

  1. DML statements (INSERT, UPDATE, DELETE) on a specific table or view issued by any user.
  2. DDL (CREATE or ALTER in the first place) issued by either a specific schema / user or any schema / user in the database
  3. 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.

+2


source


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;
0


source


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;
/
0


source 


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;
0


source






More articles:


All Articles