TME 11-b Triggers et Vues

On reprend les tables et les vues définies dans le TME 8 :

Tables :

• LIVRE (COTE, TITRE, CATEGORIE, AUTEUR)
• LECTEUR ( NOLECTEUR, NOM, ADRESSE)
• PRET (COTE, NOLECTEUR, DATEEMPRUNT, DATELIMITE, RENDU)

Vues :

• POLICIER (COTE, TITRE, AUTEUR) (185 nuplets)
• LECTEUR_CATEGORIE (NOLECTEUR, CATEGORIE) (21 nuplets)
• LECTEUR_POLICIER(NOLECTEUR, NOM, ADRESSE, COTE, DATEEMPRUNT, DATELIMITE) (18 nuplets)
• DEPASSEMENTS (NOLECTEUR, COTE, DATELIMITE) (4 nuplets)
• STATISTIQUES (NOLECTEUR, NBLIVRES) (3 nuplets)

1. Définissez un trigger pour pouvoir insérer un nouveau roman policier dans la vue Policier. La commande suivante peut être utilisée SQL INSERT et en utilisant seulement la vue POLICIER :

INSERT INTO POLICIER VALUES ('PIW01','Ma vie','Sherlock Holmes') ;

Le trigger pour une vue doit être du type INSTEAD OF (à la place de) : il est appelé à la place de l'insertion réelle et doit donc contenir une instruction INSERT qui ajoutera le n-uplet dans la table LIVRE.

CREATE OR REPLACE TRIGGER nom-TRIGGER
INSTEAD OF INSERT | DELETE | UPDATE
ON nom-TABLE [ORDER valeur de priorité]
[REFERENCING NEW | OLD [AS] nom-variable]
FOR EACH ROW | STATEMENT
[WHEN (conditionSQL)]
BEGIN actionSQL END ;

Attention : On ne peut pas spécifier l'attribut mis-à-jour avec l'option INSTEAD OF UPDATE.

Définissez des triggers (lorsque nécessaire) pour pouvoir propager l'effet des mises-à-jour des vues sur les table à partir desquelles elles sont calculées.

a)

DELETE DEPASSEMENTS WHERE nolecteur=5 ;

Attention : on ne veut pas effacer les n-uplets dans la table PRET.

b)

UPDATE LECTEUR_POLICIER SET DATELIMITE = DATELIMITE+14;

c)

UPDATE LECTEUR_POLICIER SET NOM = 'Gaspart Dupond' WHERE NOM = 'Gaspard Dupont';

Attention :

• Il faut que la mise-à-jour b) fonctionne toujours !
• Il y a deux lecteurs homonymes 'Gaspard Dupont' mais un seul est lecteur de policiers.