How do i dynamically create a job schedule in a trigger?

Question

I am creating a library system.

When a book is reserved, i want it to automatically change the status back to "Available" in 3 days if the reserved user does not borrow it.

I can create a trigger to fire when the status is changed to "Reserved" but I am lost on creating a job to happen in 3 days and change the status back to "Available"

Any comments, advises and guidance will be greatly appreciated :)

Answer 1

Hi Ranhiru, what language are you coding in?

Generally for something like this I write a cron job which would run periodically (once a day before library hours?), do a query to see everything that's been reserved for more than 3 days, and set it back to available.

Answer 2

You should first create a procedure to update the column as you want, taking as an input parameter the book id (or whatever else as PK).

In your trigger, call the submit procedure of dbms_scheduler package and define the start date in 3 days time, without redundance, and to run your procedure defined earlier with the :new.bookid as input parameter.

Once the transaction has be commited later on, the job will be submitted. Else, in case of rollback of the transaction the job will be rolled back as well.

Nicolas.