PL/SQL Trying to update a table through trigger on the same table

[DreamersDelight]

Hi,
I'm stuck on this problem and I can't find a sollution. I'm going to try and explain this step by step.


1 After certain rows get updated with a certain value. I don't know wich rows in advance. And they do not have a certain order, but after the update they all have the same trans_id. (in unload_details_ tab).
2 I select select the rows using the value that just got updated (trans_id), this way I know wich rows I needed to select, the value is the same for all the rows that I need.
3 I then call a procedure that generates a block_id for each row, I use a table to store this block_id. (temptable)

THIS ALL WORKS FINE.

4 But the problem is that I want to update the unload_details_ tab with the values from temptable. The after update trigger generates a self-deadlock error.
I also can't use a beofre update either becasue I need all of the rows with the same trans_id to generate the block_id. Also, you might think that this leads to a recursive trigger firing itself, but I check to see if the block_id is empty in the first place, this way if it is filled the trigger doesn't loop again. But for this to work the table needs to get updated with the values in the first place and this doesn't happen. I believe that the after update trigger locks the unload_details_ tab, so i can't update it again. I'm not 100% shure.


short:

After an update, I create a blok_id that needs to be updated to the same rows that got updated in the first place, in the same table ofcourse.

I'm at my wits end, and don't see a clear sollution for this problem, please help any way you can.

Thanks in advance.

[DreamersDelight]

The second update is also a part of the trigger, and no, no other transaction locks the table. If I run everything seperatly in an sql querie window it works but when I want to do it from within a trigger, it self-deadlocks. Here is some code to illustrate what I mean.

CREATE OR REPLACE TRIGGER block_id_trigger AFTER UPDATE ON unload_details_tab
 
DECLARE
 
current_transport_id NUMBER;
check_value NUMBER;
check_block_id NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN
 
current_transport_id := get_curent_trans_id;
 
check_value := check_site_and_directive(current_transport_id);
check_block_id := check_for_block_id(current_transport_id);
 
IF check_value = 1 AND check_block_id = 0 THEN
 
   create_blok_id_table(current_transport_id);
   UPDATE unload_details_tab a SET a.block_id = (SELECT DISTINCT b.blok_id FROM temptable b WHERE b.mark = a.mark_1) WHERE a.transport_id = current_transport_id;
 
ELSE
 
null;
 
END IF;
 
END block_id_trigger;

The underlined line is where the problem is, if I leave it out everything works fine, but the whole idea behind this is to update the unload_details_ tab with the block_id values.

[amitpatel66]

The second update is also a part of the trigger, and no, no other transaction locks the table. If I run everything seperatly in an sql querie window it works but when I want to do it from within a trigger, it self-deadlocks.

That should be becuase the COMMIT/ROLLBACK command is not called that will release all the objects that a particular transaction holds.

[DreamersDelight]

And this could be because ? I'm still in the trigger ? How can I rewrite my code so that I can still do the update correctly?

[amitpatel66]

And this could be because ? I'm still in the trigger ? How can I rewrite my code so that I can still do the update correctly?

This is not a good advisable way, but here it is:

Make the trigger autonomous and COMMIT the job before doing an second update. Autonomous triggers allow you to DO A COMMIT/ROLLBACK but this is not the way to make it work in oracle.

[DreamersDelight]

I have allready tried this, I think, check out my code paste in the above post. It still gives me the self-deadlock error.

Thanks again for the fast replies and helping me out on the issue.

[amitpatel66]

I have allready tried this, I think, check out my code paste in the above post. It still gives me the self-deadlock error.

Thanks again for the fast replies and helping me out on the issue.

Does your trigger not mutating?

[DreamersDelight]

The error I get is

I'm not shure what you mean by that.

[amitpatel66]

The error I get is

I'm not shure what you mean by that.

Could you please check the following and post back the results:

 
select object_name,status from all_objects where UPPER(object_name) IN ('your triggername in CAPS','your procedure name in caps','your table name in caps','any other object that you make use of in your coe in caps');
 
 

[DreamersDelight]

Everything returns valid.

[amitpatel66]

Everything returns valid.

Try one thing, just comment out the procedure call and then fire the trigger. Lets see if this is because a procedure is performing a DML that is called from a trigger.