How to activate a trigger on a date

Question

How could I have a trigger that updates a certain field when the system reaches a certain date ?

i.e.

+---------------------+  +-------------+
|   Trains            |  |    Trips    |
+---------------------+  +-------------+
| id                  |  |  id         |
| distanceTraveled    |  |  endDate    |
|                     |  |  trainUsed  |
|                     |  |  distance   |
+---------------------+  +-------------+

Trains :

train1, 0
train2, 0
train3, 0
train4, 0

Trips :

1, 12:00:00 tomorrow, train1, 10
2, 14:45:00 tomorrow, train3 ,20
3, 02:15:00 after-tomorow ,train1, 15

Execution :

At 12:00:00 tomorrow, update the table train so the distanceTraveled field for train1 comes to 10
At 14:45:00 tomorrow, update the table train so the distanceTraveled field for train3 comes to 20
At 02:15:00 the day after-tomorrow, update the table train so the distanceTraveled field for train1 comes to 25

Final result in 2 days would be

Trains :

train1, 25
train2, 0
train3, 20
train4, 0

score 7 · Answer 1 · answered Jul 27 '11 at 19:10

7

You should have a look at the DBMS_SCHEDULER package:

The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures that are callable from any PL/SQL program.

It's kind of like a built-in cron (with a lot more features).

answered Jul 27 '11 at 19:10

Mat

202,337
40
393
406

Thanks, not exactly what I need, but it helps me to figure how to do it. – flatzo Jul 27 '11 at 19:24
If that's not exactly what you need, remove the accept and edit your question to provide more detail about what you want. With an accepted answer, less people will look at your question, so you might miss some good advice. (And editing your question will make it "active" and jump back up a bit on the front page.) – Mat Jul 27 '11 at 19:26
+1 for giving an alternative to triggers, the less triggers the better. – Nathan Hughes Jul 27 '11 at 20:39

score 3 · Accepted Answer · answered Jul 27 '11 at 20:45

To flesh out @Mat's answer, I think what you want is something like this:

create or replace procedure update_train_distance(p_train_id trains.id%type,
                                                  p_distance trips.distance%type) is
begin
   update trains 
      set distancetraveled = nvl(distancetraveled,0)+p_distance
      where id = p_train_id;
end update_train_distance;

begin
dbms_scheduler.create_program('sched_train_update',
                              'STORED_PROCEDURE',
                              'UPDATE_TRAIN_DISTANCE',
                              2,
                              TRUE);
dbms_scheduler.define_program_argument('sched_train_update',
                                       1,
                                       'p_train_id',
                                       'VARCHAR2',
                                       '0');
dbms_scheduler.define_program_argument('sched_train_update',
                                       2,
                                       'p_distance',
                                       'NUMBER',
                                       0);
end;

create or replace trigger trips_sched_ai
after insert on trips
for each row
begin
   dbms_scheduler.create_job(job_name => 'TRIP_' || :new.id,
                             program_name => 'sched_train_update',
                             start_date => :new.enddate,
                             auto_drop => true);
   dbms_scheduler.set_job_argument_value(job_name => 'TRIP_' || :new.id,
                                         argument_name => 'p_train_id',
                                         argument_value => :new.trainid);
   dbms_scheduler.set_job_argument_value(job_name => 'TRIP_' || :new.id,
                                         argument_name => 'p_distance',
                                         argument_value => :new.distance);
   dbms_scheduler.enable('TRIP_' || :new.id);
end trg_trips_sched;

You should keep in mind that this is an untested example and that there may be things that I have missed. At a minimum, you probably need to add triggers to handle update or deletes before the job is executed.

How to activate a trigger on a date

2 Answers2