0

I have a stage table (TableA) where data containing XMLType is populated from a Queue. I wrote and After Insert Trigger to process this XMLType Field and insert the data to another Table(TableB). After inserting into TableB, I want to update the TableA with status as 'Completed'. I have verified other questions and most of them suggested Before Insert Trigger, which is not an option to me. I have already checked "Update same table after Insert trigger", "Oracle AFTER INSERT Trigger", "Update with after insert trigger on same table", "Updating the same row in oracle during a trigger?". As Queue is populating data I cannot follow "Copy records with insert select, then update attributes in the same set of records" as well. I saw this "Update same table after Insert trigger" but couldnt understand how it was implemented. I tried directly updating using update as well as calling a Procedure which is having update statement and both failed causing Mutation Problem. Appreciate your help.

Community
  • 1
  • 1
java-ocean
  • 59
  • 1
  • 10
  • Why is Before Insert trigger "not an option" for you? If you want us to help you need to tell us why you can't use the obvious solution. Also you should give us some description regarding the process you apply to the XMLType. Basically, a list of questions you think don't apply to your situation do not help us understand what you actually want to achieve. – APC Jun 24 '15 at 04:02
  • @APC Since I need to process & populate another table, I thought before insert is not correct way of implementation. I was able to achieve the solution with compound trigger as well as with before insert trigger. But I am not sure if there are any side effects of the before trigger vs compound trigger. Coming to XMLType I am just extracting the data from XML to columns as below – java-ocean Jun 24 '15 at 13:10
  • `INSERT INTO TableB (SELECT t.reference, t.requestor FROM TableA p,XMLTable('/PO' PASSING p.XML_DOCCOLUMNS reference VARCHAR2(28) PATH 'Reference', requestor VARCHAR2(48) PATH 'Requestor') t );` – java-ocean Jun 24 '15 at 13:19

0 Answers0