PL/SQL Trying to update a table through trigger on the same table
628858Mar 17 2008 — edited Mar 18 2008Hi everyone my name is Edwin and I'm new to this forum. I hope I can learn a lot from this community and over time contribute to it.
The problem I'm facing at the moment is a tough one. I need to update a table (table a) with a value called block_id. This block_id is generated by a procedure I have written myself, the end result is stored in another table (table b). This block_id needs to be generated after certain rows of table a get updated with a trans_id. The trans_id is all the same for these rows in table a, but the generated block_id isn't. In my code I use an after update on collumn a of table a trigger. So if the the collumn gets updated the trigger fires. The trigger calls the procedure that generates the block_id and the procedure generates table b with all the block_id's. But then I want to update the rows in table a with the generated block_id in table b. The problem is that this self-deadlocks.
You might think that this would call a recursive trigger, but I have written code in the trigger that checks if the block_id isn't allready filled in on table a.
Also I really need all the values wich get updated (table a), so I believe a before update is also out of the question.
And the first update of table a is done through an erp-packet and I can't get at that code, otherwise I would just have run my code from there.
Message was edited by:
user625855