472,364 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,364 software developers and data experts.

Dynamic SQL in an INSTEAD OF trigger causes recursion

Brad Orders
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_SCHEMA.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_nestlevel(), 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!

Jul 14 '09 #1
0 2015

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

Similar topics

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...
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...
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...
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...
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...
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...
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...
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...
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
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

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.