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