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.
22  6655 
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?
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...
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?
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.
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??
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.
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?
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.
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?
Sign in to post your reply or Sign up for a free account.
Similar topics | |
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...
|
by: shottarum |
last post by:
I currently have 2 tables as follows:
CREATE TABLE .
(
mhan8 int,
mhac02 varchar(5),
mhmot varchar(5),
mhupmj int
)
|
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...
|
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...
|
by: Neo Geshel |
last post by:
This works:
<form>
<asp:TextBox id="name" />
<%= name.ClientID %>
</form>
But this DOES NOT work:
<form>
| | |
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,
|
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...
|
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 :
|
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...
|
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
