插入触发器后更新同一张表
我正在开发一种产品，当有人等待服务超过 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;
相关文章