By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Dynamic SQL in an INSTEAD OF trigger causes recursion

Brad Orders
P: 21
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!


Brad
Jul 14 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

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