Les triggers
1. Avant tout
J’ai essayé de réunir ici ce dont on pouvait avoir besoin pour débuter avec les triggers, toutefois si vous pensez trouver une erreur ou un manque n’hésitez pas à m’en faire part, merci. Cette tutorial n’est pas totalement achevé mais je la finirai dans le courant de la semaine.
2. Syntaxe
2.1. La syntaxe telle que précisée dans la MSDN
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
sql_statement [ ...n ]
}
}
2.2. Quelques précisions :
[WITH APPEND] est considéré comme obsolète et ne sera pas commenté.
[NOT FOR REPLICATION] est plus en rapport avec la réplication et ne sera pas commenté.
[WITH ENCRYPTION] est plus en rapport avec la sécurité et la réplication et ne sera pas commenté.
2.3. Explications :
CREATE TRIGGER indique que l’on créé un trigger.
trigger_name nom que l’on souhaite donné au trigger.
ON {table | view} précise sur quelle table ou quelle vue s’applique le trigger.
{FOR | AFTER | INSTEAD OF} précise le comportement du trigger.
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] DELETE ] } Précise le ou les évènements déclancheurs.
AS introduit le code SQL du trigger
Nous voyons donc qu’un trigger associe un code SQL avec une table (ou une vue), un évènement déclencheur et un comportement. Les deux prochaines parties expliciteront les évènements déclencheurs ainsi que les comportements.
3. Les évènements déclencheurs des triggers
Il y a trois évènements susceptibles de déclancher un trigger, ils correspondent aux trois actions possible sur un enregistrement dans une table INSERT, DELETE et UPDATE. Un trigger doit spécifier au moins l’un de ces comportements mais peut tout à fait être déclanché par DEUX ou TROIS évènements, il suffit pour cela de les séparer par une virgule dans la déclaration :
CREATE TRIGGER trigger_name
ON table
AFTER INSERT, UPDATE, DELETE
AS
[…]
3.1. L’évènement INSERT
L’évènement insert est déclanché lors de l’ajout d’un enregistrement.
3.2. L’évènement UPDATE
L’évènement update est déclanché lors de la modification d’un enregistrement.
3.3. L’évènement DELETE
L’évènement DELETE est déclanché lors de la suppression d’un enregistrement.
4. Les modes de comportement des triggers
Les triggers possèdent deux comportements différents, soit ils effectuent des opérations à la suite de l’action déclenchante, soit ils effectuent des opérations à la place de l’action déclenchante
4.1. AFTER
Le comportement AFTER indique que le trigger est déclanché après l’action déclenchante :
Lors d’une action de suppression : d’abord les enregistrements sont supprimés de la table, ensuite les contraintes sont validées, enfin le trigger est déclenché.
Il peut y avoir plusieurs triggers AFTER sur chaque événement.
4.2. FOR
FOR est considéré comme obsolète et équivalent à AFTER.
4.3. INSTEAD OF
Le comportement INSTEAD OF indique que le trigger est déclanché à la place de l’action déclenchante.
Deux triggers INSTEAD OF d’une même table ne peuvent se déclancher par le même évènement.
5. Les pseudo tables
Un problème se pose maintenant comment récupérer des informations sur l’opération déclenchante ? Pour cela on utilise les pseudo tables INSERTED et DELETED.
Les pseudo tables possèdent la même définition que la table sur laquelle le trigger est appliqué. Elles ne permettent que des opérations de sélection (Pas de DELETE, INSERT, UPDATE).
5.1. La pseudo table Inserted
La pseudo table Inserted possède la même définition que la table sur laquelle le trigger est appliqué.
Elle représente soit les nouveaux enregistrements dans le cas de l’évènement INSERT soit les nouvelles valeurs des enregistrements dans le cas de l’évènement UPDATE.
5.2. La pseudo table Deleted
Elle représente soit les enregistrements supprimés dans le cas de l’évènement DELETE, soit les anciennes valeurs des enregistrements dans le cas de l’évènement UPDATE.
6. Les structures conditionnelles spécifiques des triggers
Deux fonctions spécifiques aux triggers existent. Elle permettent de détailler les colonnes affectées par une insertion ou une mise à jour.
6.1. IF UPDATE (column)
IF UPDATE (column) permet de déterminer lors d’une procédure d’insertion ou de mise à jour si une colonne a été renseignée ou non
CREATE TRIGGER trigger_name
ON table
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
IF UPDATE (Chp1, Chp2)
BEGIN
[…]
END
ELSE IF UPDATE (Chp1, Chp2)
BEGIN
[…]
END
ELSE
BEGIN
[…]
END
END
6.2. COLUMNS_UPDATED ( )
[A VENIR]
7. Triggers et récursivité
[A VENIR]
8. Activer et désactiver les triggers
Une chose intéressante est de pouvoir activer et désactiver les triggers.
8.1. Activer un Trigger
Pour activer un trigger on utilise cette syntaxe
ALTER TABLE table ENABLE TRIGGER trigger_name
8.2. Désactiver un trigger
Pour Désactiver un trigger on utilise cette syntaxe
ALTER TABLE table DISABLE TRIGGER trigger_name
9. Les triggers pour faire quoi ?
Maintenant quelques exemples
9.1. Logs & Débuggage
Les triggers permettent de facilement implémenter des logs sur les ajouts suppression et mise à jour d’enregistrements, de plus, la possibilité de les activer et les désactiver permet la création de deux procédures stockées pour passer d’un mode avec logs à un mode sans logs pour plus de rapidité.
Creation d’une table Logs :
CREATE TABLE [dbo].[Logs](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DateLog] [datetime] NOT NULL CONSTRAINT [DF_Logs_DateLog] DEFAULT (getdate()),
[Table] [nvarchar](50) COLLATE French_CI_AS NOT NULL,
[Message] [nvarchar](200) COLLATE French_CI_AS NOT NULL,
CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Creation d’une table Contacts
CREATE TABLE [dbo].[Contacts](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Nom] [nvarchar](50) COLLATE French_CI_AS NOT NULL,
[Numéro] [nvarchar](20) COLLATE French_CI_AS NOT NULL,
[Service] [bigint] NOT NULL,
CONSTRAINT [PK_Contacts_] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Triggers des logs d’insertion dans Contacts
CREATE TRIGGER Log_Insert_Contacts
ON Contacts
AFTER INSERT
AS
BEGIN
DECLARE @RC as nvarchar (10)
SET @RC = (SELECT COUNT(*) FROM inserted)
INSERT INTO Logs
([Table],
Message)
VALUES
(N'Contacts',
N'Insertion de ' + @RC + N' enregistrements.' )
END
Triggers des logs de mise à jour dans Contacts
CREATE TRIGGER Log_Update_Contacts
ON Contacts
AFTER UPDATE
AS
BEGIN
DECLARE @RC as nvarchar (10)
SET @RC = (SELECT COUNT(*) FROM inserted)
INSERT INTO Logs
([Table],
Message)
VALUES
(N'Contacts',
N'Mise à jour de ' + @RC + N' enregistrements.' )
END
Triggers des logs de suppression dans Contacts
CREATE TRIGGER Log_Delete_Contacts
ON Contacts
AFTER DELETE
AS
BEGIN
DECLARE @RC as nvarchar (10)
SET @RC = (SELECT COUNT(*) FROM deleted)
INSERT INTO Logs
([Table],
Message)
VALUES
(N'Contacts',
N'Supression de ' + @RC + N' enregistrements.' )
END
Procédure pour désactiver les logs :
CREATE PROCEDURE Desactiver_Logs
AS
BEGIN
ALTER TABLE Contacts DISABLE TRIGGER Log_Insert_Contacts
ALTER TABLE Contacts DISABLE TRIGGER Log_Update_Contacts
ALTER TABLE Contacts DISABLE TRIGGER Log_Delete_Contacts
END
Procédure pour activer les logs :
CREATE PROCEDURE Activer_Logs
AS
BEGIN
ALTER TABLE Contacts ENABLE TRIGGER Log_Insert_Contacts
ALTER TABLE Contacts ENABLE TRIGGER Log_Update_Contacts
ALTER TABLE Contacts ENABLE TRIGGER Log_Delete_Contacts
END
9.2. Statistiques & Compteurs
Les Triggers me semblent intéressant pour réaliser des statistiques en temps réel car ils ne nécessitent pas forcement une forte puissance de calcul.
Nous reprenons l’exemple précédent
Ajout de la table Services
CREATE TABLE [dbo].[Services](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Libelle] [nvarchar](100) COLLATE French_CI_AS NOT NULL,
[NombreContacts] [bigint] NOT NULL CONSTRAINT [DF_Services_NombreContacts] DEFAULT ((0)),
CONSTRAINT [PK_Services] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Modification de la table Contacts
DELETE Contacts
ALTER TABLE dbo.Contacts ADD CONSTRAINT
FK_Contacts__Services FOREIGN KEY
(
Service
) REFERENCES dbo.Services
(
ID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
Ajout du trigger de Mise à jour des statistiques
CREATE TRIGGER Stat_ContactsparService
ON Contacts
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
--Mise a jour du compteur pour les nouveaux enregistrement
UPDATE Services
SET
NombreContacts = Services.NombreContacts + NombreAjout_Service.NombreAjouter
FROM
Services
INNER JOIN (
SELECT
Service,
COUNT_BIG(Service) AS NombreAjouter
FROM
inserted
GROUP BY Service
) AS NombreAjout_Service
ON Services.ID = NombreAjout_Service.Service
--Mise a jour du compteur pour les Anciens enregistrement
UPDATE Services
SET
NombreContacts = Services.NombreContacts - NombreSuppr_Service.NombreSupprimer
FROM
Services
INNER JOIN (
SELECT
Service,
COUNT_BIG(Service) AS NombreSupprimer
FROM
deleted
GROUP BY Service
) AS NombreSuppr_Service
ON Services.ID = NombreSuppr_Service.Service
END
Et voilà notre compteur sera modifié dès que nous ajouterons, supprimerons ou mettrons à jour des enregistrements de la table Contacts.
On remarquera que le nouveau trigger se superpose aux autres existants, cela permet de pouvoir désactiver les triggers pour le logs tout en conservant le trigger de statistique actif.
9.3. A vous de voir
Il y a bien d’autres applications pour les triggers, c’est à vous de voir s’ils sont plus pratiques que d’autres techniques.
Toutefois je déconseille l’utilisation des triggers pour gérer les suppressions en cascade (Suppressions automatiques des enregistrements dans les table) avec une contrainte de clef étrangère.
Il est préférable d’utiliser les contraintes de clef étrangère avec suppression en cascade.
Pour reprendre l’exemple précédant :
ALTER TABLE dbo.Contacts_
DROP CONSTRAINT FK_Contacts__Services
ALTER TABLE dbo.Contacts ADD CONSTRAINT
FK_Contacts__Services FOREIGN KEY
(
Service
) REFERENCES dbo.Services
(
ID
) ON UPDATE NO ACTION
ON DELETE CASCADE
La même chose avec un trigger :
CREATE TRIGGER Delete_Cascade_Services
ON Services
INSTEAD OF DELETE
AS
BEGIN
DELETE Contacts WHERE Service IN (SELECT ID FROM Deleted)
DELETE Services WHERE ID IN (SELECT ID FROM Deleted)
END
On remarque l’utilisation de INSTEAD OF plutôt que AFTER l’objectif est de d’abord supprimer les contacts et ensuite les services comme ca la supression des services ne créé pas d’erreur sur la contrainte de clef étrangère.