Hi everyone. Thanks in advance to anyone who might be able to shed some light on this situation.
I have a trigger in which the following SQL code exists.
SET @.tempInserted = N'SET @.dummy = (SELECT '+@.cftColumnName+' FROM INSERTED)'
EXEC sp_executeSQL @.tempInserted, N'@.dummy varchar(255) output', @.dummy=@.tempAddress output
When the trigger executes I receive the following error message...
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'INSERTED'.
My question is, is it possible to in some way reference the INSERTED and DELETED tables using sp_executeSQL?I was able to replicate your problem:
-- Set Option Value
-- -------- ----
-- textsize 64512
-- language us_english
-- dateformat mdy
-- datefirst 7
-- arithabort SET
-- nocount SET
-- remote_proc_transactions SET
-- ansi_null_dflt_on SET
-- ansi_warnings SET
-- ansi_padding SET
-- ansi_nulls SET
-- concat_null_yields_null SET
create table #Tmp(f1 int, f2 char(1))
go
create trigger TmpTrigger on Tmp
FOR DELETE, INSERT, UPDATE
AS
BEGIN
declare @.tempInserted nvarchar(100)
, @.cftColumnName nvarchar(100)
, @.tempAddress nvarchar(100)
set @.cftColumnName = 'f2'
SET @.tempInserted = N'SELECT @.dummy = ' + @.cftColumnName + ' FROM inserted'
SET @.tempInserted = N'select * From #Tmp'
EXEC sp_executeSQL @.tempInserted, N'@.dummy varchar(255) output', @.dummy=@.tempAddress output
select @.tempAddress
END
go
insert into #Tmp values(2,'B')
insert into Tmp values(1,'A')
select * From Tmp
go
drop table #Tmp
drop table Tmp
go
The only thing I can figure out is that since inserted and deleted are special temp tables they are not available to the new process created during the execution of sp_executeSQL.|||Thanks Paul. I ended up taking a different approach that essentially enabled me to achieve what I was attempting to do. Instead of directly referencing the INSERTED and DELETED tables I first create another set of temp tables to which I copy all the records from INSERTED and DELETED. I can then make a reference to these temporary tables during the execution of sp_executeSQL.
Not perfect but it works =).|||A workable solution is better than nothing working! You can always go back and change your code once everything runs end to end.
