Does anyone know how to use sp_executesql from a trigger to run a select on the inserted (and potentially deleted) tables?
I think I've got theID sent in properly as an output parm to sp_executesql, but the 'inserted' table causes an "invalid object name 'inserted'" message.
(the actual text of @theSQL looks like this now:
'Select @theID_OUT = i.mytablesIDcolumn from inserted i'
If I ran this select directly from the trigger, it works perfectly. But since the name of the column to select from will change during other uses, I can't run it directly.)
So, when this is run in a trigger, I get the error message, Msg 208, Level 16, State 1, Line 1 Invalid object name 'inserted'.
Do I need to take the inserted table and duplicate it as a (temporary) on-disk table that can be accessed from elsewhere in the database? Or send it in as another parameter to the sp_executesql proc? I hope not!
-- Many thanks!
asked Apr 14 '10 at 07:45 PM in Default
sp_executesql runs under the context of the user, so I think that changes the ability of the code running within the trigger to reference the inserted table. You don't want to move the inserted data outside the trigger. As a matter of fact, you want to do as little as possible within a trigger.
What is it that you're trying to do? Capture the generated identity value? There are other ways to do this, on a row-by-row basis you can use @@SCOPE_IDENTITY. If you need to capture it for sets of data, you can use the OUTPUT clause of the insert statement.
answered Apr 14 '10 at 08:30 PM
Grant Fritchey ♦♦
sp_ExecuteSQL will run the dynamic SQL under a different scope. I think it's a form of "Death by SQL" to do such a thing but if you really need to run dynamic SQL in a trigger, you must first insert the data from the INSERTED table into a Temp table within the trigger and go from there.
answered May 14 '10 at 09:39 PM