Les Declencheurs

8/6/2019 Les Declencheurs

1/7

Les dclencheurs(Trigger) TS Base de donnes PROF : Mme Aissat

Page | 1

Implmentation des dclencheurs(les Triggers)

Objectifpdagogique :

A la fin de la sance, le stagiaire sera capable

y de crer des triggers en utilisant linstruction CREATE TRIGGERy dimplmenter la vrification des contraintes dintgrit

rfrentielle et les contraintes de gestion en utilisant les

triggers.

Plan :

Introduction

1. Cration dun dclencheur DML2. Rles des dclencheur DML3. Principe de fonctionnement.4. Suppression dun dclencheur5. Exemples


2/7


Page | 2

1. Introduction :

Un dclencheur est une procdure stocke spciale qui s'excute automatiquement lorsqu'un

vnement se produit dans le serveur de base de donnes. Les dclencheurs DML s'excutent lorsqu'un

utilisateur essaie de modifier des donnes via un vnement DML. Les vnements DML sont des

instructions INSERT, UPDATE ou DELETE excutes sur une table ou une vue. Ces dclencheurs

s'activent au dclenchement d'un vnement valide, que des lignes de table soient affectes ou non.Les dclencheurs du DDL repose sur le mme principe, savoir associer lexcution dune procdure stocke lexcution dune instruction CREATE, ALTER DROP, GRANT, DENY, REVO

KE et UPDATE STATISTICS.Nous allons aborder uniquement les dclencheurs DML dans ce cours.

2. Rles des dclencheur DMLLes triggers DML dfinis sur la base de donnes ont deux rles principaux:

- contrler que les donnes manipules vrifient lintgrit rfrentielle

Exemple: crer un nouveau stagiaire ncessite de laffecter une spcialit

- mettre en uvre des traitements correspondant une rgle de gestion de lentrepriseExemple: lorsquun produit est pass en commande, on mettra jour la quantit disponible enstock dans la table Produits

3. Cration dun dclencheur DMLLes dclencheurs ne peuvent tre crs que par le T-SQL.

Syntaxe :

CREATETRIGGERnom_triggerON{table|vue}

{FOR|AFTER|INSTEADOF}{ INSERT,UPDATE,DELETE}[ WITH APPEND ] [ NOT FOR REPLICATION ]AS[ IF UPDATE ( colonne )| IF ( COLUMNS_UPDATED ( )oprateur_comparaison_bits)] Instructions_SQL

Comme tout objet de BD, un trigger est identfi par son nom nom_trigger

y ON : spcifie la table ou la vue sur laquelle le dclencheur ou les actions dfinies serontexcutes.

y FOR:est quivalent AFTER ( viter dans les nouvelles versions)y AFTER: Cest le mode par dfaut des dclencheurs. Le code est excut aprs vrification de

s contra-intes dintgrit et aprs modification des donnes.y INSTEADOF

Le corps du dclencheur est excut la place de lordre SQL envoy sur la table ou la vue.

y INSERT, UPDATE,DELETEUn dclencheur peut agir par rapport une ou plusieurs actions. Dans ce cas, on sparera les actions

par des virgules.

y IF UPDATE (colonne)Ne peut tre utilis que pour les dclencheurs UPDATE ou INSERT et ne sexcutera que si la ou

les colonnes sont concernes.


3/7


Page | 3

y IF ( COLUMNS_UPDATED ( ) oprateur_comparaison_bits) :Cette fonction permet de connatre les indices de la ou des colonnes qui ont t mises jour. Pour chaq

ue colonne affecte par la mise jour, un bit est lev. Pour savoir quelles ont t les colonnes

mises jour, une simple comparaison binaire suffit.

y Instructions_SQLIl est possible dutiliser toutes les instructions Transact SQL de manipulations de donnes

(DML). Cependant, Les instructions suivantes ne sont pas autorises :y CREATE et DROP.y ALTER TABLE et ALTER DATABASE.y TRUNCATE.y GRANT et REVOKE.y UPDATE STATISTICS.y RECONFIGURE.4. Principe de fonctionnement.

Deux tables virtuelles sont cres au moment de la mise jour (insertion,suppression ou modification)

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

Les tables INSERTED et DELETED peuvent tre utilises par le trigger pour dterminer comment letraitement doit se drouler.

Les mcanismes dutilisation par SQL Server de ces deux tables diffrent en fonction de lopration

effectue sur la table contenant le ou les triggers.

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

y Cas de cration dune ligne de table (insert)La/les lignes nouvelles sont places dans la table temporaire INSERTED et dans la

table relle; toutes les lignes de la table INSERTED apparaissent dans la table de labase.

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

5. Suppression dun trigger.la procdure SQL Server DROP TRIGGER supprime un Trigger.

Syntaxe :

DROPTRIGGERnom_trigger


4/7

Les

c e c e

s

e

S B se

e

es

