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.

[amitpatel66]

What is the reason behind you getting the block_id for each row and sotring in temp table and using the same to update the table from which you got this block id?

[DreamersDelight]

What is the reason behind you getting the block_id for each row and sotring in temp table and using the same to update the table from which you got this block id?

You missunderstood me i think, the block_id gets generated and that is what gets updated back. The block_id is not known at that time, it gets generated after the update of the rows with a trans_id. Ow, and thanks for the fast reply...

[amitpatel66]

You missunderstood me i think, the block_id gets generated and that is what gets updated back. The block_id is not known at that time, it gets generated after the update of the rows with a trans_id. Ow, and thanks for the fast reply...

The first update that you are doing is done manually is it?
so you do manual update, and COMMIT which triggers a AFTER UPDATE trigger on that table which calls another procedure to generate block_id?

[DreamersDelight]

Correct, the first update is done by hand through an erp-packet interface. Sadly I can't get at the code that does this, otherwise it would have been easy. I would just have called the procedure from within the erp-packet interface.

[amitpatel66]

Correct, the first update is done by hand through an erp-packet interface. Sadly I can't get at the code that does this, otherwise it would have been easy. I would just have called the procedure from within the erp-packet interface.

What about the remaining steps? Are they doing the same as I mentioned?
A trigger calling a procedure to generate block id??

[DreamersDelight]

What do you mean by remaining steps?

The procedure generates all the block_id's for the rows wich get updated and everything gets commited on time.

[amitpatel66]

What do you mean by remaining steps?

The procedure generates all the block_id's for the rows wich get updated and everything gets commited on time.

So the procedure is called manually which generated block_id and store in temp table?

After this, you run the update statement again to update the block_id from temp table to your main table?

[DreamersDelight]

Correct, I have written an update statement that should update the main table again, setting the correct block_id on that table for that particular row.

[amitpatel66]

Correct, I have written an update statement that should update the main table again, setting the correct block_id on that table for that particular row.

Ok, so when you run a second update, the problem occurs. But once you commit, or rollback, the table lock should get released. Is any other transaction using that table? while you ran second update?

[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.

[DreamersDelight]

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.

Ok, I did what you asked and commented out the update, the trigger fired fine, and the temptable was created succesfully.

[amitpatel66]

Ok, I did what you asked and commented out the update, the trigger fired fine, and the temptable was created succesfully.

So the call to a procedure from a trigger is causing a problem here right. Try this: Make the procedure an Autonomous by adding PRAGMA derivatives, and then try calling a procedure from a trigger and check if it works?

[DreamersDelight]

So the call to a procedure from a trigger is causing a problem here right. Try this: Make the procedure an Autonomous by adding PRAGMA derivatives, and then try calling a procedure from a trigger and check if it works?

No, I don't think that's the problem. I can call procedures fine, even when I put the update statement in a procedure, (currently located in the trigger itself) it will still deadlock.