SQL Server : INSTEAD OF UPDATE trigger: dynamic code generation; inserted table not available

Question

I have the following with an INSTEAD OF UPDATE trigger which dynamically generates code and then executes it. The problem is that when the code is executed I always get the error message:

invalid object name 'inserted'

The trigger code is like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Trigger [dbo].[TACMasterLayouts_VersionVisibilityHandling_updates] 
ON [dbo].[TACMasterLayouts_VersionVisibilityHandling] 
INSTEAD OF UPDATE
AS
    IF @@rowcount = 0 
        RETURN; 

    SET NOCOUNT ON

    DECLARE @tableToUpdate AS NVARCHAR(MAX) = 'dbo.tacmasterlayouts';
    DECLARE @UpdateStatement AS NVARCHAR(MAX) 
    DECLARE @IdentityField AS NVARCHAR(MAX) = (SELECT Name 
                                               FROM sys.columns 
                                               WHERE [object_id] = OBJECT_ID('dbo.tacmasterlayouts') 
                                                  AND is_identity = 1)

    SET @UpdateStatement = 'update ' + @tableToUpdate + ' set ';

    DECLARE @Fields AS NVARCHAR(MAX)

    SELECT @Fields = COALESCE(@Fields + ', ', '') + @tableToUpdate + '.' + Name + ' = inserted.' + Name 
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID('dbo.tacmasterlayouts') 
      AND Name != @IdentityField;

    SET @UpdateStatement = @UpdateStatement + @fields

    SET @UpdateStatement = @UpdateStatement + ' FROM ' + @tableToUpdate + ', inserted'
    SET @UpdateStatement = @UpdateStatement + ' where ' + @tableToUpdate + '.' + @IdentityField + ' = inserted.' + @IdentityField

    EXECUTE sp_executesql @UpdateStatement

The dynamic code looks like this:

UPDATE TACMasterLayouts SET

 TACMasterLayouts.TACMasterLayoutName = inserted.TACMasterLayoutName, 
 TACMasterLayouts.TACMasterLayoutDestinationFileName = inserted.TACMasterLayoutDestinationFileName, 
 TACMasterLayouts.LayoutTypeCategoryId = inserted.LayoutTypeCategoryId, 
 TACMasterLayouts.LayoutTypeId = inserted.LayoutTypeId, 
 TACMasterLayouts.TACMasterLayoutDescription = inserted.TACMasterLayoutDescription, 
 TACMasterLayouts.TACMasterLayoutJasper = inserted.TACMasterLayoutJasper, 
 TACMasterLayouts.TACMasterLayoutJrxml = inserted.TACMasterLayoutJrxml, 
 TACMasterLayouts.TACMasterLayoutHtml = inserted.TACMasterLayoutHtml, 
 TACMasterLayouts.TACMasterLayoutDontClone = inserted.TACMasterLayoutDontClone, 
 TACMasterLayouts.TACMasterLayoutIsSubReport = inserted.TACMasterLayoutIsSubReport, 
 TACMasterLayouts.TACMasterLayoutVersion = inserted.TACMasterLayoutVersion, 
 TACMasterLayouts.TACMasterLayoutKey = inserted.TACMasterLayoutKey, 
 TACMasterLayouts.TACMasterLayoutTimestampLastModified = inserted.TACMasterLayoutTimestampLastModified, 
 TACMasterLayouts.TACMasterLayoutHash = inserted.TACMasterLayoutHash, 
 TACMasterLayouts.TACMasterLayoutTimestampLastCheckout = inserted.TACMasterLayoutTimestampLastCheckout,
 TACMasterLayouts.TACMasterLayoutCheckedOutByUserId = inserted.TACMasterLayoutCheckedOutByUserId,
 TACMasterLayouts.TACMasterLayoutIsCheckedIn = inserted.TACMasterLayoutIsCheckedIn, 
 TACMasterLayouts.TACMasterLayoutCheckedInByUserId = inserted.TACMasterLayoutCheckedInByUserId, 
 TACMasterLayouts.TACMasterLayoutCheckedOutFolderName = inserted.TACMasterLayoutCheckedOutFolderName 

FROM TACMasterLayouts, inserted
WHERE TACMAsterlayouts.TACMasterLayoutId = inserted.TACMasterLayoutId;

Hint:

If I am statically entering the dynamically generated code within the trigger, it works fine. so the code itself is ok.

