Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)

[Link]
SQL Server Trigsql

Mise en place de lintgrit rfrentielle

sur une base de donnes.

Utilisation des dclencheurs.

Afpa St Brieuc - 14/01/07 Page 1

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Dfinition des dclencheurs.

Forme volue de rgles utilises pour renforcer lintgrit

de la base de donnes, particulirement lintgri t
rfrentielle.
Le plus souvent on parle de TRIGGERS.

Les triggers sont stockes avec les donnes dans la base.

F Principes:

Les triggers sont un type particulier de procdure

mmorise.

- sont attachs des tables

- ragissent aux fonctions de cra tion (create), modification
(update) et suppression (delete)
- ne peuvent pas tre appels explicitement dans les
applications

Les triggers sont dclenchs automatiquement par le noyau

SQL chaque intervention sur la table qui les supportent.

Pas de possibilits de passer outre quelque soit lutilisateur.

Afpa St Brieuc - 14/01/07 Page 2

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Mise en place dun trigger.

Prcautions prendre.

Un trigger est toujours associ une table, il est impossible

de lassocier une vue ou a une table temporaire.

Une table peut avoir au maxi mum trois triggers, chacun

tant dclench en fonction de lvnement dtect sur la
table

l Insertion de ligne
l modification de ligne
l suppression de ligne

Chaque trigger est facultatif et une table peut navoir aucun

trigger
Dautre part, un trigger ne sapplique qu une seule table,
plusieurs tables ne pouvant pas utiliser le mme trigger.

La suppression dune table entrane la destruction de ses

triggers

Principe de fonctionnement.

Deux tables virtuelles sont cres au moment de la MAJ sur

la table:
INSERTED
DELETED.
Elles sont destines contenir les lignes de la table sur
lesquelles ont t effectues des oprations.

Afpa St Brieuc - 14/01/07 Page 3

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Les tables INSERTED et DELETED peuvent tre utilises

par le trigger pour dterminer comment le traitement doit
se drouler.
Ce traitement est crire par le dveloppeur .

Les mcanismes dutilisation par SQL Server de ces deux

tables diffrent en fonction de lopration effectue sur la
table contenant le ou les triggers.

Cas de suppression dune ligne de table (delete)

La/les lignes supprimes sont places dans la table
temporaire DELETED et supprimes de la table relle;
la table DELETED et les tables de la base ne peuvent
pas avoir de lignes en commun.

Cas de cration dune ligne de table (insert)

La/les lignes nouvelle s sont places dans la table
temporaire INSERTED et dans la table relle; toutes
les lignes de la table INSERTED apparaissent dans la
table de la base.

Cas de modification dune ligne de table (update)

La/les lignes avant modification sont places dans la
table temporaire DELETED et la/les lignes aprs
modification sont places dans la table temporaire
INSERTED et dans la table relle.

Cration dun trigger.

Un trigger est un objet SQL Server, en consquence de quoi:

- les triggers seront grs de man ire autonome

Afpa St Brieuc - 14/01/07 Page 4

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

- chaque trigger porte un nom

- chaque trigger possde un certain nombre de proprits.

Les triggers doivent tre crs juste aprs la cration de la

table de sorte que lintgrit rfrentielle des donnes soit
assure.

La cration dun trigger peut se faire

- de manire interactive grce linterface graphique
- en utilisant les procdures de SQL Server.

Utiliser la procdure create trigger

Syntaxe : CREATE TRIGGER nom de trigger

ON nom de table
FOR INSERT
AS
squence1 dordres SQL
FOR UPDATE
AS
squence2 dordres SQL
FOR DELETE
AS
squence3 dordres SQL

ou CREATE TRIGGER nom de trigger

ON nom de table
FOR INSERT, UPDATE
AS
squence dordres SQL

Attention la gestion des dclencheurs dans la fentre

SQL Server.

Afpa St Brieuc - 14/01/07 Page 5

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Afpa St Brieuc - 14/01/07 Page 6

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Exemples de mise en place de triggers.

Les triggers dfinis sur la base de donnes ont deux rles principaux:

- contrler que les donnes manipules vrifient lintgrit

rfrentielle, cest dire que les tables restent
synchronises.

Exemple: crer un nou veau salari ncessite de laffecter

un service existant.

- mettre en oeuvre des traitements correspondant une rgle

de gestion de lentreprise

