插入触发器后更新同一表 [英] Update same table after Insert trigger
问题描述
我正在开发一种产品,当有人等待送达15分钟以上时,我必须向相关人员发送短信.
I am working on a product in which I have to send SMS to concerned person when someone waits for more than 15 minutes for being served.
为此,我编写了一个过程,当Duration
超过15时,该过程将监视一个表并将CUST_ID, CUST_CATEGORY, DURATION
存储在单独的表中.该表的表结构为:
For that I have written a procedure that watches a table and stores CUST_ID, CUST_CATEGORY, DURATION
in a separate table when the Duration
exceeds 15. The table structure of this table is:
Some_Table
CUST_ID CUST_CATEGORY DURATION SMS_STATUS
我写了一个触发器为:
触发
create or replace trigger kiosk_sms_trg
after insert on Some_Table
referencing new as new old as old
for each row
BEGIN
SMS_Proc@My_Server; --Procudure that generates SMS
update Some_Table set status = 'Y' where id = (select max(id) id from Some_Table where status = 'N'); --Update Table that SMS has been sent
select 'Y' into :new.status from dual;
END;
但是它会导致突变问题.我该如何解决?任何帮助将不胜感激.我正在使用Oracle 11G.
But it creates Mutation Problem. How do I resolve it? Any help would be highly appreciated. I'm using Oracle 11G.
推荐答案
我认为SOME_TABLE当前正在变异,因此不允许UPDATE.
I don't think that UPDATE is allowed on SOME_TABLE as it is currently mutating.
为什么不将它放在首先触发触发器的INSERT语句之后?
Why not place it right after the INSERT statement which fired the trigger in the first place?.
INSERT INTO SOME_TABLE ...
update Some_Table set status = 'Y' where id = (select max(id) id from Some_Table where status = 'N'); --Update Table that SMS has been sent
考虑到您没有在该UPDATE中执行任何特定于行的操作,我认为这将是正确的方法.
I guess this would be the right approach considering you aren't doing anything row specific in that UPDATE.
正如我在评论中提到的,AFTER INSERT
触发器中的最后一条语句是否有特殊用途?在BEFORE INSERT
触发器中确实具有意义.
As I mentioned in the comment, Is there any particular use for this last statement in the AFTER INSERT
trigger? It does have meaning in the BEFORE INSERT
trigger.
select 'Y' into :new.status from dual;
这篇关于插入触发器后更新同一表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!