x

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!

more ▼

asked Apr 14 '10 at 07:45 PM in Default

ILA gravatar image

ILA
11 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Apr 14 '10 at 08:30 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

"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?
Apr 14 '10 at 09:35 PM ILA

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.
Apr 15 '10 at 08:22 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 14 '10 at 09:39 PM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x51

asked: Apr 14 '10 at 07:45 PM

Seen: 4554 times

Last Updated: Apr 14 '10 at 07:45 PM