1

SQL: Contraintes et Triggers

Chapitre 5, 5.7-5.8

2

Contraintes Générales: CHECK Syntaxe: CHECK conditional-expression. L’expression 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 >= 1 AND rating <= 10 ))

4

Contraintes CHECK: Exemples

CREATE TABLE Reserves( sname CHAR(10),bid INTEGER,day DATE,PRIMARY KEY (bid,day),CONSTRAINT noInterlakeResCHECK (`Interlake’ <>

( SELECT B.bnameFROM Boats BWHERE B.bid=bid)))

Contrainte: Les bateaux Interlake ne peuvent pas être réservés.

5

Contraintes Générales: ASSERTION

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) < 100 ) Cette solution est compliquée, voire fausse:

Elle est erronément associée seulement avec Sailors, bien qu’elle implique à la fois Sailors et Boats.

Si Sailors est vide, le nombre de tuples de Boats peut être arbitraire, car l’expression conditionnelle n’est pas requise d’être satisfaite dans ce cas.

Contrainte: le nombre de bateaux plus le nombre de navigateurs est < 100

6

Contraintes Générales: ASSERTION

L’assertion n’est associée avec aucune des tables impliquées.

ASSERTION est la solution adéquate pour la raison ci haut.

CREATE ASSERTION smallClubCHECK ( (SELECT COUNT (S.sid) FROM Sailors S)+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )

Contrainte: le nombre de bateaux plus le nombre de navigateurs est < 100

7

Contraintes Générales: Autres Exemples

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 d’assignements à des projets pour chaque

étudiant doit être d’au plus 100%. Un TA doit avoir un cgpa plus élevé que celui de tout étudiant

qu’il supervise.

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)

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 >= 5))

Constraint: Chaque TA doit aussi être un étudiant.

CREATE ASSERTION TAisStudentCHECK ( (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 d’assignements à des projets pour chaque étudiant doit être d’au 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) > 100) = 0)

10

Contraintes Générales: Autres Exemples (3)

CREATE ASSERTION TAHigherCGPACHECK ((SELECT COUNT(S.stid) FROM Students S, Students TA, Works W, Projects P WHERE S.sid=W.sid AND W.pid=P.pid AND P.ta=TA.sid AND S.cgpa > TA.cgpa) =0)

Contrainte: Un TA doit avoir un cgpa plus élevé que celui de tout étudiant qu’il supervise.

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 (SQL:92)

CREATE TRIGGER incr_count AFTER INSERT ON Sailors

WHEN (new.age < 18)FOR EACH ROWBEGIN

count:=count+1;END

Illustre l’usage de NEW pour referer à des tuples nouvellement insérés Existe depuis SQL:92

Incrémentez un compteur pour chaque navigateur nouvellement dont l’age < 18.

14

Triggers: Exemple (SQL:1999)

CREATE TRIGGER youngSailorsUpdateAFTER INSERT ON Sailors

REFERENCING NEW TABLE AS NewSailorsFOR EACH STATEMENT /* This is the default */

INSERTINTO YoungSailors(sid, name, age, rating)SELECT sid, name, age, ratingFROM NewSailors NWHERE N.age <= 18

Stockez les navigateurs nouvellement insérés dont l’age < 18dans une table spéciale.

Illustre l’usage de NEW TABLE pour referer à un ensemble de tuples nouvellement insérés. Existe depuis SQL:1999

15

Triggers: Exemple plus Élaboré

CREATE TRIGGER bumpTAcgpaAFTER UPDATE ON Students

WHEN OLD.cgpa < NEW.cgpaFOR EACH ROWBEGIN UPDATE Students S SET S.cgpa = NEW.cgpa WHERE S.cgpa < 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

Chaque fois que le cgpa d’un étudiant est augmenté, Le cgpa de son TA doit aussi être augmenté pour être au moins aussi élevé que celui de l’étudiant.

Illustre l’usage de la syntaxe de PL/SQL en Oracle pour l’action.

16

Résumé SQL permet la spécification de contraintes

d’intégrité. Les triggers répondent aux changements dans

la base de données.