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
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