473,836 Members | 1,419 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

17 New Member
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
22 6677
DreamersDelight
17 New Member
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 Recognized Expert Top Contributor
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
DreamersDelight
17 New Member
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 Recognized Expert Top Contributor
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
DreamersDelight
17 New Member
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 Recognized Expert Top Contributor
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
DreamersDelight
17 New Member
The error I get is

I'm not shure what you mean by that.
Mar 17 '08 #17
amitpatel66
2,367 Recognized Expert Top Contributor
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
DreamersDelight
17 New Member
Everything returns valid.
Mar 17 '08 #19
amitpatel66
2,367 Recognized Expert Top Contributor
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

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

Similar topics

1
6180
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the trigger and a not very complex select statement inside the if followed by an insert to the audit table. When I leave only a few IF-s in the...
1
15431
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
3
7286
by: takilroy | last post by:
Hi, Does anyone know of a simple way to do this? I want to create an insert trigger for a table and if the record already exists based on some criteria, I want to update the table with the values that are passed in via the insert trigger without having to use all the 'set' statements for each field (so if we add fields in the future I won't have to update the trigger). In other words, I want the trigger code to look something like...
2
1420
by: JA | last post by:
Hi, Newbie here. I have a mailing list program that I really like. I also have a new membership program. The membership program has mailing list signups built-in, but it isn't nearly as robust as the stand-alone mail program. If someone signs up for a newsletter when they register (membership program), can a "trigger" in SQL Server put the same information into the stand-alone program's tables? And when they remove or update their...
25
4080
by: Neo Geshel | last post by:
This works: <form> <asp:TextBox id="name" /> <%= name.ClientID %> </form> But this DOES NOT work: <form>
5
5364
by: wpellett | last post by:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table schema1.emp ( fname varchar(15) not null, lname varchar(15) not null, dob date,
1
6216
by: abhi81 | last post by:
Hello All, I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp. Insert and Update trigger work fine when i have only one of them defined. However when I have all the 3 triggers in place and when i try to fire a insert query on the statement. It triggers both insert and update trigger at the same time and...
1
4066
by: adithi | last post by:
My Table Structure is: Table A Table B Table C colA -PK Col B-PK Col C-PK Col B-FK Col B-FK Col C-FK This relation establish a Concurrent relation where in Cascade Property fails.I can set Cascade property for any two tabnles...but not the third table. My requirement is :
13
4179
by: Neil | last post by:
I'm running an update query in SQL 7 from QA, and it runs forever. Has been running for 20 minutes so far! The query is quite simple: update a single field in a table, based on a join with another table. There are a total of 1200 rows that are affected (out of a total of 60,000 in the table). Should not take 20 minutes! Also, retrieving the 1200 rows using the same criteria is instantaneous. Anyone have any idea what's going on? Here's...
0
9816
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9668
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10546
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10588
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9371
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4013
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3112
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.