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. • 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. CREATE TABLE Reserves ( sname CHAR(10), bid INTEGER, day DATE, PRIMARY KEY (bid,day), CONSTRAINTnoInterlakeRes CHECK (`Interlake’ <> ( SELECT B.bname FROM Boats B WHERE B.bid=bid))) 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 < 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) < 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.

6. Contraintes Générales: ASSERTION Contrainte: le nombre de bateaux plus le nombre de navigateurs est < 100 • 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 smallClub CHECK ( (SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100 )

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

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 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 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) Contrainte: Un TA doit avoir un cgpa plus élevé que celui de tout étudiant qu’il supervise. CREATE ASSERTION TAHigherCGPA CHECK ((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)

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) Incrémentez un compteur pour chaque navigateur nouvellement dont l’age < 18. CREATE TRIGGER incr_count AFTER INSERT ON Sailors WHEN (new.age < 18) FOR EACH ROW BEGIN count:=count+1; END • Illustre l’usage de NEW pour referer à des tuples nouvellement insérés • Existe depuis SQL:92

14. Triggers: Exemple (SQL:1999) Stockez les navigateurs nouvellement insérés dont l’age < 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 <= 18 • 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é 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. CREATE TRIGGER bumpTAcgpa AFTER UPDATE ON Students WHEN OLD.cgpa < NEW.cgpa FOR EACH ROW BEGIN 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 • 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.