trigger: need to use inserted in sp_executesql

[stt_d31]

i have a trigger, so need to use the "inserted" table that comes in.

however, i need to use this "inserted" in the following way:


EXEC @LRES = sp_executesql N'
INSERT INTO newtable (col1,col2)
select * from (select acol1,acol2 from inserted WITH (NOLOCK))

however i keep on getting the error that he doesn't know the object "inserted".... I i have the feeling that i may not use inserted at that stage.

what now?

i really need to use the sp_executesql result in the @LRES since after it we should perform some actions in other tables, depending on the result of the @LRES. anyway i really hope anyone can help me as soon as possible.

[skhanal]

Inserted table is only visible within the triggers context, when you use sp_executesql it runs on a separate context so you can't use it there.

You will have to copy the contents of inserted into a temp table and access that

[stt_d31]

isn't it possible to create a variable or something that stores the content of the inserted table (instead of copying everything into a temporary table and drop it afterwards)?

[rmiao]

You can try with table variable.

[skhanal]

Table variable is just another variable which is only available to current context only.