F

e

ss

e | 4

6. EXEMPLES : Voyons t s des exemples chaque partie de la syntaxe:

AFTER INSERT

Un dclencheur d'insertion est un dclencheur DM qui agit quand un nouvel enregistrement est

ajout sa table.

Sy! " # xe :CREATETRIGGERnom_triggerON{table|vue}

AFTERINSERTAS

Instructions_SQL

Exemple 1 :

-- un trigger qui permet de donner la date systme la dateCom-- aprs l'insertion de nouvelles commandes

USE BdconsommableGOCREATETRIGGERAjout_commandeONCommandeAFTERINSERTASBegin

UPDATECommandeSETDATECOM=GETDATE()

end;

Ajoutons une ligne comme suit:

INSERTINTOCommande(Numcom,obscom,numfou)

VALUES ('10100','RAS','00120')A laffic $ age :

Re % arquons la date de la commande:

AFTER UPDATE Un dclencheur de mise jour est un dclencheur DM qui agit quand un nouvel

enregistrement est modi i.

Syntaxe :CREATETRIGGERnom_triggerON{table|vue}

AFTERUPDATEAS

Instructions_SQL

AFTER DELETE Un dclencheur de suppression est un dclencheur DM qui agit quand

une suppression est opre surla table.


5/7

Les dclenc & eurs '(

rigger) TSBase de donnes PROF: MmeAissat

Page| 5

Synt)

xe:

CREATETRIGGERnom_triggerON{table|vue}AFTERDELETE

ASInstructions_SQL

Exemple2 :Le dclencheur suivant empche la suppression plus dune ligne de la table ligcom

USEBDconsommableGOCREATETRIGGERSup_ligcomONligcomAFTERDELETEASBegin

if (select count(*)fromdeleted)>1begin

rollbackprint'vous ne pouvez pas supprimer plus de 1 ligne'

end;end;

Pourtesterle dclencheur:

DELETEligcom

Aprs excution :

Exemple 3 :

-- le dclencheur suivant traduit la rgle de gestion "le stocke d'alerte doit-- tre > ou gale 50CREATETRIGGERMod_produitonproduit

AFTERUPDATE,INSERTASBEGIN

DECLARE@N1char(4)SET@N1=(SELECT stkalefrominserted)IFUPDATE(stkale)

beginif@N1< 50

beginROLLBACKTRANSACTIONRAISERROR('le stocke d''alerte doit tre > 50',16,1)

end;end;

end;


6/7

Les dclenc 0 eurs 1 Trigger) TSBase de donnes PROF: MmeAissat

Page| 6

pourtesterle dclencheur, effctuons un UPDATE et un INSERT dans la table produit:UPDATE PRODUIT

setstkale= 35wherecodeart='D001'

ouINSERTINTOProduitVALUES

('D005','LAPTOP',20,100,'Unit')

Et dans les deux cas, lopration est abondonne:

I UP TETeste des actions UPDATE ou INSERT sur des colonnes spcifiques.

La syntaxe est :

CREATETRIGGERtrigger_nameONnom_table

AFTERINSERT,UPDATE,DELETEASBEGIN

IF (UPDATE (Chp1)AND|ORUPDATE (Chp2))BEGIN

[]ENDELSEBEGIN

[]END

END

Exemple 5:Le trigger suivant empche la modification du code du fournisseur.

CREATETRIGGERModif_CodeonFournisseurAFTERUPDATEASBEGIN

IfUPDATE(Numfou)begin

PRINT('Vous ne pouvez pas modifier le code')ROLLBACKTRANSACTION

end;END

Pourtesterle dclancheur:

UPDATEFournisseursetnumfou='00101'wherenumfou='00100'


7/7


Page | 7

INSTEADOF : Spcifie que le dclencheur DML est excut la place de l'instruction SQL de

dclenchement, remplaant ainsi les actions de celle-ci

Exemple 6:

ce dclencheur est excut la place de linstruction INSERT dans la table Fournisseur. Le nom du

fournisseur est converti en majuscule et si le champ confou nest pas prcis (= NULL) la valeurXXXXXX est insre.

CREATETRIGGERIsert_Fourn ONFournisseurINSTEADOFINSERT

ASBEGINDECLARE@nomfouvarchar(30)DECLARE@contfouvarchar(25)set@nomfou=(selectnomfoufrominserted)set@nomfou=upper(@nomfou)set@contfou=(selectcontfoufrominserted)if@contfouisNULLset@contfou='XXXXX'

INSERTINTOFournisseurvalues((selectnumfoufrominserted),@nomfou,(selectadrfoufrominserted),

(selectcpfoufrominserted),(selectsatisffrominserted),@contfou)END

Testons le dclencheur :INSERTINTOFournisseur(numfou,nomfou,adrfou,cpfou)values('00132','Sid houm Ouali' ,'TO',15000)

Documents

Les Declencheurs