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.
DECLARE @theID as int; DECLARE @theSql as nvarchar(4000); DECLARE @ParmDefinition nvarchar(500); SET @ParmDefinition = N'@theID_OUT int OUTPUT'; SET @theSQL = N'Select @theID_OUT = i.'+@theIDColumnName+' from inserted i'
(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.)
EXECUTE sp_executesql @theSQL, @ParmDefinition, @theID_OUT=@theID OUTPUT;
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!