TPTriggers

Université Henri Poincaré Nancy 1 2004-2005

TD : Contraintes d’intégrité et triggers

Hala Skaf-Molli

Création de tables avec contraintes d’intégrité

Nous allons uniquement nous intéresser aux prêts d’œuvres littéraires. Pour cela, nous allons créer les tables correspondant aux relations suivantes :

oeuvre(code_œuvre, titre, date_par)

exemplaire(code_œuvre, numero_ex, date_achat)

adherent(adh_id, nom_adh, adr_adh, date_adh, date_cotis)

prêt(code_œuvre, numero_ex, adh_id, date_prêt, date_rendu)

Créez les tables en incluant les contraintes de clés et de dépendances référentielles. Les attributs apparaissant en gras dans le schéma relationnel doivent avoir une valeur non nulle. Vérifiez de plus que la date de rendu d’un exemplaire est supérieure à la date d’emprunt. La date de parution d’une œuvre sera de type entier (seule l’année sera indiquée), toutes les autres dates étant de type date. Enfin, la contrainte référentielle sur la clé étrangère (code_œuvre, numero_ex) de la table pret doit être nommée ("ctr_pret_oeuvrex").

Insertion de tuples

Effectuez les insertions suivantes. Parmi ces insertions, certaines ne peuvent pas être satisfaites et un message d’erreur doit être généré. Expliquez pourquoi.

insert into oeuvre values ('zol001', 'Au bonheur des dames', 1883)

insert into oeuvre values ('zol001', 'Germinal', 1885)

insert into oeuvre values ('zol002', 'Germinal', 1885)

insert into oeuvre values ('pen001', 'La fee carabine', NULL) insert into exemplaire values ('zol003', 1, NULL)

insert into exemplaire values ('pen001', 1, NULL)

insert into exemplaire values ('pen001', 2, NULL)

insert into adherent values (1, 'Antoinette', '1, rue du Chateau de Versailles', '16-oct-98', '16-oct-98')

insert into adherent values (2, 'Seize', '1, rue du Chateau de Versailles', '21-jan-93', '21-jan-93')

insert into pret values ('pen001', 1, 1, '26-feb-2001', NULL)

insert into pret values ('pen001', 2, 1, '26-feb-2000', '26-mar-2000')

insert into pret values ('zol001', 1, 2, '26-feb-2001', NULL)

insert into pret values ('pen001', 1, 2, '26-mar-2000', '26-fev-2000')

Suppression d'une contrainte

Supprimez la contrainte ctr_pret_oeuvrex en utilisant la commande alter table. Essayez à nouveau l'insertion ("zol001", 1, 2, "2001-02-26", NULL ) dans la table pret. Cette insertion doit maintenant fonctionner si la contrainte d'intégrité a bien été supprimée.

Triggers

Cette question vise à redéfinir la contrainte référentielle ctr_pret_oeuvrex à l'aide d'un trigger. Créez le trigger trig_insert_pret qui vérifie, avant insertion, que le couple (code_œuvre, numero_ex) du prêt à insérer existe bien dans la table exemplaire. Si ce n’est pas le cas, un message d’erreur est affiché (utiliser raise_application_error). Remarque : lorsqu’une erreur de compilation se produit lors d’une création de trigger, les messages d’erreurs détaillés n’apparaissent pas immédiatement. Pour les obtenir, il faut exécuter la commande « show errors ».
Créez un trigger qui affecte automatiquement un numéro d'adhérent à tout nouvel adhérent inséré, en incrémentant de 1 le dernier numéro d'adhérent attribué
Créez un trigger qui permet d’empêcher de faire des emprunts ou des retours avant 9 heures du matin et après 18 heures ainsi que le dimanche.
Créez un trigger qui supprime de la base l’adhérent qui essaye d’emprunter un livre alors qu’il n’a pas payé sa cotisation annuelle.