Exercice Langage SQL : Les Triggers
Exercice 1
Soit le schéma relationnel d’une agence bancaire régionale.
CLIENT (NUMCL, NOM, PRENOM, ADR, CP, VILLE, SALAIRE, CONJOINT)
DETENTEUR (NUMCL, NUMCP)
COMPTE (NUMCP, DATEOUVR, SOLDE)
Attributs soulignés : Clés primaires. Attributs en italiques: Clés étrangères.
NUMCL et CONJOINT sont définis sur le même domaine.
Écrire un trigger en insertion permettant de contrôler les contraintes suivantes :
- le département dans lequel habite le client doit être 01, 07, 26, 38, 42, 69, 73, ou 74 ;
- le nom du conjoint doit être le même que celui du client.
Exercice 2
Soit une table quelconque TABL, dont la clé primaire CLENUM est numérique.
Définir un trigger en insertion permettant d’implémenter une numérotation automatique de la clé. Le premier numéro doit être 1.
| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950 | CREATE TRIGGER INS_CLIENT BEFORE INSERT ON CLIENT FOR EACH ROW DECLARE nom_conjoint CLIENT.NOM%TYPE ;compteur CLIENT.NUMCL%TYPE ;pb_dept EXCEPTION ;pb_conjoint1 EXCEPTION ;pb_conjoint2 EXCEPTION ; BEGIN -- Contrainte sur le département IF TRUNC(:NEW.CP/1000) NOT IN (01, 07, 26, 38, 42, 69, 73, 74) THEN RAISE pb_dept ;END IF ; -- Contrainte sur le nom du conjoint (+ test d’existence du conjoint) IF NEW.CONJOINT IS NOT NULL THENSELECT COUNT(*), NOMINTO compteur, nom_conjointFROM CLIENTWHERE NUMCL = :NEW.CONJOINTGROUP BY NOM ; IF compteur = 0 THEN -- Pas de conjoint RAISE pb_conjoint1 ;END IF ; IF nom_conjoint :NEW.NOM THEN RAISE pb_conjoint2 ;END IF ;END IF ; EXCEPTION WHEN pb_dept THEN RAISE_APPLICATION_ERROR (-20501,‘Insertion impossible : le client n’habite pas en région Rhône-Alpes !’) ; WHEN pb_conjoint1 THEN RAISE_APPLICATION_ERROR (-20502,‘Insertion impossible : le conjoint du client n’existe pas !’) ; WHEN pb_conjoint2 THEN RAISE_APPLICATION_ERROR (-20503,‘Insertion impossible : le nom du conjoint est différent de celui du client !’) ; END ; |
| 1234567891011121314151617181920212223242526272829 | CREATE OR REPLACE TRIGGER cleautobefore INSERT ON tablFOR each row declare begin n integer;newkey integer;preums exception; -- Recherche s'il existe des tuples dans la tableSELECT count(*) INTO n FROM tabl;IF n=0 then raise preums; -- Premiere insertionend IF; -- Recherche la valeur de cle C la plus elevee-- et affecte C+1 a la nouvelle cleSELECT max(clenum) INTO newkey FROM tabl;:new.clenum := newkey + 1; exception -- Premier numero = 1when preums then :new.clenum := 1; end; |