Exemple: lorsquun produit est pass en commande, on

mettra jour la quantit disponible en stock dans la
table Produits.

Triggers dintgrit rfrentielle.

Exemple 1 : Un avion ne peut tre cr que si le modle qui lui

correspond existe dj dans la base

CREATE TRIGGER TI_Avion ON AVION FOR INSERT

AS

BEGIN

IF (SELECT count(*) FROM MODELE, i nserted

Afpa St Brieuc - 14/01/07 Page 7

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

WHERE [Link] = [Link]) =

0
BEGIN
PRINT 'Dclencheur Insert sur AVION. Modle
inexistant. Cration impossible'
ROLLBACK TRANSACTION
RETURN
END

END

Exemple 2 : Un modle ne peut tre supprim que si aucun

avion ne correspond ce modle dans la base de
donnes

CREATE TRIGGER TD_Modele ON MODELE FOR

DELETE AS

BEGIN

IF (SELECT count(*) FROM AVION, deleted

WHERE [Link] = deleted. NUMMOD) >
0
BEGIN
PRINT 'Dclencheur Delete sur modle. Il existe
des avions
correspondant au modle'
ROLLBACK TRANSACTION
RETURN
END

Afpa St Brieuc - 14/01/07 Page 8

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

END

Afpa St Brieuc - 14/01/07 Page 9

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Exemple 3 .

En cas de modification des donnes concernant un

avion, il est ncessaire de contrler si le modle qui lui
correspond existe effectivemen t dans la base; mais
cette vrification nest faire que si la cl trangre
dans la table avion a t modifie.

CREATE TRIGGER TU_Avion ON AVION FOR UPDATE

AS

BEGIN

F IF UPDATE (CODEMODELE_MODELE)

BEGIN
IF (SELECT count(*) FROM MODELE, inse rted
WHERE [Link] = inserted.
NUMMOD) = 0
BEGIN
PRINT 'Dclencheur Update sur Avion. Modle
inexistant,
vous ne pouvez pas le modifier.'
ROLLBACK TRANSACTION
RETURN
END
END
END

Triggers traduisant des rgles de gestion.

Afpa St Brieuc - 14/01/07 Page 10

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Certaines rgles de gestion concernant les donnes de la base

peuvent tre prises en compte dans le base de donnes elle -
mme, de telle manire que la synchronisation des donnes soit
indpendante des traitements effectus.

Afpa St Brieuc - 14/01/07 Page 11

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Exemple 4.

Un vol effectu une certaine date ne doit

embarquer que deux pilotes

/*-------------------------------------------------------------
---------------*/
/* Rgle de gestion: un vol embarque deux pilotes
*/
/* Mise en place sur TRIGGER INS ERT
*/
/*-------------------------------------------------------------
---------------*/

IF(select count(*) from AFFECTE, PERSNAV, inserted

where [Link] = [Link]
and [Link] = [Link]
and [Link] = 'P'
)>2
BEGIN
PRINT 'Il y a dj deux pilotes sur ce vol'
ROLLBACK TRANSACTION
END

Dans lexemple ci-dessus, une partie seulement du trigger a t

list, elle concerne exclusivement lapplication de la rgle sur le
pilote.

Exemple 5.

Afpa St Brieuc - 14/01/07 Page 12

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Un modle dappareil nexistant plus dans la compagnie,

on souhaite ne pas conserver aux pilotes la qualification
correspondante

CREATE TRIGGER TD_Modele ON MODELE FOR

DELETE AS

BEGIN

IF (SELECT count(*) FROM AVION, deleted

WHERE [Link] MMOD = deleted. NUMMOD) > 0
BEGIN
PRINT 'Dclencheur Delete sur modle. Il
existe des avions correspondant au modle'
ROLLBACK TRANSACTION
RETURN
END

ELSE
BEGIN
DELETE QUALIFICATION FROM
QUALIFICATION, DELETED
WHERE NUMMOD = DE LETED. NUMMOD
PRINT 'Qualifications correspondant au
modle. Suppression effectue.'
END

END

Les triggers en cascade.

Afpa St Brieuc - 14/01/07 Page 13

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Une opration sur une table dclenche automatiquement, sil

existe, le trigger correspondant lopration.
Si le trigger dclenc h effectue une opration sur une autre
table, les triggers associs cette table sont alors dclenchs.
Cest ce que lon qualifie de cascade.
Le nombre de niveaux cascads est fonction du SGBD (16 en
SQL Server).

