Programmation des déclencheurs DML

Lors de la création d'un déclencheur DML, vous pouvez insérer pratiquement toutes les instructions Transact-SQL utilisables dans un lot d'instructions, à l'exception de celles indiquées ci-dessous :

Important :
Les instructions LOAD DATABASE et LOAD LOG sont incluses dans SQL Server 2005 uniquement à des fins de compatibilité descendante et ne seront peut-être plus prises en charge dans les versions futures.

De plus, les instructions Transact-SQL suivantes ne peuvent pas être incluses dans le corps d'un déclencheur DML en cas d'utilisation sur une table ou une vue qui constitue la cible de l'action déclenchante :

Important :
Bien que cette restriction soit introduite dans SQL Server 2005, elle est également d'application lorsque le mode de compatibilité descendante est défini à 80.

Obscurcissement des définitions de déclencheurs DML

Pour obscurcir le texte original de l'instruction CREATE TRIGGER, utilisez l'option WITH ENCRYPTION. La sortie générée par l'obscurcissement n'est pas visible directement dans les tables ou les vues système de SQL Server 2005. Les utilisateurs n'ayant pas accès aux tables système, aux vues système ou aux fichiers de base de données ne peuvent pas récupérer le texte obscurci. Toutefois, le texte est disponible pour les utilisateurs privilégiés avec accès direct aux fichiers de base de données. Ces utilisateurs peuvent être en mesure d'effectuer une ingénierie à rebours de l'obscurcissement pour récupérer le texte original de la définition de déclencheur.

L'utilisation de l'argument WITH ENCRYPTION évite la publication du déclencheur dans le cadre de la réplication SQL Server.

Options de l'instruction SET

Lorsqu'une application ODBC se connecte à SQL Server, le serveur définit automatiquement les options suivantes pour la session :

• SET QUOTED_IDENTIFIER ON
• SET TEXTSIZE 2147483647
• SET ANSI_DEFAULTS ON
• SET CURSOR_CLOSE_ON_COMMIT OFF
• SET IMPLICIT_TRANSACTIONS OFF

Ces paramètres améliorent la portabilité des applications ODBC. Étant donné que les applications basées sur la bibliothèque DB-Library ne définissent généralement pas ces options, les déclencheurs doivent être testés avec les options SET indiquées ci-dessus, à la fois activées (ON) et désactivées (OFF). Cette opération garantit que les déclencheurs fonctionnent correctement, quelles que soient les options activées par une connexion déterminée lors de l'appel du déclencheur. Un déclencheur qui requiert un paramétrage particulier pour une de ces options devra commencer par une instruction SET. Cette instruction SET n'est active que pour l'exécution du déclencheur ; à la fin de celle-ci, la configuration d'origine est restaurée.

Test des modifications de colonnes spécifiques

La fonction UPDATE() peut être utilisée pour déterminer si une instruction INSERT ou UPDATE a modifié une colonne particulière de la table. Cette fonction renvoie la valeur TRUE si une valeur est attribuée à la colonne.

Remarque :
Étant donné que la valeur spécifique d'une colonne ne peut pas être supprimée par l'instruction DELETE, la clause IF UPDATE() ne s'applique pas à l'instruction DELETE.

Vous pouvez aussi utiliser la fonction COLUMNS_UPDATED pour vérifier quelles colonnes de la table ont été mises à jour par une instruction INSERT ou UPDATE. Cette fonction utilise un masque de bits (binaire) entier pour spécifier les colonnes à tester. Pour plus d'informations, consultez CREATE TRIGGER.

Exemples

A. Utilisation de la clause IF UPDATE() pour tester les modifications de données

L'exemple ci-dessous crée le déclencheur d'insertion (INSERT) my_trig sur la table my_table et vérifie si la colonne b a été modifiée par une instruction INSERT.

CREATE TABLE my_table*
(a int NULL, b int NULL)
GO

CREATE TRIGGER my_trig
ON my_table
FOR INSERT
AS
IF UPDATE(b)
   PRINT 'Column b Modified'
GO

B. Utilisation de la clause COLUMNS UPDATED pour tester les modifications de données

L'exemple suivant permet d'obtenir des résultats similaires avec la clause COLUMNS_UPDATED().

CREATE TRIGGER my_trig2
ON my_table
FOR INSERT
AS
IF ( COLUMNS_UPDATED() & 2 = 2 )
   PRINT 'Column b Modified'
GO

Résolution de noms différée

Les déclencheurs DML peuvent faire référence à une table qui n'existait pas au moment où le déclencheur a été créé. Cette fonction s'appelle la résolution de noms différée. Pour plus d'informations sur la résolution de noms différée, consultez Résolution de noms différée et compilation.

Remarque :

Si un objet référencé par un déclencheur DML est supprimé ou renommé, une erreur est renvoyée lors de l'exécution du déclencheur. Cependant, si un objet référencé dans un déclencheur DML est remplacé par un objet du même nom, le déclencheur s'exécute sans qu'il soit nécessaire de le recréer. Par exemple, si le déclencheur trig1 référence la table test1, que celle-ci est supprimée et qu'une autre table appelée test1 est créée, trig1 référence la nouvelle table.

Renvoi de résultats

Il est recommandé qu'un déclencheur DML ne renvoie aucun résultat. En effet, le traitement particulier des résultats qui seraient renvoyés doit être écrit dans chacune des applications permettant d'apporter des modifications à la table du déclencheur. Pour éviter qu'un déclencheur DML renvoie des résultats, n'incluez pas dans sa définition des instructions SELECT ni des affectations de variables. Si une affectation de variable doit intervenir dans un déclencheur, utilisez l'instruction SET NOCOUNT avant le déclencheur pour empêcher le renvoi d'un ensemble de résultats.

Important :

La possibilité de renvoyer des ensembles de résultats à partir de déclencheurs sera supprimée dans une version future de SQL Server. Évitez d'intégrer des déclencheurs qui renvoient des ensembles de résultats dans le développement de nouvelles applications et prévoyez de modifier celles qui le font actuellement. Pour empêcher les déclencheurs de renvoyer des ensembles de résultats dans SQL Server 2005, attribuez à l'Option disallow results from triggers la valeur 1. La valeur par défaut de cette option sera 1 dans une version future de SQL Server.

Voir aussi

Autres ressources

CREATE TRIGGER (Transact-SQL)
SELECT (Transact-SQL)
SET (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications