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.
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
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.