View
213
Download
0
Category
Preview:
Citation preview
Les triggers
1 / 18
Les triggers
Anne-Cecile Caron
Licence MIAGE - Bases de Donnees
2015-2016
Les triggers
Introduction
2 / 18
ObjectifsApres ce cours, les TD et TP correspondants, vous devez etre capablesde
I Ecrire en PL/SQL des triggers lies aux tables.I Utiliser a bon escient le parametrage des triggers :
I trigger ligne ou instructionI trigger before ou after
I Dans le cas d’un trigger ligne, utiliser correctement new et old
I Dans le cas d’un trigger ligne, utiliser la clause When
I Utiliser les predicats updating, inserting, deleting
I Comprendre donc eviter les erreurs de table en mutation.
Les triggers
Introduction
3 / 18
DefinitionI Un trigger est un programme qui se declenche automatiquement
suite a un evenement) A la di↵erence d’une procedure stockee, on ne peut pas appelerun trigger explicitement.
I En base de donnees, l’evenement est une instruction du DML quimodifie la base.(INSERT, DELETE, UPDATE)
I Ces triggers font partie du schema de la base. Leur code compile estconserve (comme pour les programmes stockes)
I Ils font partie de la norme SQL3.
I sous Oracle, aussi triggers INSTEAD OF sur les vues, et triggerssystemes
Les triggers
Introduction
4 / 18
Pourquoi ?I Les triggers peuvent servir a verifier des contraintes que l’on ne peut
pas definir de facon declarative
I Ils peuvent aussi gerer de la redondance d’information.
I Ils peuvent aussi servir a collecter des informations sur lesmises-a-jour de la base.
Les triggers
Structure d’un trigger
5 / 18
Syntaxe
CREATE [OR REPLACE] TRIGGER <nom_trigger><instant> <liste_evts>ON <nom_table> [FOR EACH ROW][WHEN ( <condition> ) ]<corps>
<instant> ::= AFTER | BEFORE<liste_evts> ::= <evt> {OR <evt>}<evt> ::= DELETE | INSERT |
UPDATE [OF { <liste_cols> }]<liste_col> ::= <nom_col> { , <nom_col> }<corps> corps de pgme PL/SQL
Les triggers
Structure d’un trigger
6 / 18
Entete du triggerOn definit
I la table,
I les instructions du DML qui declenchent le trigger
I le moment ou le trigger va se declencher par rapport a l’instructionDML (avant ou apres)
I si le trigger se declencheI une seule fois pour toute l’instruction (i.e. trigger instruction),I ou une fois pour chaque ligne modifiee/inseree/supprimee.
(i.e. trigger ligne, avec l’option FOR EACH ROW)
I et eventuellement une condition supplementaire de declenchement(clause WHEN) pour les triggers ligne
Les triggers
Structure d’un trigger
7 / 18
AFTER ou BEFORE ?I Si le trigger doit determiner si l’instruction DML est autorisee :
utiliser BEFORE
I Si le trigger doit ”fabriquer” la valeur d’une colonne pour pouvoirensuite la mettre dans la table : utiliser BEFORE.Par exemple : trigger ligne qui fabrique la valeur de clef primaire apartir d’une sequence.
I Si on a besoin que l’instruction DML soit terminee pour executer lecorps du trigger : utiliser AFTER
I pour des triggers lignes, il se peut que l’utilisation de Before ouAfter n’ait aucune importance.
Les triggers
Structure d’un trigger
8 / 18
Trigger ligne ou instruction ?1. Trigger ligne :
- notion de ligne courante, :old designe la ligne avant modificationet :new designe la ligne apres modification.
:old :newinsert null valeur insereedelete valeur supprimee nullupdate valeur avant modif valeur apres modif
- et possibilite de limiter le declenchement par la clause WHEN
2. Trigger instruction :Le trigger est execute une fois pour l’ensemble de l’instruction) pas de notion de ligne courante.
Les triggers
Structure d’un trigger
9 / 18
Ordre d’execution des triggersPour une instruction du DML sur une table de la base, il peut y avoir 4sortes de triggers possibles selon l’instant (before, after) et le type(instruction ou ligne).Ces triggers se declenchent dans l’ordre suivant :
I Trigger(s) instruction BEFOREI Pour chaque ligne concernee
I Trigger(s) ligne BEFOREI Trigger(s) ligne AFTER
I Trigger(s) instruction AFTER
Les triggers
Structure d’un trigger
10 / 18
Clause WHENOn peut definir une condition pour un trigger ligne : le trigger sedeclenchera pour chaque ligne verifiant la condition.
Create or replace trigger journal_emp
after update of salary on EMPLOYEE
for each row
when (new.salary < old.salary)
begin
insert into EMP_LOG(emp_id, date_evt, msg)
values (:new.empno, sysdate, ’salaire diminue’);
end ;
Attention syntaxe : dans la clause when il n’y a pas de ” :” devant new et old
Les triggers
Structure d’un trigger
11 / 18
Clause WHEN (2)
Empno salary
3000
1700
2100
4100
1654
9675
5467
2543
1654
9675
5467
2543
Empno salary
2000
2000
2000
4100
Update EMPSet salary = 2000Where salary < 4000
3 lignes modifiées
Emp_id date_evt msg
1654
5467 03/10/12
03/10/12
Salaire diminué
Salaire diminué
EMP EMP
EMP_LOG
Le trigger se déclenche 2 fois.
Les triggers
Structure d’un trigger
12 / 18
Corps du triggerI bloc PL/SQL
I predicat INSERTING (resp. UPDATING, DELETING) qui vaut vraissi le trigger a ete declenche par un INSERT (resp UPDATE,DELETE)
I pseudo-tuples :new et :old pour les triggers lignes
IIl ne faut pas, dans un trigger ligne, interroger une table qui
est en cours de modification (probleme de table en mutation).
Les triggers
Exemples
13 / 18
Exemple 1
create or replace trigger clef_autobefore inserton T1for each rowwhen (new.c1 is null)declare
la_clef number ;begin
select nvl(max(c1),0)+1 into la_clef from T1 ;:new.c1 := la_clef ;
end ;
insert into T1(c2) values (’abc’);--> ligne (2,’abc’)insert into T1 values (10,’def’);--> ligne(10,’def’)insert into T1(c2) values (’ghi’);--> ligne(11,’ghi’)
insert into T1(c2)select c2 from T1bis;
ORA-04091: la table CARON.T1est en mutation ;le declencheur ou la fonctionne peut la voir...
Les triggers
Exemples
14 / 18
Exemple 1 (suite)create table T1(
c1 number(3)constraint t1_pkey primary key,c2 varchar2(20)
);
create sequence seqincrement by 1start with 1 ;
create or replace trigger clef_autobefore inserton T1for each row-- ne pas mettre la clause WHEN-- du trigger precedent !begin
select seq.NEXTVALinto :new.c1 from dual ;
end ;
insert into T1(c2) values (’coucou’) ;
select * from T1;C1 C2----------------------------1 coucou
Remarque : il faut definir un trigger pour empecher de modifier c1, A FAIRE EN
EXERCICE
Les triggers
Exemples
15 / 18
Exemple 2On reprend l’exemple du Commerce, donne aux cours precedents.
alter table client add(nbCmdes NUMBER default 0 not null);
-- on met a jour Client en fonction des commandes qui existent dejaupdate clientset nbCmdes = (select count(*) from commande
where commande.num_client = client.num_client);
-- un trigger instruction pour les futures commandescreate or replace trigger calcul_nbcmdesafter insert or delete or update of num_clienton commandebegin
update clientset nbCmdes = (select count(*) from commande
where commande.num_client = client.num_client);end ;
Les triggers
Exemples
16 / 18
Exemple 2 (suite)I Dans le transparent precedent, le trigger recalcule tous les nbCmdes,
meme pour des clients pour qui ce nombre n’a pas change.
I Si on definit un trigger ligne au lieu d’un trigger instruction, on peutne faire que les calculs necessaires
create or replace trigger calcul_nbcmdesafter inserton commandefor each rowbegin
update client set nbCmdes = nbCmdes+1where num_client = :new.num_client ;
end ;-- faire la meme chose pour delete et update
Les triggers
Exemples
17 / 18
Exemple 2 (suite et fin)On peut faire 1 seul trigger pour les 3 cas : insert, update, delete.
create or replace trigger calcul_nbcmdesafter insert or delete or update of num_clienton commandefor each rowbegin
if inserting or updating thenupdate client set nbCmdes = nbCmdes+1where num_client = :new.num_client ;
end if ;if deleting or updating then
update client set nbCmdes = nbCmdes-1where num_client = :old.num_client ;
end if ;end ;
Les triggers
Exemples
18 / 18
SyntheseI programmes dont l’execution est declenchee par les instruction du
DML
I pratiques pour verifier des contraintes que l’on ne peut pas declarerdans le schema.
I Ne pas en abuser !I interdependance entre triggers qui peut etre di�cile a gerer, cascade
de triggers.I cout
Recommended