469,306 Members | 1,587 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

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

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.
Mar 17 '08 #1
22 6181
amitpatel66
2,367 Expert 2GB
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?
Mar 17 '08 #2
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...
Mar 17 '08 #3
amitpatel66
2,367 Expert 2GB
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?
Mar 17 '08 #4
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.
Mar 17 '08 #5
amitpatel66
2,367 Expert 2GB
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??
Mar 17 '08 #6
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.
Mar 17 '08 #7
amitpatel66
2,367 Expert 2GB
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?
Mar 17 '08 #8
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.
Mar 17 '08 #9
amitpatel66
2,367 Expert 2GB
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?
Mar 17 '08 #10
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.




Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE TRIGGER block_id_trigger AFTER UPDATE ON unload_details_tab
  2.  
  3. DECLARE
  4.  
  5. current_transport_id NUMBER;
  6. check_value NUMBER;
  7. check_block_id NUMBER;
  8. PRAGMA AUTONOMOUS_TRANSACTION;
  9.  
  10. BEGIN
  11.  
  12. current_transport_id := get_curent_trans_id;
  13.  
  14. check_value := check_site_and_directive(current_transport_id);
  15. check_block_id := check_for_block_id(current_transport_id);
  16.  
  17. IF check_value = 1 AND check_block_id = 0 THEN
  18.  
  19.    create_blok_id_table(current_transport_id);
  20.    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;
  21.  
  22. ELSE
  23.  
  24. null;
  25.  
  26. END IF;
  27.  
  28. 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.
Mar 17 '08 #11
amitpatel66
2,367 Expert 2GB
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.
Mar 17 '08 #12
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?
Mar 17 '08 #13
amitpatel66
2,367 Expert 2GB
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.
Mar 17 '08 #14
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.
Mar 17 '08 #15
amitpatel66
2,367 Expert 2GB
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?
Mar 17 '08 #16
The error I get is

I'm not shure what you mean by that.
Mar 17 '08 #17
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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');
  3.  
  4.  
Mar 17 '08 #18
Everything returns valid.
Mar 17 '08 #19
amitpatel66
2,367 Expert 2GB
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.
Mar 18 '08 #20
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.
Mar 18 '08 #21
amitpatel66
2,367 Expert 2GB
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?
Mar 18 '08 #22
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.
Mar 18 '08 #23

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by takilroy | last post: by
25 posts views Thread by Neo Geshel | last post: by
13 posts views Thread by Neil | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.