sp_executesql from trigger to run dynamic select against 'inserted' table

Question

question

ILA asked Apr 14, '10

sp_executesql from trigger to run dynamic select against 'inserted' table

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!

t-sql dynamic

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-14T20:30:57Z

Grant Fritchey answered Apr 14, '10

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.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ILA commented · Apr 14, 2010 at 09:35 PM

"You don't want to move the inserted data outside the trigger... do as little as possible within a trigger." I think you say this for performance reasons, right? Are there other reasons NOT to do this (or that it can't be done?) It's not to capture the ID value. I'm writing generalized code to be able to go through all columns of any table and then play with each of them. http://www.sqlservercentral.com/scripts/Miscellaneous/65769/ has some base code that helped, but I need to go beyond this. Can sp_execsql in the trigger to access inserted work, even if slow?

0 ·

Grant Fritchey ♦♦ commented · Apr 15, 2010 at 08:22 AM

I'm still not understanding the need. You want to go through all columns... Do you mean you're trying to capture the changes? Maybe you want to be using Change Data Capture (SQL Server 2008 only)? No, I'm pretty sure that sp_executesql won't work with the inserted table because it runs under the context of your connection, not the internal context of the trigger. You can't see the inserted table directly, so sp_executesql can't either. That sample code is a method for searching meta-data for specific strings. I don't quite understand how it relates to what you're trying to do.

0 ·

Answer 2 · 2010-05-14T21:39:10Z

Jeff Moden answered May 14, '10

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question