Exemple.

Dans la base de donnes A vions; on dcide qu la suppression

dun avion, on vrifiera sil existe dautres avions du mme
modle. Sil sagit du dernier avion de ce modle dans la base, on
dtruira le modle en question de la table des modle.
Hors le fait de dtruire un modle e ntrane la destruction par
trigger des lignes de qualification des pilotes pour ce modle. (cf
exemple 5) .
On est en pleine cascade!!

Mise en oeuvre.

CREATE TRIGGER TD_AVION ON AVION FOR

DELETE AS

BEGIN

declare @mod char(3)

IF(SELECT COUNT(*) F ROM AVION, deleted

WHERE [Link] = [Link]) =
0

Afpa St Brieuc - 14/01/07 Page 14

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

BEGIN
SELECT @mod = (SELECT NUMMOD from
deleted)
PRINT 'Dclencheur DELETE sur Avion. Plus
aucun avion pour le modle'
PRINT @mod
DELETE MODELE WHERE NUMMOD = @mod
END

END

CREATE TRIGGER TD_Modele ON MODELE FOR

DELETE AS
BEGIN

IF (SELECT count(*) FROM AVION, deleted

WHERE [Link] = [Link]) >
0
BEGIN
PRINT 'Dclencheur Delete sur modle.
Il existe des avions correspondant au
modle'
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
DELETE QUALIFICATION FROM
QUALIFICATION, DELETED
WHERE NUMMOD =
[Link]

Afpa St Brieuc - 14/01/07 Page 15

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

PRINT 'Qualifications correspondant au

modle. Suppression demande.'
END

END

CREATE TRIGGER TD_QUALIFICATION ON

QUALIFICATION FOR DELETE AS

BEGIN

PRINT 'Dclencheur DELETE sur Qualif. Suppression

effectue.'

END

Trace du rsultat.

Afpa St Brieuc - 14/01/07 Page 16

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Notez lordre dapparition des messages par rapport

lenchanement des triggers.

Afpa St Brieuc - 14/01/07 Page 17

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Suppression dun trigger.

Utiliser la procdure SQL Server DROP TRIGGER

Syntaxe : DROP TRIGGER nom de trigger

Ou

Au travers linterface graphique de SQL Server

Dans la fentre Objets,

Marquer le trigger supprimer
Dans le menu Objets
Prendre loption Supprimer un Objet...

Afpa St Brieuc - 14/01/07 Page 18

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Procdure de cration dun trigger grce linterface graphique

de SQL Server.

Accs la fentre de gestion des triggers.

Se fait partir de la fentre Objets dune base de donnes.

Prendre dans le menu Gestion loption Dclenche urs....
Ou menu contextuel sur la table (bouton droit de la souris sur
la table)

SQL Server affiche la fentre de gestion de triggers

Afpa St Brieuc - 14/01/07 Page 19

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Prsentation de la fentre de gestion de triggers

Choisir le trigger coder sur la table concerne

Coder le traitement du trigger partir de la fentre de

saisie que vous propose SQL Server. Utilisation de
lditeur.

Le fait de demander la cration dun dclencheur, SQL Server

propose une trame de squence SQL pouvant tre modifie.

Demander la gnration de lo bjet trigger par le bouton

Excuter (bouton vert).

Afpa St Brieuc - 14/01/07 Page 20

 Ce document a t fabriqu par PDFmail (Copyright RTE Multimedia)
[Link]
SQL Server Trigsql

Exercices sur la base de donnes GOELAND (voir dossier

gestion d'une compagnie arienne, tude de cas)

Triggers de cration

Table vol
Vrifier que date de vol infrieure ou gale date d'arrive
et heure de dpart infrieur heure d'arrive.

Table modle
Vrifier poids vide infrieur poids maxi

Table Personnel
Vrifier la cohrence des dates
Date naissance infrieure date embauche infrieure
ou gale date fin de contrat et date dcs non
renseigne.

Triggers de mise jour

Table personnel
- Interdire modification sur le sexe et date de naissance

- Vrifier la cohrence du code activit,

passages possibles de :
A R et R D, A C et C A, A D, A X.

Triggers de suppression

Table Rservation

- Ne pas supprimer une "rsa" si le vol n'est pas effectu

Afpa St Brieuc - 14/01/07 Page 21