Exemple de déclencheur SQL Server

Par : Daniel Farina | Mis à jour : 2019-02-26 | Commentaires (7) | Connexe : Plus > Déclencheurs

Problème

Vous avez déjà appris à écrire des requêtes SQL et des procédures stockées, mais vous voulez maintenant vous familiariser avec les déclencheurs de SQL Server. Cette astuce vous servira de point de départ et de guide pour créer des déclencheurs SQL Server.

Solution

Les déclencheurs sont l’un des sujets les plus incompris des personnes qui découvrent SQL Server.Peut-être est-ce dû au fait qu’ils permettent presque toutes les mêmes fonctionnalités que les procédures stockées, ce qui fait que le développeur inexpérimenté ne sait pas s’il doit créer une procédure stockée ou un déclencheur.

Qu’est-ce qu’un déclencheur SQL Server ?

Un déclencheur SQL Server est un morceau de code procédural, comme une procédure stockée qui n’est exécuté que lorsqu’un événement donné se produit. Il existe différents types d’événements qui peuvent déclencher un déclencheur. Pour n’en citer que quelques-uns, l’insertion de lignes dans une table, un changement dans la structure d’une table et même un utilisateur qui se connecte à une instance SQL Server.

Il y a trois caractéristiques principales qui rendent les déclencheurs différents des procédures stockées :

• Les déclencheurs ne peuvent pas être exécutés manuellement par l’utilisateur.
• Il n’y a aucune chance pour que les triggers reçoivent des paramètres.
• Vous ne pouvez pas commettre ou annuler une transaction à l’intérieur d’un trigger.

Le fait qu’il soit impossible d’utiliser des paramètres sur les triggers n’est pas une limitationpour recevoir des informations de l’événement de déclenchement. Comme vous le verrez plus loin, il existe des alternatives pour obtenir des informations sur l’événement de déclenchement.

Classes de déclencheurs SQL Server

Il existe deux classes de déclencheurs dans SQL Server :

• Les déclencheursDDL (Data Definition Language). Cette classe de déclencheurs se déclenche lors d’événements qui modifient la structure (comme la création, la modification ou la suppression d’une table),ou lors de certains événements liés au serveur comme les changements de sécurité ou les événements de mise à jour des statistiques.
• DML (Data Modification Language) triggers. Il s’agit de la classe de triggers la plus utilisée. Dans ce cas, l’événement déclencheur est une instruction de modification de données ; il peut s’agir d’une instruction d’insertion, de mise à jour ou de suppression, soit sur une table, soit sur une vue.

En outre, les déclencheurs DML ont différents types :

• FOR ou AFTER : Ces types de déclencheurs sont exécutés après la fin de l’instruction déclencheuse (soit une insertion, une mise à jour ou une suppression).
• INSTEAD OF : Contrairement au type FOR (AFTER), les déclencheursINSTEAD OF s’exécutent à la place de l’instruction de tir. En d’autres termes,ce type de déclencheur remplace l’instruction de déclenchement. C’est très utile dans les cas où vous devez avoir une intégrité référentielle entre bases de données.

Quelle est l’importance des déclencheurs SQL Server?

L’une des caractéristiques fondamentales des bases de données relationnelles est la cohérence des données.Cela signifie que les informations stockées dans la base de données doivent être cohérentes à tout moment pour chaque session et chaque transaction. La façon dont les moteurs de bases de données relationnelles comme SQL Server mettent cela en œuvre est en appliquant des contraintes comme les clés primaires et les clés étrangères. Mais parfois, cela ne suffit pas.

Dans SQL Server, il n’y a aucune chance d’appliquer l’intégrité référentielle entre deux tables en utilisant des clés étrangères si ces tables se trouvent dans différentes bases de données ou différents serveurs. Dans ce cas, la seule façon de la mettre en œuvre est d’utiliser des déclencheurs.

Comment puis-je savoir quels rangs ont été mis à jour, insérés ou supprimés en utilisant un déclencheur SQL ServerDML ?

