[ms sql server] - débuter avec les triggers

Dernière mise à jour le par cs_Malkuth .

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.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

==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.

Adresse d'origine

A voir également
Ce document intitulé « [ms sql server] - débuter avec les triggers » issu de CodeS SourceS (codes-sources.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.
Utilisation d'un curseur en sql
Team system for database professionals
Rejoignez-nous