dynamique de l'erreur sql: 'CREATE TRIGGER" doit être la première instruction dans un traitement de requêtes
Dans le cadre de certaines tâches administratives, nous avons un grand nombre de tables que chaque besoin d'un déclencheur créé. Le déclenchement de définir un indicateur et la date dans la base de données d'Audit lorsqu'un objet a été modifié. Pour des raisons de simplicité, j'ai un tableau avec tous les objets qu'il déclenche créé.
Je suis en train de générer le sql dynamique pour ce faire, pour chaque objet, mais j'obtiens cette erreur:
'CREATE TRIGGER' must be the first statement in a query batch.
Voici le code pour générer le 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 j'utilise PRINT
et collez le code pour une nouvelle fenêtre de requête, le code s'exécute sans problème.
J'ai enlevé la GO
états comme ce fut également donner des erreurs.
Ce qui me manque?
Pourquoi suis-je une erreur en utilisant les EXEC(@sql);
ou même EXEC sp_executesql @sql;
?
Est-ce quelque chose à voir avec le contexte à l'intérieur de EXEC()
?
Merci beaucoup pour toute aide.
OriginalL'auteur MarkusBee | 2012-04-26
Vous devez vous connecter pour publier un commentaire.
Si vous utilisez SSMS (ou autre outil similaire) pour exécuter le code produit par ce script, vous obtiendrez exactement la même erreur. Il pouvait courir tout droit lorsque vous avez inséré lot de séparateurs (
GO
), mais maintenant que vous ne le faites pas, vous serez confronté à la même question dans SSMS trop.D'autre part, la raison pour laquelle vous ne pouvez pas mettre
GO
dans votre dynamique de scripts est parce queGO
n'est pas une instruction SQL, c'est simplement un délimiteur reconnu par SSMS et quelques autres outils. Probablement vous êtes déjà au courant de cela.De toute façon, le point de
GO
est l'outil de savoir que le code devrait être divisé et ses parties exécuter séparément. Et que, séparément, est ce que vous devez faire dans votre code.Donc, vous avez les options suivantes:
insérer
EXEC sp_execute @sql
juste après la partie qui descend le déclencheur, puis réinitialiser la valeur de@sql
pour ensuite stocker et exécuter la définition de la partie à son tour;utiliser deux variables,
@sql1
et@sql2
, le magasin S'il EXISTE/DROP partie en@sql1
, la création de DÉCLENCHER un dans@sql2
, puis exécuter des scripts (encore une fois, séparément).Mais alors, comme vous l'avez déjà trouvé, vous devrez faire face à un autre problème: vous ne pouvez pas créer un déclencheur dans une autre base de données sans l'exécution de l'instruction dans le contexte de la base de données.
Maintenant, il y a 2 façons de fournir le contexte nécessaire:
1) utiliser un
USE
déclaration;2) exécuter l'instruction(s) d'une requête dynamique à l'aide de
EXEC targetdatabase..sp_executesql N'…'
.Évidemment, la première option ne fonctionnera pas ici: nous ne pouvons pas ajouter
USE …
avantCREATE TRIGGER
, parce que celui-ci doit être la seule instruction dans le lot.La deuxième option peut être utilisé, mais il faudra une couche supplémentaire de dynamicité (pas sûr si c'est un mot). C'est parce que le nom de base de données est un paramètre ici et nous avons donc besoin pour exécuter
EXEC targetdatabase..sp_executesql N'…'
comme un script dynamique, et depuis le script à exécuter est lui-même censé être un script dynamique, il va donc être imbriquées à deux reprises.Donc, avant de la (deuxième)
EXEC sp_executesql @sql;
ligne ajouter ce qui suit:Comme vous pouvez le voir, d'intégrer le contenu de
@sql
comme un ensemble dynamique de script correctement, ils doivent être entourés de guillemets simples. Pour la même raison, chaque guillemet simple dans@sql
doit être doublé (par exemple à l'aide de laREPLACE()
function, comme dans la déclaration ci-dessus).[EDIT expiré sur le commentaire précédent.] Merci beaucoup. J'ai diviser le code en deux "morceaux" comme vous le suggérez dans votre première option. La première partie s'exécute parfaitement. Je vais préciser que la procédure est exécutée à partir de l'Audit de la base de données et les objets qui nécessitent des déclencheurs dans d'autres bases de données. Excecuting la
CREATE TRIGGER
déclaration déclenche l'erreur suivant, même lors de l'utilisation de nom de table complet: "Impossible de créer le déclencheur sur [...] tant que la cible n'est pas dans la base de données actuelle." Est-il un moyen de contourner cela? Comment puis-je obtenir à exécuter dans le cadre d'une autre base de données? Merci.Veuillez voir ma mise à jour. Je ne sais pas si tout est clair comme je voudrais qu'il soit, n'hésitez donc pas à demander.
C'est excellent, parfaitement clair et c'est exactement ce que je recherchais. Le seul problème - j'ai vérifié dans le BOL - a avec
QUOTENAME
, où le paramètre de chaîne est limité à 128 caractères. Les entrées de plus de 128 caractères de retourNULL
, donc j'ai mis des guillemets autour d'elle au lieu d'utiliser la fonction. Je peux maintenant voir comment'EXEC'+@dbname+'..sp_executesql'
peut être extrêmement puissant et permettre l'exécution de code sql dynamique dans un autre contexte/base de données. Merci beaucoup encore pour votre aide.Ah, en effet, my bad. Vous pouvez remplacer la fonctionnalité de
QUOTENAME
avec quelque chose comme'''' + REPLACE(@sql, '''', '''''') + ''''
. (I. e. Je pense qu'il ne suffit pas d'enfermer@sql
entre guillemets, mais parce que vous avez les expressions de chaîne à l'intérieur de la définition du déclencheur, vous aurez également besoin de doubler chaque citation.)OriginalL'auteur Andriy M
un déclencheur de la création doit être fait sur son propre lot de l'exécution. Vous êtes à l'intérieur d'une procédure, afin que vous ne serez pas en mesure de créer.
Je vous suggérons d'ajouter @sql dans une table temporaire, puis une fois le proc est fini de produire toutes les déclarations, boucle de cette table temporaire pour les exécuter et créer les déclencheurs
OriginalL'auteur Diego