SQL Server: триггер INSTEAD OF UPDATE: динамическая генерация кода; вставленная таблица недоступна

Question

У меня есть следующее с INSTEAD OF UPDATE триггер, который динамически генерирует код и затем выполняет его. Проблема в том, что при выполнении кода я всегда получаю сообщение об ошибке:

вставлено неверное имя объекта

Код триггера выглядит так:

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

Динамический код выглядит так:

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;

Подсказка:

Если я статически вводю динамически сгенерированный код в триггер, он работает нормально. так что сам код в порядке.

Вопрос: почему inserted недоступен в этой ситуации? И как это исправить?

Answer 1

Когда динамический sql выполняется, он имеет собственную область видимости, такую ​​как вызов другой хранимой процедуры, поэтому он не может ссылаться на то, что находится в области действия триггера, например на вставленные, удаленные или локальные переменные и т. Д. Вам нужно будет включить буквальное значение в строку динамического sql. для требуемого значения ключа, а также быть готовым обработать более 1 строки во вставленном, что вызвало срабатывание триггера.

Answer 2

Из того, что я обнаружил, похоже, что у вас, скорее всего, нет доступа к inserted таблицы в динамическом SQL таким образом, но вы должны быть в состоянии выполнить то, что вы пытаетесь сделать, используя SQLCLR

Использование вставленных / удаленных таблиц в Dynamic SQL

Answer 3

Поскольку приведенные выше ответы не дали мне желаемого решения, я продолжил поиск и нашел следующее - классное - решение (благодаря следующей статье: https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11318):

Если вам нужно передать вставленную таблицу в динамический код SQL (в хранимую процедуру sp_executesql), выполните:

select * from inserted into #inserted

и затем вы можете получить к нему доступ сразу в sp_executesql под #inserted. Нет необходимости передавать это как параметр в sp_executesql.

окончательный рабочий код выглядит следующим образом:

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