error de sql dinámico: 'CREATE TRIGGER' debe ser la primera declaración en un lote de consultas
Como parte de algunas tareas administrativas, tenemos muchas tablas en las que cada una necesita un desencadenante creado. El activador establecerá una marca y la fecha en la base de datos de auditoría cuando se haya modificado un objeto. Para simplificar, tengo una tabla con todos los objetos que necesitan crear disparadores.
Estoy tratando de generar algunos sql dinámicos para hacer esto para cada objeto, pero recibo este error:'CREATE TRIGGER' must be the first statement in a query batch.
Aquí está el código para generar el 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>
Si yo usoPRINT
y pegue el código en una nueva ventana de consulta, el código se ejecuta sin ningún problema.
He quitado elGO
Declaraciones ya que esto también estaba dando errores.
¿Qué me estoy perdiendo?
¿Por qué me da un error usandoEXEC(@sql);
o inclusoEXEC sp_executesql @sql;
?
¿Es esto algo que ver con el contexto dentro deEXEC()
?
Muchas gracias por cualquier ayuda.