Dans le cas des déclencheurs DML, il existe deux tables virtuelles pendant l’exécutiondu déclencheur qui contient les données affectées par l’exécution du déclencheur. Ces tables sont nommées inserted et deleted et elles ont la même structure de table que leur table de base. Il faut garder à l’esprit que les tables insérées et supprimées ne sont pas toujours disponibles ensemble (c’est-à-dire que vous pouvez avoir la table insérée, mais pas la table supprimée ou vice versa). Vous trouverez plus d’informations sur ces tables dans le conseil suivantComprendre les tables insérées et supprimées de SQL Server pour les déclencheurs DML.

SQL Server DML Trigger Syntaxe

Dans la prochaine section de code, vous verrez la syntaxe de base CREATE TRIGGER.

CREATE TRIGGER trigger_name ON { Table name or view name } { FOR | AFTER | INSTEAD OF } { , , }

En outre, le tableau suivant décrit chacun des arguments de la syntaxe CREATE TRIGGER.

.

SQL Server Trigger Scenarios d’utilisation

Il existe deux scénarios clairs dans lesquels les déclencheurs sont le meilleur choix : l’audit et l’application des règles métier. En utilisant un déclencheur, vous pouvez garder la trace des modifications sur une table donnée en écrivant un enregistrement de journal avec des informations sur l’auteur de la modification et ce qui a été modifié dans la table.

Vous pensez peut-être que vous pouvez faire la même chose dans l’application avec une procédure stockée qui gère la modification des données comme les insertions et les mises à jour. Vous pouvez utiliser une procédure stockée,mais dans ce cas, vous ne serez pas en mesure de consigner les modifications apportées directement à la base de données depuis l’extérieur de l’application.

Le même phénomène se produit lorsque vous voulez appliquer des règles de gestion avec une procédure stockée.Si quelqu’un modifie les données de la table de base depuis l’extérieur de l’application, vous pouvez avoir un problème car la cohérence des données ne peut pas être garantie. Pour éviter ce problème, vous devez vous assurer que la procédure stockée est le seul moyen d’accéder à la table.

Exemple de déclencheur DML de SQL Server

Supposons que nous ayons une base de données pour le département des ressources humaines. Cette base de données contient une table Employés pour conserver les informations sur le personnel et les salaires.A l’aide d’un trigger, nous pouvons conserver un enregistrement d’audit dans une table séparée qui contient chaque modification d’enregistrement ainsi que l’utilisateur qui a effectué la modification et l’heure à laquelle elle s’est produite.

D’abord, nous devons créer la table Employés.

CREATE TABLE Employees ( EmployeeID integer NOT NULL IDENTITY(1, 1) , EmployeeName VARCHAR(50) , EmployeeAddress VARCHAR(50) , MonthSalary NUMERIC(10, 2) PRIMARY KEY CLUSTERED (EmployeeID) )GO

Puis nous devons créer la table EmployésAudit pour stocker les enregistrements d’audit. Cette table a la même structure que la table Employees, mais comprend une colonne AuditId comme clé primaire, ModifiedDate pour conserver la date de modification, ModifiedBy pour que nous puissions savoir qui a modifié la table Employees et enfin Operation où nous indiquerons l’opération DML qui a généré l’enregistrement d’audit avec une des trois lettres (I pourinsert, U pour Update et D pour Delete).

CREATE TABLE EmployeesAudit ( AuditID INTEGER NOT NULL IDENTITY(1, 1) , EmployeeID INTEGER , EmployeeName VARCHAR(50) , EmployeeAddress VARCHAR(50) , MonthSalary NUMERIC(10, 2) , ModifiedBy VARCHAR(128) , ModifiedDate DATETIME , Operation CHAR(1) PRIMARY KEY CLUSTERED ( AuditID ) )GO

Afin de pouvoir tester l’exemple de déclencheur, nous devons ajouter quelques données à la tableEmployés.

INSERT INTO dbo.Employees ( EmployeeName , EmployeeAddress , MonthSalary )SELECT 'Mark Smith', 'Ocean Dr 1234', 10000UNION ALLSELECT 'Joe Wright', 'Evergreen 1234', 10000UNION ALLSELECT 'John Doe', 'International Dr 1234', 10000UNION ALLSELECT 'Peter Rodriguez', '74 Street 1234', 10000GO

Maintenant que nous avons l’environnement de test défini, il est temps de créer notre déclencheur. Jetez un coup d’œil au code ci-dessous.

