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!


Brad