Erro de sql dinâmico: 'CREATE TRIGGER' deve ser a primeira instrução em um lote de consulta
Como parte de algumas tarefas administrativas, temos muitas tabelas que precisam de um gatilho criado. O gatilho definirá um sinalizador e a data no banco de dados de Auditoria quando um objeto tiver sido modificado. Para simplificar, tenho uma tabela com todos os objetos que precisam de gatilhos criados.
Eu estou tentando gerar alguns sql dinâmico para fazer isso para cada objeto, mas estou recebendo este erro:'CREATE TRIGGER' must be the first statement in a query batch.
Aqui está o código para gerar o sql.
<code>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 </code>
Se eu usarPRINT
e cole o código em uma nova janela de consulta, o código é executado sem nenhum problema.
Eu removi oGO
declarações como esta também estava dando erros.
o que estou perdendo?
Por que estou recebendo um erro usandoEXEC(@sql);
ou até mesmoEXEC sp_executesql @sql;
?
Isso é algo a ver com o contexto dentroEXEC()
?
Muito obrigado por qualquer ajuda.