Question: why is inserted not available in this situation? And how to fix it?

Be carful to `where d.TACMasterLayoutId = (select TACMasterLayoutId from inserted)` it may return many rows. — Ilyes, Oct 11 '18 at 17:42
Possible duplicate of [SQL Server trigger: Invalid object name 'inserted' in case sensitive database](https://stackoverflow.com/questions/40464905/sql-server-trigger-invalid-object-name-inserted-in-case-sensitive-database) — Ilyes, Oct 11 '18 at 17:51
@sami: No - this is not the same issue because when the dynamically generated code is typed statically within this trigger it works fine. — John Ranger, Oct 11 '18 at 18:13
A better idea is to *use dynamic sql to write your trigger* instead of using it to execute your trigger. — RBarryYoung, Aug 18 '21 at 17:17

score 2 · Answer 1 · answered Oct 11 '18 at 17:54

2

When dynamic sql is executed it has it's own scope like calling another stored procedure, so it can not reference what is in the Trigger's scope like inserted, deleted, or local variables etc. You would have to include a literal value in the dynamic sql string for the required key value and also be prepared to handle more than 1 row in inserted which caused the firing of the trigger.

answered Oct 11 '18 at 17:54

jabeyer0324

21
3

As I am no SQL expert (not yet!) - Is it possible to pass the "inserted" table into sp_executesql? I have to dynamically generate the SQL code as the tables and views could change the amount and name of fields any time - I have many tables which would need this - and I don't want to update (run after) the instead of triggers' code when the underlying tables/views are modified. – John Ranger Oct 11 '18 at 18:11

mr_nobody · Answer 2 · 2018-10-11T20:11:41.480

1

From what I'm finding, it appears that you likely don't have access to use the inserted table in dynamic SQL in this way, but you should be able to accomplish what you're trying to do using the SQLCLR

Using the inserted / deleted tables in Dynamic SQL

edited Oct 11 '18 at 20:11

answered Oct 11 '18 at 17:50

mr_nobody

89
1
7

I am here within plain SQL - no C#. – John Ranger Oct 11 '18 at 18:06
sorry - no I didn't because of the C# part. And yes - the linked article also uses #xxx to pass inserted/deleted to the sp_executesql. Therefore I give you a +1 to nullify the one which voted you down :-) Maybe you can edit your answer and remove the C# part to clarify what you mean. – John Ranger Oct 11 '18 at 19:55

John Ranger · Answer 3 · 2018-10-11T19:51:57.457

As the above answers didn't gave me the desired solution, I digged further and found the following - cool - solution (thanks to the following article: https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11318):

If you need to pass the inserted table into dynamic SQL code (to the sp_executesql stored procedure), do a:

select * from inserted into #inserted

and then you can access it immediately within the sp_executesql under #inserted. No need to pass this as a parameter to the sp_executesql.

the final - working - code looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[TACMasterLayouts_VersionVisibilityHandling_updates] on [dbo].[TACMasterLayouts_VersionVisibilityHandling] INSTEAD OF update  
as 
 if @@rowcount = 0 return; 
 SET NOCOUNT ON

 Declare @tableToUpdate AS Nvarchar(max) = 'dbo.tacmasterlayouts';
 Declare @UpdateStatement AS Nvarchar(max)
 Declare @IdentityField as Nvarchar(max) = (SELECT Name FROM sys.columns WHERE [object_id] = OBJECT_ID(@tableToUpdate) and is_identity = 1)

 set @UpdateStatement = 'update ' + @tableToUpdate + ' set ';

 Declare @Fields AS Nvarchar(MAX)
 SELECT  @Fields = COALESCE(@Fields + ', ', '') + @tableToUpdate + '.' + Name + ' = #inserted.' + Name FROM sys.columns WHERE [object_id] = OBJECT_ID(@tableToUpdate) and Name != @IdentityField;

 set @UpdateStatement = @UpdateStatement + @fields

 set @UpdateStatement = @UpdateStatement + ' FROM ' + @tableToUpdate + ', #inserted'
 set @UpdateStatement = @UpdateStatement + ' where ' + @tableToUpdate + '.' + @IdentityField + ' = #inserted.' + @IdentityField

 select * into #inserted from inserted 

 EXECUTE sp_executesql @UpdateStatement

SQL Server : INSTEAD OF UPDATE trigger: dynamic code generation; inserted table not available

3 Answers3