sql contraintes et triggers n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL: Contraintes et Triggers PowerPoint Presentation
Download Presentation
SQL: Contraintes et Triggers

Loading in 2 Seconds...

play fullscreen
1 / 16

SQL: Contraintes et Triggers - PowerPoint PPT Presentation


  • 119 Views
  • Uploaded on

SQL: Contraintes et Triggers. Chapitre 5, 5.7-5.8. 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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

SQL: Contraintes et Triggers


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql contraintes et triggers

SQL: Contraintes et Triggers

Chapitre 5, 5.7-5.8

contraintes g n rales check
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

contraintes check exemples
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 ))

contraintes check exemples1

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.

contraintes g n rales assertion
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.
contraintes g n rales assertion1
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 )

contraintes g n rales autres exemples
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.
contraintes g n rales autres exemples 1
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 )

contraintes g n rales autres exemples 2
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)

contraintes g n rales autres exemples 3
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)

triggers
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é)
triggers1
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.
triggers exemple sql 92
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
triggers exemple sql 1999
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
triggers exemple plus labor
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.
r sum
Résumé
  • SQL permet la spécification de contraintes d’intégrité.
  • Les triggers répondent aux changements dans la base de données.