1

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.

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

I wrote a trigger as:

Trigger

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;

But it creates Mutation Problem. How do I resolve it? Any help would be highly appreciated. I'm using Oracle 11G.

Ingila Ejaz
  • 399
  • 7
  • 25

2 Answers2

1

I don't think that UPDATE is allowed on SOME_TABLE as it is currently mutating.

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

I guess this would be the right approach considering you aren't doing anything row specific in that UPDATE.

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;
toddlermenot
  • 1,588
  • 2
  • 17
  • 33
  • The Status col will be by default N, when text has been sent, only then it needs to be Y. – Ingila Ejaz Sep 27 '14 at 08:16
  • I see that you are using an "AFTER INSERT" trigger. Shouldn't that be an "BEFORE INSERT" trigger for this to happen? – toddlermenot Sep 27 '14 at 08:18
  • If I use Before Insert, the record ID of the previous customer gets picked. – Ingila Ejaz Sep 27 '14 at 08:40
  • @IngilaEjaz I couldn't understand you. Please explain this scenario by editing / adding to your question. – toddlermenot Sep 27 '14 at 08:43
  • @Ingila In a _before trigger_, the `id` is either accessible from `:new.id` (if it was set before the trigger has fired) -- or, if you need to, you might generate it from a sequence directly from that trigger. No need for sub-optimal solutions like `select max(id) ...` to get that value. – Sylvain Leroux Sep 27 '14 at 12:48
  • @toddlermenot yes that's exectly what I did :) Updated the table in the procedure after the trigger was fired.. Thanks :) – Ingila Ejaz Sep 29 '14 at 05:22
  • @SylvainLeroux Thanks for the suggestion but the sequence is reset everyday. So I don't think that would be a fine approach.. However, the problem is resolved :) Thanks for the help :) – Ingila Ejaz Sep 29 '14 at 05:23
  • @Ingila If you solved your issue yourself, please feel free to post your own answer and self-accept it. This will mark the issue as resolved -- and it will help anyone coming to this question from a search on Internet. – Sylvain Leroux Sep 29 '14 at 08:59
0

You cannot update the same table in Row-Level AFTER Trigger.
Change your Row-Level AFTER INSERT trigger to row-level BFEORE INSERT trigger.

But you UPDATE stmt inside the trigger will not effect the new record being inserted.

Wonder how it can be done, this is tricky.

user1897277
  • 485
  • 4
  • 13