如何在触发器中动态创建作业计划?
我正在创建一个图书馆系统。
当预订一本书时,如果预订用户没有借阅它,我希望它在 3 天内自动将状态更改回“可用”。
当状态更改为“保留”时,我可以创建一个触发器来触发,但我对创建 3 天内发生的工作感到迷失,并将状态更改回“可用”
任何评论、建议和指导将不胜感激:)
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 :)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您应该首先创建一个过程来根据需要更新列,将书籍 ID(或其他任何 PK)作为输入参数。
在触发器中,调用 dbms_scheduler 包的提交过程,并在 3 天时间内定义开始日期,没有冗余,并使用 :new.bookid 作为输入参数运行之前定义的过程。
一旦事务稍后提交,作业就会被提交。否则,如果事务回滚,作业也将回滚。
尼古拉斯.
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.
你用什么语言编码?
一般来说,对于这样的事情,我会编写一个定期运行的 cron 作业(每天在图书馆开放时间之前运行一次?),执行查询以查看已保留超过 3 天的所有内容,并将其设置回可用状态。
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.