CREATE TRIGGER TR_Audit_Employees ON dbo.Employees FOR INSERT, UPDATE, DELETEAS DECLARE @login_name VARCHAR(128) SELECT @login_name = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID IF EXISTS ( SELECT 0 FROM Deleted ) BEGIN IF EXISTS ( SELECT 0 FROM Inserted ) BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT D.EmployeeID , D.EmployeeName , D.EmployeeAddress , D.MonthSalary , @login_name , GETDATE() , 'U' FROM Deleted D END ELSE BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT D.EmployeeID , D.EmployeeName , D.EmployeeAddress , D.MonthSalary , @login_name , GETDATE() , 'D' FROM Deleted D END END ELSE BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT I.EmployeeID , I.EmployeeName , I.EmployeeAddress , I.MonthSalary , @login_name , GETDATE() , 'I' FROM Inserted I ENDGO

Basiquement, le code consiste à obtenir l’utilisateur qui modifie l’Employeestable en regardant le sys.dm_exec_sessions Dynamic ManagementView pour la session avec le SPID actuel. Après cela, le déclencheur insère un enregistrement dans la table EmployeesAudit pour chaque enregistrement inséré, mis à jour ou supprimé dans la tableEmployees ainsi que l’heure actuelle et l’opération DML qui a déclenché letrigger.

Afin de tester le déclencheur, j’ai créé trois requêtes. J’ai mis le code à l’intérieur d’unetransaction juste pour garder l’ordre dans mon environnement de test, vous pouvez omettre cela.

La première de ces requêtes est une mise à jour.

BEGIN TRANSACTIONSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1 UPDATE EmployeesSET EmployeeName = 'zzz'WHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesWHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesAuditROLLBACK TRANSACTION

Sur la capture d’écran suivante, vous verrez l’enregistrement mis à jour dans la table Employeeset le nouvel enregistrement dans EmployeesAudit qui garde la trace de l’opération DML sur la table Employees.

La deuxième requête est une insertion de deux lignes dans la table Employés.

BEGIN TRANSACTIONINSERT INTO dbo.Employees ( EmployeeName , EmployeeAddress , MonthSalary ) SELECT 'zz' , 'dsda' , 10000 UNION ALL SELECT 'Markus Rubius' , 'dsda' , 6000SELECT *FROM dbo.EmployeesSELECT *FROM dbo.EmployeesAuditROLLBACK TRANSACTION

Sur la capture d’écran suivante, vous verrez les deux lignes insérées dans la table Employeestable et leur enregistrement d’audit respectif dans la table EmployeesAudit.

Enfin, la troisième requête est une instruction de suppression sur la table Employés.

BEGIN TRANSACTIONSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1DELETE FROM dbo.EmployeesWHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesAuditSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1ROLLBACK TRANSACTION

Sur la capture d’écran suivante, vous verrez la rangée supprimée de la table Employeeset son enregistrement d’audit respectif dans la table EmployeesAudit.

Prochaines étapes

• Si vous avez eu du mal à comprendre la façon dont j’ai capturé quelle opération DML a été effectuée dans la table Employees, jetez un œil à cette astuce :Comprendre les tables insérées et supprimées de SQL Server pour les déclencheurs DML.
• Pour un exemple de déclencheur INSTEAD OF, vous pouvez consulter cette astuce:Utiliser les déclencheurs INSTEAD OF dans SQL Server pour les opérations DML.
• Vous pouvez trouver plus d’informations sur la DMV sys.dm_exec_sessions ici:Comprendre et utiliser sys.dm_exec_sessions dans SQL Server.
• Pour une explication plus détaillée de la commande EXECUTE AS, jetez un coup d’œil à l’astuce suivante :Accorder des autorisations avec la commande EXECUTE AS dans SQL Server.
• Rester à l’écoute de la catégorie Astuces des déclencheurs de SQL Server pour plus de conseils et d’astuces.

Dernière mise à jour : 2019-02-26

.

A propos de l’auteur

Daniel Farina est né à Buenos Aires, en Argentine. Autodidacte, il a montré depuis l’enfance une passion pour l’apprentissage.
Voir tous mes conseils

Ressources connexes

• Plus de conseils pour les développeurs de bases de données…

.