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);
   [U][B]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;[/B][/U]
   
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.