473,595 Members | 2,589 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Dynamic SQL in an INSTEAD OF trigger causes recursion

Brad Orders
21 New Member
Hi all

Here is my situation:

When table A is updated, I need to record some data in table B, then apply the update to table A

Normally I would use a FOR UPDATE trigger, but the table has a TEXT field in it. Therefore, I must use an INSTEAD OF trigger.

Columns may be added to this table without my knowledge or control, and without warning.

Here is the approach I took inside the INSTEAD OF trigger:
- The extra information is recorded in table B
- I created Dynamic SQL using the INFORMATION_SCH EMA.COLUMNS table, so I could catch when the table columns change in table A
- I then used EXEC to perform the original UPDATE on table A

This gives me a recursion error on the trigger.

I then tried testing for trigger_nestlev el(), to prevent the trigger firing recursively. This didn't work, because the EXEC is running in a different context (ie. "outside" the trigger)

I then tried EXEC (@UpdateString) AS USER = 'TriggerUser', and tested for IF SYSTEM_USER <> 'TriggerUser' at the start of the trigger. No luck, it still gave me the recursion error.

Does anyone have any ideas on this annoying problem?

I am happy to post the script I am using, if it will help.

A big "thanks" for your help!


Brad
Jul 14 '09 #1
0 2081

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

Similar topics

6
6543
by: Scott CM | last post by:
I have a multi-part question regarding trigger performance. First of all, is there performance gain from issuing the following within a trigger: SELECT PrimaryKeyColumn FROM INSERTED opposed to: SELECT * FROM INSERTED
6
7134
by: Mary | last post by:
We are developing a DB2 V7 z/OS application which uses a "trigger" table containing numerous triggers - each of which is activated by an UPDATE to a different column of this "trigger" table. When the triggers are fired, various other operations are performed on other tables in the database. The triggers are not created on these other tables because other programs perform updates to these tables and we do not want the triggers to fire...
2
6987
by: Jack | last post by:
We are have a question about the no cascade option on before triggers. The description stays that no other triggers will be fired by the changes of a before trigger. One of our developers is seeing results that would imply that other after triggers are being fired by the results of a before trigger. Is this possible? Is there some little documented behavior going on here? thanks
3
6816
by: MikeY | last post by:
Hi Everyone, I am working in C#, windows forms.My question is this. All my button dynamic controls properties are present and accounted for except for the"FlatStyle" properties. I can't seem to figure out, if there is a way of using polymorphic way (if that is a word) of doing this particular property. A sample of my code is as follows: DynamicControls.ButtonControl(this,btnSearchByName, new Point(5, 75), new Size(95, 20),...
2
2036
by: Pascal Polleunus | last post by:
(another try with a different subject as it doesn't seem to work with "EXECUTE + transaction = unexpected error -8" :-/) Hi, It seems that there is a problem when executing a dynamic commands containing a transaction... Here's a simple example...
24
2383
by: Sillaba atona | last post by:
I use this code to read dynamic string: char *s1; ....... puts("Inserire una stringa: "); while((*s1++=getchar())!='\n'); *s1='\0'; The compilation (ANSI C) is OK but I receive an error during the execution.
23
7389
by: sandy | last post by:
I need (okay, I want) to make a dynamic array of my class 'Directory', within my class Directory (Can you already smell disaster?) Each Directory can have subdirectories so I thought to put these in an array. The application compiles but aborts without giving me any useful information. What I suspect is happening is infinite recursion. Each Directory object creates an array of Subdirectories each of which has an array of...
1
1985
by: martinghale | last post by:
Hello, I have to create an AFTER UPDATE trigger that needs to perform an UPDATE on the same table. I understand this is a recursive situation and I'm wondering if there is a way to NOT go through all 16 levels of recursion. Ideally the UPDATE performed by the trigger would not cause the trigger to fire again, although that is what I'm expecting. If anyone has experience with creating AFTER UPDATE triggers that perform an UPDATE on...
26
2796
by: Aaron \Castironpi\ Brady | last post by:
Hello all, To me, this is a somewhat unintuitive behavior. I want to discuss the parts of it I don't understand. .... f= lambda: n .... 9 9
0
7954
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
7882
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
8260
Oralloy
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...
0
8379
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...
0
6671
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...
0
5416
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3872
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...
1
2391
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1490
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.