SQL: Contraintes et Triggers

1
SQL Contraintes et Triggers

• Chapitre 5, 5.7-5.8

2
Contraintes Générales CHECK

• Syntaxe CHECK conditional-expression.
• Lexpression conditionnelle
• Exprime une IC plus générale que les clés.
• Peut contenir des requêtes.
• Requiert dêtre satisfait seulement lorsque la
  table associée est non vide.
• Peut être exprimée sur plusieurs tables
  cependant elle est le plus souvent exprimée sur
  une seule table.
• Les contraintes CHECK peuvent être nommées
• CONSTRAINT MyConstraint
• CHECK conditional-expression

3
Contraintes CHECK Exemples
Contrainte Le niveau doit être entre 1 et
10 CREATE TABLE Sailors ( sid
INTEGER, sname CHAR(10), rating INTEGER, age
REAL, PRIMARY KEY (sid), CHECK ( rating gt 1
AND rating lt 10 ))
4
Contraintes CHECK Exemples
Contrainte Les bateaux Interlake ne peuvent pas
être réservés.
5
Contraintes Générales ASSERTION
Contrainte le nombre de bateaux plus le nombre
de navigateurs est lt 100
CREATE TABLE Sailors ( sid INTEGER, sname
CHAR(10), rating INTEGER, age REAL, PRIMARY
KEY (sid), CHECK ( (SELECT COUNT (S.sid)
FROM Sailors S) (SELECT COUNT (B.bid) FROM
Boats B) lt 100 )

• Cette solution est compliquée, voire fausse
• Elle est erronément associée seulement avec
  Sailors, bien quelle implique à la fois Sailors
  et Boats.
• Si Sailors est vide, le nombre de tuples de Boats
  peut être arbitraire, car lexpression
  conditionnelle nest pas requise dêtre
  satisfaite dans ce cas.

6
Contraintes Générales ASSERTION
Contrainte le nombre de bateaux plus le nombre
de navigateurs est lt 100
CREATE ASSERTION smallClub CHECK ( (SELECT
COUNT (S.sid) FROM Sailors S) (SELECT COUNT
(B.bid) FROM Boats B) lt 100 )

• Lassertion nest associée avec aucune des tables
  impliquées.
• ASSERTION est la solution adéquate pour la raison
  ci haut.

7
Contraintes Générales Autres Exemples
Schéma Students(sid int, sname string, age
int, cgpa real) Works(sid int, pid
int, pct_time int) Projects(pid
int, budget real, ta int)

• Donnez une contrainte SQL (domaine, clé, clé
  étrangère, ou contrainte CHECK ou assertions)
  pour les exigences suivantes
• Les étudiants doivent avoir un cgpa minimum de 5.
• Chaque TA doit aussi être un étudiant.
• Le pourcentage total dassignements à des projets
  pour chaque étudiant doit être dau plus 100.
• Un TA doit avoir un cgpa plus élevé que celui de
  tout étudiant quil supervise.

8
Contraintes Générales Autres Exemples (1)
Contrainte Les étudiants doivent avoir un cgpa
minimum de 5. CREATE TABLE Students ( sid
INTEGER, sname CHAR(10), age REAL,
cgpa REAL, PRIMARY KEY
(sid), CHECK (cgpa gt 5))
Constraint Chaque TA doit aussi être un
étudiant. CREATE ASSERTION TAisStudent CHECK
( (SELECT COUNT () FROM Projects P
WHERE P.ta_id NOT IN (SELECT sid
FROM Students)) 0 )
9
Contraintes Générales Autres Exemples (2)
Contrainte Le pourcentage total dassignements à
des projets pour chaque étudiant doit être
dau plus 100. CREATE TABLE Works ( sid
INTEGER, pid INTEGER, pct_time
INTEGER, PRIMARY KEY (sid,pid),
FOREIGN KEY (sid) REFERENCES Students,
FOREIGN KEY (pid) REFERENCES
Projects, CHECK ((SELECT COUNT (W.stid)
FROM Works W
GROUP BY W.stid
HAVING SUM(pct_time) gt 100) 0)
10
Contraintes Générales Autres Exemples (3)
Contrainte Un TA doit avoir un cgpa plus élevé
que celui de tout étudiant quil
supervise.
CREATE ASSERTION TAHigherCGPA CHECK
((SELECT COUNT(S.stid) FROM Students S,
Students TA, Works W, Projects P WHERE
S.sidW.sid AND W.pidP.pid AND
P.taTA.sid AND S.cgpa gt TA.cgpa) 0)
11
Triggers

• Trigger procédure qui exécute automatiquement si
  des changements spécifiés surviennent dans la
  base de données.
• Il comportent trois partie
• Événement (active le trigger)
• Condition (teste si le trigger devrait être
  exécuté)
• Action (ce qui survient si le trigger est
  exécuté)

12
Triggers

• BEFORE trigger action exécuté avant que
  lévénement activant ne survienne.
• AFTER trigger action exécuté après que
  lévénement activant ne survienne.
• INSTEAD trigger action exécuté en lieu et place
  de lévénement activant.
• Trigger à exécution par ligne (Row-level
  trigger) exécuté une seule fois pour chaque
  tuple modifié (qui satisfait la condition du
  trigger).
• Trigger à exécution par commande (Statement-level
  trigger) exécuté une seule fois pour chaque
  commande.
• Variable de transition NEW, OLD, NEW TABLE, OLD
  TABLE.

13
Triggers Exemple (SQL92)
Incrémentez un compteur pour chaque navigateur
nouvellement dont lage lt 18.

• CREATE TRIGGER incr_count
• AFTER INSERT ON Sailors
• WHEN (new.age lt 18)
• FOR EACH ROW
• BEGIN
• countcount1
• END

• Illustre lusage de NEW pour referer à des
  tuples
• nouvellement insérés
• Existe depuis SQL92

14
Triggers Exemple (SQL1999)
Stockez les navigateurs nouvellement insérés dont
lage lt 18 dans une table spéciale.

• CREATE TRIGGER youngSailorsUpdate
• AFTER INSERT ON Sailors
• REFERENCING NEW TABLE AS NewSailors
• FOR EACH STATEMENT / This is the default /
• INSERT
• INTO YoungSailors(sid, name, age, rating)
• SELECT sid, name, age, rating
• FROM NewSailors N
• WHERE N.age lt 18

• Illustre lusage de NEW TABLE pour referer à
  un
• ensemble de tuples nouvellement insérés.
• Existe depuis SQL1999

15
Triggers Exemple plus Élaboré
Chaque fois que le cgpa dun étudiant est
augmenté, Le cgpa de son TA doit aussi être
augmenté pour être au moins aussi élevé que
celui de létudiant.

• CREATE TRIGGER bumpTAcgpa
• AFTER UPDATE ON Students
• WHEN OLD.cgpa lt NEW.cgpa
• FOR EACH ROW
• BEGIN
• UPDATE Students S
• SET S.cgpa NEW.cgpa
• WHERE S.cgpa lt NEW.cgpa
• AND S.sid IN (SELECT P.ta
• FROM Students S1, Works W,
  Projects P
• WHERE S1.sid NEW.sid
• 
  AND S1.sid W.sid
• 
  AND W.sid P.sid)
• END

• Illustre lusage de la syntaxe de PL/SQL en
  Oracle pour laction.

16
Résumé

• SQL permet la spécification de contraintes
  dintégrité.
• Les triggers répondent aux changements dans la
  base de données.