trigger mutating error

[Borracho1]

Hi,

i'm currently facing a problem with a trigger.

I created 2 tables:
-communication
-communication_data

The trigger i created for communication is of type ( after insert or update on status for each row)

whenever an insert is done in communication the trigger is raised and a dbms_alert is raised. The coding that listens to this alert does a read from the 2 tables and starts up a webservice communication.

So far so good but whenever this webservice has retrieved some data it's sending a reply (insert in the data table and an update of the status in the communication table)

After this status update i am intrepreting/converting the reply and doing inserts in other tables.

But when i try this its always throwing me this trigger mutating error.


I googled a bit and understand that this error is thrown when a trigger references the table that owns the trigger. But at the moment that the reply is coming back, everything is committed so i would assume that the 2nd raise of the trigger is a completely different action, no?

I found that it could be a help to start using autonomous transactions. I could also do the data conversion later on and triggered by other coding but than this increases the total execution time and i could be impacting endusers.

i am doing this on a oracle 11g database and for simulating the webservice communication i am using a stored procedure which is listening to this alert and providing the reply.


should i reconsider this way of working or are there any alternatives that can make this work?
thanks in advance.

Koen

 

[Dagon]

a) Is the trigger attempting to select from either communication or communication_data at any stage?
b) Do you have a foreign key relationship between communication and communication_data?

 

[Borracho1]

hi,

a)when insert is done, trigger is fired and passes status and a pk-field to a stored proc.If status is initial i am starting a dbms_alert.signal.

The coding that is waiting for this alert now does a select from communication and communication data. Inserts the reply in communication data and updates status of communication table to finished. Which again should cause the trigger to start and call the stored proc that interpretes the status and now converts the data...

But the 2nd time the trigger is fired, i get the mutating problem.

So yes, a select is happening but doesnt the trigger unlock the row after the dbms_alert.signal is done?

b) yes. pk of communication is a number(13) and is referenced in the data table.

Koen

 

[Dagon]

I'm still a bit unclear on exactly what your trigger is doing, but it appears that:

on insert
- just sends the signal

on update
- interprets the status and <i>now converts the data</i>

I suspect it's the data conversion that is causing the problem. Are you trying to do reads and/or writes to communication or communication_data at this point? If so, it won't work. You can't get round a mutating table issue just by moving the code to an SP.

 

[taupirho]

To me it seems as though what would happen is

Insert original data
|
Trigger fires on insert
-> read data
-> update data
|
-> (oops! trigger fires again on update this time)
-> read data
-> update data -
|
-> oops! trigger fires again on update
and so on etc
...

Hence mutating table error to prevent this recursive trigger loop from ocurring


In order to understand recursion, you must first understand recursion.

 

[Dagon]

I don't think that would cause a mutating table error, although it might cause an infinite loop. The code which listens for the message and sends back the update appears to be outside of the trigger, so the trigger wouldn't know anything about it.

 

[taupirho]

In OP I think this phrase is key "But at the moment that the reply is coming back, everything is committed" . My question is how do we know that ?

The trigger code must look something like

if status = 'I' -- Insert status code
then
raise the alert
elsif status = 'F' -- Finish status code
call the finish_procedure -- process F status update
end if

Now unless I'm mistaken it seems that the procedure that reads and processes the alert also reads the table and updates its status to F for finish, say, so the above trigger will fire again calling the finish procedure. When this happens the table must still be in changed state from previous processing otherwise the mutating error would not occur

Of course I could be getting this all wrong, perhaps the OP should post some bare bones code and table create/inserts to allow us to recreate things more fully





In order to understand recursion, you must first understand recursion.

 

[Borracho1]

thanks for the replies due to some problems i wasnt able to post sooner.

Dagon, your interpretion is indeed what i am trying to do.
'on insert - just sends the signal for retrieving card data'
'on update - interprets the status and <i>now converts the data</i>

Taupirho,
trigger coding is indeed like you suggested

if status = 'I' then
raise the alert
elsif status = 'F'
call the finish_procedure
end if

'Now unless I'm mistaken it seems that the procedure that reads and processes the alert also reads the table and updates its status to F for finish, say, so the above trigger will fire again calling the finish procedure. When this happens the table must still be in changed state from previous processing otherwise the mutating error would not occur'

indeed correct. Sounds very logic, i'll check it tomorrow. At this moment i am also thinking of expanding the primary key with a message type (request/reply). In stead of 1 row with a status and some timestamps and a resultcode.

I'll do some extra testing and if i dont get it to work i'll fabricate a small script.

and sorry but what does op mean? thread starter or something?

Already thanks a lot for the previous replies!

Koen

 

[taupirho]

OP - original post(er)


In order to understand recursion, you must first understand recursion.