Dans cet article, nous allons passer en revue les déclencheurs dans SQL Server, les différents types d’événements de déclenchement, l’ordre de déclenchement et le NOT FOR REPLICATION dans les déclencheurs. Un déclencheur est un objet de base de données qui s’exécute automatiquement lorsqu’un événement se produit. Il existe trois types d’événements différents .
- Événements DML
- Événements DDL
- Événement LOGON – Le déclencheur Logon est déclenché lorsqu’un événement LOGON se produit, c’est-à-dire. lorsqu’une session utilisateur est établie
DML Triggers in SQL Server
DML triggers in SQL Server are fired when a DML event occurs. i.e. when data is inserted/ updated/deleted in the table by a user.
Création de déclencheurs pour un événement DML
Créons quelques exemples de tables et de déclencheurs dans SQL Server.
|
….
1
2
3
|
CREATE TABLE Locations (LocationID int, LocName varchar(100))
CREATE TABLE LocationHist (LocationID int, ModifiedDate DATETIME)
|
Nous pouvons créer un déclencheur DML pour un événement spécifique ou plusieurs événements. Les déclencheurs dans SQL Server(DML) se déclenchent sur les événements indépendamment du nombre de lignes affectées.
Vous trouverez ci-dessous un exemple de syntaxe pour la création d’un déclencheur DML pour un événement de mise à jour.
|
1
… 2
3
4
5
6
7
8
9
10
11
|
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
. NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHist
SELECT LocationID
,getdate()
FROM inserted
END
|
Ces triggers sont créés au niveau de la table. Lors de la création réussie du déclencheur, nous pouvons voir les déclencheurs en naviguant vers le dossier Triggers au niveau de la table. Veuillez vous référer à l’image ci-dessous.
Au lieu de triggers dans SQL Server
Ces triggers sont déclenchés avant l’événement DML et les données réelles ne sont pas modifiées dans la table.
Par exemple, si nous spécifions un trigger instead of pour la suppression sur une table, lorsque l’instruction de suppression est émise contre la table, le trigger instead of est déclenché et le bloc T-SQL à l’intérieur des triggers dans SQL Server est exécuté mais la suppression réelle ne se produit pas.
T-Syntaxe SQL pour créer un trigger instead of
|
1
2
…. 3
4
5
6
|
CREATE TRIGGER TR_DEL_Locations ON Locations
INSTEAD OF DELETE
AS
BEGIN
Sélectionner ‘Sample Instead of trigger’ comme
END
|
- S’il y a plusieurs triggers avec instead of trigger sur la table, le trigger instead of est déclenché en premier dans l’ordre
- Il est possible de créer des triggers INSTEAD of sur des vues
- On ne peut définir qu’un seul trigger instead of par INSERT, UPDATE, ou DELETE sur une table ou une vue
Activer et désactiver les déclencheurs DML sur une table
Naviguer dans le dossier des déclencheurs au niveau de la table, sélectionner le déclencheur, faire un clic droit sur le déclencheur et cliquer sur Activer/Désactiver pour activer ou désactiver le déclencheur en utilisant SSMS.
Désactiver un déclencheur SQL Server spécifique sur une table en utilisant T-SQL.
|
1
|
. DISABLE TRIGGER TR_UPD_Locations2 on Locations
|
.
Activer un déclencheur spécifique sur la table en utilisant T-.SQL.
|
1
|
ENABLE TRIGGER TR_UPD_Locations2 on Locations
|
Pour activer tous les triggers sur une table, utilisez la syntaxe ci-dessous.
|
1
|
ENABLE TRIGGER ALL ON Locations
|
Pour désactiver tous les triggers sur une table, utilisez la syntaxe ci-dessous. Cette instruction n’est pas prise en charge si la table fait partie de la réplication par fusion.
|
1
|
DISABLE TRIGGER ALL ON Locations
|
Déposer un trigger sur une table.
Pour déposer un déclencheur DML sur la table à l’aide de SQL Server management studio, accédez au dossier Triggers sous la table. Sélectionnez la table que vous voulez déposer, faites un clic droit sur le déclencheur et cliquez sur Supprimer. Cliquez sur Ok.
T-SQL pour déposer un déclencheur sur la table.
|
1
|
DROP TRIGGER TRL_UPD_Locations2
|
La suppression d’une table entraîne la suppression de tous les déclencheurs SQL Server sur la table en même temps que celle-ci.
Trigger DDL
Les triggers DDL de SQL Server sont déclenchés sur les événements DDL, c’est-à-dire contre les instructions create, alter et drop, etc. Ces déclencheurs sont créés au niveau de la base de données ou du serveur en fonction du type d’événement DDL.
Ces déclencheurs sont utiles dans les cas ci-dessous.
- Prévenir les modifications du schéma de la base de données
- Auditer les modifications du schéma de la base de données
- Pour répondre à une modification du schéma de la base de données
Création d’un déclencheur DDL
Vous trouverez ci-dessous l’exemple de syntaxe pour la création d’un déclencheur DDL pour l’événement ALTER TABLE sur une base de données qui enregistre toutes les déclarations alter contre la table. Vous pouvez écrire votre code personnalisé pour suivre ou auditer les changements de schéma en utilisant EVENTDATA().
|
1
2
3
4
. 5
. 6
7
8
9
10
11
|
CREATE TABLE TableSchemaChanges (ChangeEvent xml, DateModified datetime)
CREATE TRIGGER TR_ALTERTABLE ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
INSERT INTO TableSchemaChanges
SELECT EVENTDATA(),GETDATE()
END
|
Vous pouvez spécifier un groupe d’événements qui se compose de différents événements DDL. Si nous spécifions un groupe d’événements lors de la création d’un déclencheur DDL, le déclencheur est déclenché lorsqu’un événement DDL dans le groupe se produit.
Par exemple, si nous voulons créer un déclencheur pour tous les événements DDL au niveau de la base de données, nous pouvons simplement spécifier le groupe d’événements DDL_DATABASE_LEVEL_EVENTS comme indiqué dans l’image ci-dessous.
Pour afficher les déclencheurs au niveau de la base de données, connectez-vous au serveur à l’aide de SQL Server management studio et accédez à la base de données. Développez la base de données et naviguez jusqu’à Programmability -> Database Triggers.
Pour visualiser les déclencheurs au niveau du serveur, connectez-vous au serveur à l’aide de SSMS et naviguez jusqu’aux objets du serveur, puis au dossier Triggers.
Activation et désactivation des déclencheurs DDL
Utiliser la syntaxe T-SQL ci-dessous pour désactiver ou activer le déclencheur DDL au niveau de la base de données.
|
1
2
3
4
5
|
..
ENABLE TRIGGER TR_DATABASEEVENTS ON DATABASE
GO
DISABLE TRIGGER TR_DATABASEEVENTS ON DATABASE
GO
|
Utilisez la syntaxe T-SQL pour abandonner un déclencheur DDL qui est créé au niveau de la base de données.
|
1
|
DROP TRIGGER TR_DATABASEEVENTS ON DATABASE
|
Déclencheurs LOGON dans SQL Server
Ces déclencheurs dans SQL Server se déclenchent en réponse à un événement LOGON. Les déclencheurs LOGON se déclenchent après une authentification réussie et avant d’établir la session de l’utilisateur.
Les déclencheurs LOGON sont créés au niveau du serveur et sont utiles dans les cas suivants :
- Pour auditer l’activité de connexion
- Pour contrôler l’activité de connexion
Création de déclencheurs LOGON
Vous pouvez utiliser EVENTDATA() et écrire votre code personnalisé pour suivre ou contrôler les connexions. Ici, je crée des déclencheurs simples dans SQL Server pour l’événement LOGON. Vous trouverez ci-dessous l’exemple de syntaxe pour créer un déclencheur LOGON.
|
1
2
3
4
5
6
7
8
9
10
|
CREATE TABLE LoginActivity (LOGONEvent XML ,Logintime datetime)
CREATE TRIGGER ON ALL SERVER
FOR LOGON AS
BEGIN
INSERT INTO LoginActivity
SELECT EVENTDATA()
,GETDATE()
END
|
Nous devons être prudents lors de la création de ces triggers car la connexion peut échouer si l’exécution du trigger échoue ou si vous n’avez pas accès aux objets référencés dans le trigger LOGON. Dans ce cas, le seul membre du rôle sysadmin peut se connecter au serveur en utilisant une connexion administrateur dédiée. Ainsi, il est toujours préférable d’activer la connexion administrateur dédiée lors de l’utilisation de ces déclencheurs.
Activation et désactivation des déclencheurs LOGON
Utiliser la syntaxe T-SQL ci-dessous pour désactiver ou activer le déclencheur LOGON.
|
1
2
3
4
5
|
ENABLE TRIGGER track_logins ON ALL SERVER
GO
DISABLE TRIGGER track_logins ON ALL SERVER
GO
|
Utilisez la syntaxe T-SQL pour déposer un déclencheur LOGON.
|
1
|
DROP TRIGGER track_logins ON ALL SERVER
|
Récursion directe
La récursion directe est un cas où le déclencheur SQL Server sur la table est déclenché et effectue une action qui déclenche à nouveau le même déclencheur.
Par exemple, veuillez vous référer à l’exemple de déclencheur ci-dessous pour une mise à jour qui est récursive directe.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE .(
NULL,
(100) NULL,
DateUpdated datetime
). ON
GO
INSERT INTO Locations VALUES(1, ‘Richmond Road’, NULL)
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
AS
BEGIN
Mise à jour de Locations set DateUpdated =GETDATE()
END
|
La récursion directe peut être contrôlée par un paramètre de base de données RECURSIVE_TRIGGERS. Si ce paramètre est activé, alors le déclencheur ci-dessus lance une erreur.
Si le paramètre de base de données RECURSIVE_TRIGGERS est désactivé, alors le déclencheur est déclenché une seule fois et ne tourne pas en boucle.
Pour modifier le paramètre RECURSIVE_TRIGGERS à l’aide de SSMS, naviguez jusqu’à la base de données, cliquez avec le bouton droit sur la base de données et sélectionnez Propriétés. Cliquez sur Options et modifiez le paramètre sur l’option que vous souhaitez.
Pour définir le paramètre RECURSIVE_TRIGGERS OFF à l’aide de T-SQL, utilisez l’instruction ci-dessous et remplacez le nom de la base de données par le nom de votre base de données.
|
1
2
|
.
ALTER DATABASE SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
GO
|
Pour définir les RECURSIVE_TRIGGERS ON en utilisant T-SQL, utilisez l’instruction ci-dessous et remplacez le nom de la base de données par le nom de votre base de données.
|
1
2
|
ALTER DATABASE SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
GO
|
Récurrence indirecte
C’est un cas où un déclencheur est déclenché et invoque un autre déclencheur du même type.
Vous trouverez ci-dessous l’exemple de déclencheur pour la récursion indirecte.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
CREATE TABLE Temp1 (id int)
GO
INSERT INTO Temp1 values (1),(2)
GO
CREATE TABLE Temp2 (id int)
GO
INSERT INTO Temp2 values (1),(2)
GO
CREATE TRIGGER TR_Temp1 on Temp1
Pour UPDATE
AS
BEGIN
UPDATE TEMP2 set ID = ‘5’ where id in (select id from inserted)
END
GO
CREATE TRIGGER TR_Temp2 on Temp2
Pour UPDATE
AS
BEGIN
UPDATE de Temp1 set ID = ‘5’ where id in (select id from inserted).
END
|
Maintenant lorsque nous mettons à jour une valeur dans la table Temp1, le déclencheur TR_Temp1 est déclenché et met à jour la table Temp2. TR_Temp2 est déclenché et met à jour la table Temp1, ce qui provoque un nouveau déclenchement de TR_Temp1.
Ce comportement peut être contrôlé en désactivant les déclencheurs imbriqués.
|
1
2
|
EXEC sp_configure ‘nested triggers’, 0 ;
GO
|
Ordre des déclencheurs de SQL Server
SQL Server permet plusieurs déclencheurs sur la table pour le même événement et il n’y a pas d’ordre défini d’exécution de ces déclencheurs.
Nous pouvons définir l’ordre d’un déclencheur comme premier ou dernier en utilisant la procédure sp_settriggerorder. Il ne peut y avoir qu’un seul premier ou dernier déclencheur pour chaque instruction sur une table.
Vous trouverez ci-dessous l’exemple de syntaxe pour définir l’ordre du déclencheur à premier pour l’instruction INSERT.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
CREATE TABLE TriggerOrderTest (id int)
GO
CREATE TRIGGER TR_1 ON TriggerOrderTest
POUR INSERT
as
BEGIN
PRINT ‘First Trigger’
END
GO
CREATE TRIGGER TR_2 ON TriggerOrderTest
FOR INSERT
as
BEGIN
Print ‘Second Trigger’.
PRINT ‘Second Trigger’
END
GO
CREATE TRIGGER TR_3 ON TriggerOrderTest
FOR INSERT
as
BEGIN
PRINT ‘Third Trigger’
END
GO
sp_settriggerorder @triggername = ‘TR_3’
, @order = ‘FIRST’
, @stmttype = ‘INSERT’
|
Maintenant, lorsque les données sont insérées dans la table « TriggerOrderTest » l’événement INSERT se produit et le déclencheur TR_3 se déclenche en premier.
Dans le cas des déclencheurs DDL, nous devons spécifier le paramètre d’espace de nom qui est la portée du déclencheur SQL Server dans la procédure stockée sp_settriggerorder.
Vous trouverez ci-dessous l’exemple de syntaxe pour définir l’ordre du déclencheur DDL.
|
1
2
3
4
|
settriggerorder @triggername = ‘DDL_3’
, @order = ‘FIRST’
, @stmttype = ‘ALTER_TABLE’
, @namespace = ‘DATABASE’
|
NOT FOR REPLICATION
NOT FOR REPLICATION indique que le déclencheur ne doit pas se déclencher lorsque l’agent de réplication synchronise les changements de données avec l’abonné.
Par exemple, si vous répliquez à la fois Locations et LocationHist. Maintenant, lorsque vous mettez à jour un enregistrement sur Location, le déclencheur est déclenché, insère l’enregistrement dans la table d’historique. Lorsque ces changements se synchronisent à une autre extrémité (abonnés), il n’y a pas besoin de déclencher le déclencheur à nouveau. Donc, si nous marquons le déclencheur pour « NOT FOR REPLICATION », le déclencheur ne se déclenche pas lorsque l’agent de réplication synchronise les changements et se déclenche uniquement pour les changements de données effectués par l’utilisateur.
Vous trouverez ci-dessous l’exemple de syntaxe pour créer un déclencheur dans SQL Server avec not for replication.
|
1
2
3
4
5
6
7
8
9
10
11
|
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
. NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHist
SELECT LocationID
,getdate()
FROM inserted
END
|
Si vous voulez que les déclencheurs dans SQL Server soient déclenchés lorsque l’agent de réplication synchronise des modifications de données à une autre extrémité, il suffit de créer le déclencheur sans spécifier « NOT FOR REPLICATION ».
- Auteur
- Messages récents
- Géo Réplication sur les bases de données Azure SQL à chiffrement de données transparent (TDE) – 24 octobre, 2019
- Présentation de la commande Collate SQL – 22 octobre 2019
- Récupération d’un mot de passe SA perdu – 20 septembre 2019
.