Cet article se concentrera sur les différentes façons de désactiver les déclencheurs dans SQL Server afin qu’ils n’interfèrent pas avec certaines opérations comme les insertions en masse.
Problème
Nous avons eu une situation où nous devons charger des données dans les tables d’une de nos bases de données. Il y avait quelques déclencheurs créés sur ces tables pour l’insertion, la mise à jour et la suppression et ces déclencheurs sont définis comme « NOT FOR REPLICATION ». Pendant le chargement des données dans les tables, nous ne voulons pas que ces déclencheurs soient activés. Nous avons pensé à désactiver les déclencheurs sur les tables, mais cela les désactivera également pour les transactions des autres utilisateurs. Nous voulons qu’ils ne soient pas déclenchés uniquement pour ma session à partir de laquelle j’exécute le script de chargement de données. Dans cet article, voyons comment désactiver les triggers dans SQL Server pour un spécifique.
Solution
Nous devons d’abord comprendre le concept « NOT FOR REPLICATION » dans SQL Server.
NOT FOR REPLICATION peut être utilisé lors de la création de triggers dans SQL Server. Cela indique que ces déclencheurs ne sont pas déclenchés lorsque l’agent de réplication fait des modifications de données (INSERT / UPDATE / DELETE) sur la table.
Non seulement pour les déclencheurs dans SQL Server, cette indication peut également être utilisée lors de la création de clés étrangères, de colonnes d’identité et de contraintes de contrôle.
Dans le cas des clés étrangères, la vérification de la clé étrangère se produit uniquement lorsqu’un utilisateur modifie les données sur la table et la validation de la clé étrangère ne se produit pas lorsque l’agent de réplication synchronise ces modifications à l’autre extrémité (soit à l’abonné, soit à l’abonné et à l’éditeur en fonction du type de réplication configuré)
Dans le cas des colonnes d’identité, une nouvelle valeur d’identité n’est pas générée lorsque l’agent de réplication insère des données dans la table et la valeur d’identité originale qui est générée à la source est utilisée.
Comme les déclencheurs dans SQL Server ont été créés avec « NOT FOR REPLICATION » sur ces tables, si nous insérons des données dans la table comme un utilisateur normal, ces déclencheurs ont été déclenchés. Ces déclencheurs n’étaient pas déclenchés lorsque l’agent de réplication insère, supprime et met à jour des données sur les tables. Donc, prétendre être un agent de réplication fera mon travail. (c’est-à-dire que les déclencheurs ont été déclenchés pour toutes les sessions d’utilisateur et non déclenchés pour la session spécifique à laquelle je me suis connecté en tant qu’agent de réplication.)
Pour illustrer cela, je vais créer deux tables d’exemple « Emp » et « EmpJoining » et un déclencheur sur la table « Emp » qui est déclenché pour insérer la date de jonction lorsqu’une nouvelle ligne est insérée dans la table « Emp ».
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE TABLE Emp ( Empid int , nom varchar (50) )
GO
CREATE TABLE EmpJoining ( Empid int, JD datetime)
GO
CREATE TRIGGER TR_INSEMPDETAILS ON Emp
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON
INSERT INTO EmpJoining
SELECT Empid , GETDATE() FROM inserted
END
GO
|
Maintenant, par exemple, j’ai l’id de l’employé, le nom et leur date d’adhésion dans un autre système qui doit être importé dans les tables existantes ci-dessus. Dans ce cas, je ne veux pas que la logique de déclenchement soit déclenchée lorsque j’insère des données dans la table Emp.
Fondamentalement, j’ai un script d’insertion T-SQL pour ces deux tables qui insère le nom de l’id de l’employé dans la table « Emp » et leurs dates de jonction dans la table « EmpJoining ».
Ouvrons une session en tant qu’utilisateur normal et exécutons le script T-SQL pour insérer des données dans la table.
Login as normal user:
Ouvrez SQL Server management studio. Maintenant, connectez-vous au serveur SQL en tant qu’utilisateur normal et utilisez la base de données où vous avez créé la table. Utilisez le script ci-dessous pour insérer des données dans la table Emp et vérifier si le déclencheur est déclenché ou non.
|
1
2
3
4
5
|
INSERT INTO Emp ( Empid , name ) values ( 1 , ‘Adam’ )
select * from Emp
select * from EmpJoining
|
nous pouvons voir que le trigger est déclenché et a inséré des données dans la table EmpJoining.
Nous pouvons vérifier la propriété de la session en utilisant le script ci-dessous.
|
1
|
select SESSIONPROPERTY ( ‘replication_agent’ )
|
il retournera zéro si nous nous connectons en tant qu’utilisateur normal et il retournera un si nous nous connectons en tant qu’agent de réplication.
Étapes pour se connecter en tant qu’agent de réplication:
Maintenant, connectons-nous en tant qu’agent de réplication et insérons des données en utilisant le script.
Fermez SQL server management studio et rouvrez-le.
Entrez le serveur, le login et le mot de passe que vous voulez utiliser. cliquez sur les options. Veuillez vous référer à l’image ci-dessous.
Naviguez vers l’onglet des paramètres de connexion supplémentaires. Dans le tableau des paramètres de connexion supplémentaires, entrez REPLICATION=TRUE dans la zone de texte comme indiqué dans l’image ci-dessous.
Vous pouvez utiliser votre méthode de connexion préférée, soit l’authentification Windows, soit l’authentification SQL Server.
Cela vous connectera en tant qu’agent de réplication et toute opération DML que vous effectuez sur la table sera exécutée en tant qu’agent de réplication.
Maintenant, exécutez la requête ci-dessous pour vérifier si vous êtes connecté en tant qu’agent de réplication ou non.
|
1
|
select SESSIONPROPERTY (‘replication_agent’)
|
Il devrait retourner 1.
Maintenant, insérez quelques lignes en utilisant le script ci-dessous.
|
1
2
3
4
|
INSERT INTO Emp ( Empid , name ) values ( 2 , ‘Greg’ )
select * from Emp
select * from EmpJoining
|
Nous pouvons voir que le déclencheur n’est pas déclenché car nous nous sommes connectés en tant qu’agent de réplication et le déclencheur est défini comme « NOT FOR REPLCIATION ».
Les données sont insérées dans seulement la table Emp. Veuillez vous référer à l’image ci-dessous.
Ici, dans ce cas, le déclencheur n’est pas désactivé et disponible pour les autres transactions utilisateur qui déclencheront le déclencheur lorsqu’il y a un INSERT. Le déclencheur n’est pas déclenché uniquement pour la transaction qui est exécutée comme agent de réplication.
Utilisez la requête ci-dessous pour vérifier si votre déclencheur est marqué comme « NOT FOR REPLCIATION » ou non.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
Il existe d’autres façons de désactiver les déclencheurs dans SQL Server pour une session en manipulant le code avec des conditions.
Par exemple, vous pouvez utiliser CONTEXT_INFO() dans le code du déclencheur et le retour. L’utilisation de CONTEXT_INFO() ne nécessite pas de permissions spéciales.
Dans ce cas, si le CONTEXT_INFO correspond à la valeur spécifiée dans le déclencheur, le déclencheur revient et n’exécute pas le code ci-dessous. Veuillez vous référer au code ci-dessous.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE TRIGGER TR_INSEMPDETAILS ON Emp
FOR INSERT
AS
BEGIN
SET NOCOUNT on
DECLARE @CONT_INFO VARBINARY(128)
SELECT @CONT_INFO = CONTEXT_INFO()
IF @CONT_INFO = 0x1256698456
RETURN
INSERT INTO EmpJoining
SELECT Empid , GETDATE() FROM inséré
END
|
Dans ce cas, nous devons définir la valeur context_info à 0x1256698456 avant d’insérer des données dans la table « Emp ».
Les vues système suivantes stockent également les informations de contexte, mais l’interrogation directe de ces vues nécessite des autorisations SELECT et VIEW SERVER STATE.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
Au lieu du script T-SQL INSERT, supposons que les données sont un fichier .txt ou un fichier .csv. Nous pouvons utiliser les options BCP UTILITY ou BULK INSERT pour charger des données dans des tables sans déclencher les déclencheurs.
Utilisation de l’utilitaire BCP
Nous pouvons utiliser l’utilitaire BCP pour charger des données en vrac dans une table sans déclencher les déclencheurs dans SQL Server. Cette méthode ne fonctionnera que pour les INSERTS sur la table et ne déclenchera pas les déclencheurs créés « for insert » et « instead of insert ».
Par défaut, l’utilitaire BCP ne déclenche pas les déclencheurs lors du chargement des données dans les tables. Pour forcer l’exécution des déclencheurs, nous devrions utiliser -h « FIRE_TRIGGERS » dans BCP tout en chargeant les données dans la table.
Voici des exemples de tables utilisées dans cet exemple.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE TABLE USERS
(ID int,
NAME varchar(50))
CREATE TABLE USERHIST
(USERID int,
CREATEDDATE datetime )
CREATE TRIGGER INS_USERS ON USERS
FOR INSERT
AS
BEGIN
INSERT INTO USERHIST
SELECT ID,GETDATE() FROM INSERTED
END
|
Veuillez vous référer à l’exemple ci-dessous de BCP par défaut pour charger des données dans la table « USERS » qui ne déclenchera pas de triggers dans SQL Server. J’ai masqué le serveur d’origine, les noms des bases de données et les informations d’identification.
bcp .dbo.USERS in D:\bcp.txt -T -c -S « SERVERNAME » -Uusername -Ppassword
Veuillez vous référer à l’exemple ci-dessous de BCP avec l’indication « FIRE_TRIGGERS » pour charger des données dans la table USERS qui déclenchera des triggers.
bcp .dbo.USERS dans D:\bcp.txt -T -c -S « SERVERNAME » -Uusername -Ppassword -h « FIRE_TRIGGERS »
Utilisation de BULK INSERT
Cette option aussi ne fonctionnera que pour les INSERTS sur la table et ne déclenchera pas les déclencheurs créés « pour l’insertion » et « au lieu de l’insertion ».
Par défaut, BULK INSERT ne déclenche pas les déclencheurs dans SQL Server. Nous pouvons forcer l’exécution du déclencheur en spécifiant « FIRE_TRIGGERS »
J’ai un fichier test.txt qui comme données avec « , » comme FIELDTERMINATOR.
Veuillez vous référer au code ci-dessous pour BULK INSERT par défaut pour charger des données dans la table USERS qui ne déclenchera pas de déclencheurs dans SQL Server.
|
1
2
3
4
5
6
7
8
9
|
BULK
INSERT USERS
FROM ‘D :\\N- Test.txt’ –location avec nom de fichier
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
|
Veuillez vous référer au code ci-dessous avec l’indication « FIRE_TRIGGERS » qui déclenchera des triggers dans SQL Server lors du chargement des données du fichier test.txt file.
|
1
2
3
4
5
6
7
8
9
10
|
BULK
INSERT USERS
FROM ‘D :\\N- Test.txt’ –location avec nom de fichier
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’,
FIRE_TRIGGERS
)
GO
|
Dans cet article, nous avons discuté du comportement des triggers dans SQL Server pour les cas ci-dessous.
- Connexion en tant qu’utilisateur normal.
- Connexion en tant qu’agent de réplication et le déclencheur est défini comme non pour la réplication.
- Utilisation de la fonction CONTEXT_INFO dans le code du déclencheur.
- Utilisation de BCP par défaut et avec l’indication « FIRE_TRIGGER »
- Utilisation de BULK INSERT par défaut et avec l’indication « FIRE_TRIGGER »
Pour les points 3, 4 et 5 peu importe si nous créons des déclencheurs avec « NOT FOR REPLICATION » ou non.
- Auteur
- Postes récents
- Géo-réplication sur les bases de données Azure SQL à chiffrement de données transparent (TDE) – 24 octobre, 2019
- Aperçu de la commande Collate SQL – 22 octobre 2019
- Récupérer un mot de passe SA perdu – 20 septembre 2019
.