插入触发器后更新同一张表 [英] 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屋！