В рамках некоторых административных задач у нас есть много таблиц, для каждой из которых необходимо создать триггер. Триггер устанавливает флаг и дату в базе данных аудита, когда объект был изменен. Для простоты у меня есть таблица со всеми созданными объектами, для которых нужны триггеры.
Я пытаюсь сгенерировать динамический sql, чтобы сделать это для каждого объекта, но получаю следующую ошибку:'CREATE TRIGGER' must be the first statement in a query batch.
Вот код для генерации sql.
CREATE PROCEDURE [spCreateTableTriggers]
AS
BEGIN
DECLARE @dbname varchar(50),
@schemaname varchar(50),
@objname varchar(150),
@objtype varchar(150),
@sql nvarchar(max),
@CRLF varchar(2)
SET @CRLF = CHAR(13) + CHAR(10);
DECLARE ObjectCursor CURSOR FOR
SELECT DatabaseName,SchemaName,ObjectName
FROM Audit.dbo.ObjectUpdates;
SET NOCOUNT ON;
OPEN ObjectCursor ;
FETCH NEXT FROM ObjectCursor
INTO @dbname,@schemaname,@objname;
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = N'USE '+QUOTENAME(@dbname)+'; '
SET @sql = @sql + N'IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'''+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates]'')) '
SET @sql = @sql + N'BEGIN DROP TRIGGER '+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates]; END; '+@CRLF
SET @sql = @sql + N'CREATE TRIGGER '+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates] '+@CRLF
SET @sql = @sql + N' ON '+QUOTENAME(@schemaname)+'.['+@objname+'] '+@CRLF
SET @sql = @sql + N' AFTER INSERT,DELETE,UPDATE'+@CRLF
SET @sql = @sql + N'AS '+@CRLF
SET @sql = @sql + N'IF EXISTS(SELECT * FROM Audit.dbo.ObjectUpdates WHERE DatabaseName = '''+@dbname+''' AND ObjectName = '''+@objname+''' AND RequiresUpdate=0'+@CRLF
SET @sql = @sql + N'BEGIN'+@CRLF
SET @sql = @sql + N' SET NOCOUNT ON;'+@CRLF
SET @sql = @sql + N' UPDATE Audit.dbo.ObjectUpdates'+@CRLF
SET @sql = @sql + N' SET RequiresUpdate = 1'+@CRLF
SET @sql = @sql + N' WHERE DatabaseName = '''+@dbname+''' '+@CRLF
SET @sql = @sql + N' AND ObjectName = '''+@objname+''' '+@CRLF
SET @sql = @sql + N'END' +@CRLF
SET @sql = @sql + N'ELSE' +@CRLF
SET @sql = @sql + N'BEGIN' +@CRLF
SET @sql = @sql + N' SET NOCOUNT ON;' +@CRLF
SET @sql = @sql + @CRLF
SET @sql = @sql + N' -- Update ''SourceLastUpdated'' date.'+@CRLF
SET @sql = @sql + N' UPDATE Audit.dbo.ObjectUpdates'+@CRLF
SET @sql = @sql + N' SET SourceLastUpdated = GETDATE() '+@CRLF
SET @sql = @sql + N' WHERE DatabaseName = '''+@dbname+''' '+@CRLF
SET @sql = @sql + N' AND ObjectName = '''+@objname+''' '+@CRLF
SET @sql = @sql + N'END; '+@CRLF
--PRINT(@sql);
EXEC sp_executesql @sql;
FETCH NEXT FROM ObjectCursor
INTO @dbname,@schemaname,@objname;
END
CLOSE ObjectCursor ;
DEALLOCATE ObjectCursor ;
END
Если я использую PRINT
и вставляю код в новое окно запроса, код выполняется без проблем.
Я удалил операторы GO
, так как это также приводило к ошибкам.
Что я упускаю?
Почему я получаю сообщение об ошибке при использовании EXEC(@sql);
или даже EXEC sp_executesql @sql;
?
Это как-то связано с контекстом внутри EXEC()
?
Большое спасибо за любую помощь.