How to activate a trigger on a date

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

i.e.

+---------------------+  +-------------+
|   Trains            |  |    Trips    |
+---------------------+  +-------------开发者_如何学Python+
| 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

---

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).

---

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.