Exercices Corrigés PL/SQL: Triggers
La meilleure façon d’apprendre quelque chose est de pratiquer des exercices. Nous avons préparer ces exercices corrigés pour les personnes (débutantes ou intermédiaires) qui sont familières avec PL/SQL. Nous espérons que ces exercices vous aideront à améliorer vos compétences en PL/SQL. Les exercices corrigés suivantes sont actuellement disponibles, nous travaillons dur pour ajouter plus d’exercices. Bon apprentissage!
Vous pouvez lire notre tutoriel PL/SQL avant de résoudre les exercices suivants.
Exercices corrigés PL/SQL: Triggers
1. Ecrivez un code en PL/SQL pour créer un trigger qui met automatiquement à jour un timestamp ‘date_modification’ chaque fois qu’une ligne du table clients est mise à jour.
Table: clients +-----------+---------------+------+---------------------------------+ | client_id | nom | age | date_modification | +-----------+---------------+------+---------------------------------+ | 1001 | Alex Barbara | 25 |2016-01-16 11:32:07.433841 -05:00| | 1002 | Lois Collins | 30 |2018-06-17 12:12:06.123846 -08:00| | 1005 | Karen Farley | 44 |2020-11-02 01:11:05.183247 -10:00| | 1006 | Kevin Thompson| 65 |2022-12-30 00:40:00.437748 -11:00| | 1007 | Scott Henry | 32 |2022-03-19 14:22:01.034146 -01:00| +-----------+---------------+------+---------------------------------+
CREATE OR REPLACE TRIGGER update_date_modification BEFORE UPDATE ON clients FOR EACH ROW BEGIN :NEW.date_modification := SYSTIMESTAMP; END; /
Nous allons tester notre trigger:
UPDATE clients SET nom = 'Bob Babtise' WHERE client_id = 1007; SELECT * FROM clients;
Sortie:
+-----------+---------------+------+---------------------------------+ | client_id | nom | age | date_modification | +-----------+---------------+------+---------------------------------+ | 1001 | Alex Barbara | 25 |2016-01-16 11:32:07.433841 -05:00| | 1002 | Lois Collins | 30 |2018-06-17 12:12:06.123846 -08:00| | 1005 | Karen Farley | 44 |2020-11-02 01:11:05.183247 -10:00| | 1006 | Kevin Thompson| 65 |2022-12-30 00:40:00.437748 -11:00| | 1007 | Bob Babtise | 32 |2024-04-29 12:44:01.074191 -00:00| +-----------+---------------+------+---------------------------------+
2. Écrire un code en PL/SQL pour créer un trigger qui empêche les mises à jour d’une certaine colonne pendant des heures de travail (8 heures à 16 heures).
Table: clients +-----------+---------------+------+---------------------------------+ | client_id | nom | age | date_modification | +-----------+---------------+------+---------------------------------+ | 1001 | Alex Barbara | 25 |2016-01-16 11:32:07.433841 -05:00| | 1002 | Lois Collins | 30 |2018-06-17 12:12:06.123846 -08:00| | 1005 | Karen Farley | 44 |2020-11-02 01:11:05.183247 -10:00| | 1006 | Kevin Thompson| 65 |2022-12-30 00:40:00.437748 -11:00| | 1007 | Scott Henry | 32 |2022-03-19 14:22:01.034146 -01:00| +-----------+---------------+------+---------------------------------+
CREATE OR REPLACE TRIGGER stop_updates
BEFORE UPDATE OF nom ON clients
FOR EACH ROW
DECLARE
heure_actuelle NUMBER;
BEGIN
SELECT TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'HH24')) INTO heure_actuelle FROM DUAL;
IF heure_actuelle < 8 OR heure_actuelle >= 16 THEN
RAISE_APPLICATION_ERROR(-20001, 'Les mises à jour ne sont pas autorisées hors les heures de travail.');
END IF;
END;
/
Nous allons tester notre trigger à l’heure 00:05:59
UPDATE clients SET nom = 'Bob Babtise' WHERE client_id = 1007;
Sortie:
Les mises à jour ne sont pas autorisées hors les heures de travail.
3. Écrire un code en PL/SQL pour développer un trigger qui renforce l’intégrité référentielle en empêchant la suppression d’un enregistrement parent s’il existe des enregistrements fils.
Table: clients +-----------+----------------+-------------+--------+----------------+ | client_id | nom_cl | ville | age | fournisseur_id | +-----------+----------------+-------------+--------+----------------+ | 1001 | Alex Barbara | Paris | 25 | 6001 | | 1002 | Lois Collins | Lyon | 30 | 6001 | | 1005 | Karen Farley | Nice | 44 | 6002 | | 1006 | Kevin Thompson | Paris | 65 | 6003 | | 1007 | Scott Henry | Marseille | 32 | 6006 | | 1003 | Ellen McMullen | Montpellier | 53 | 6001 | +-----------+----------------+-------------+--------+----------------+
Table: fournisseurs +----------------+----------------+-------------+------------+ | fournisseur_id | nom_fr | ville | commission | +----------------+----------------+-------------+------------+ | 6001 | Arnaud Beaulac | Toulouse | 0.2 | | 6002 | Fauna Gaillard | Paris | 0.5 | | 6005 | Barry Cantin | Nice | 0.9 | | 6006 | Royce Guimond | Strasbourg | 0.6 | | 6007 | Fusberta Faure | Marseille | 0.8 | | 6003 | Ernest Petit | Montpellier | 0.1 | +----------------+----------------+-------------+------------+
CREATE OR REPLACE TRIGGER empecher_suppression
BEFORE DELETE ON fournisseurs
FOR EACH ROW
DECLARE
count_fr NUMBER;
BEGIN
-- Vérifier s'il existe des enregistrements fils associés
SELECT COUNT(*) INTO count_fr FROM clients WHERE fournisseur_id = :OLD.fournisseur_id;
-- S'il existe des enregistrements fils, une erreur est générée.
IF count_fr > 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Impossible de supprimer un fournisseur avec des clients associés.');
END IF;
END;
/
Nous allons tester notre trigger:
DELETE FROM fournisseurs WHERE fournisseur_id = 6001;
Sortie:
Impossible de supprimer un fournisseur avec des clients associés.
4. Écrire un code en PL/SQL pour créer un trigger qui vérifie les valeurs dupliquées dans la colonne « nom » du table clients et lève une exception si elles sont trouvées.
Table: clients +-----------+---------------+------+---------------------------------+ | client_id | nom | age | date_modification | +-----------+---------------+------+---------------------------------+ | 1001 | Alex Barbara | 25 |2016-01-16 11:32:07.433841 -05:00| | 1002 | Lois Collins | 30 |2018-06-17 12:12:06.123846 -08:00| | 1005 | Karen Farley | 44 |2020-11-02 01:11:05.183247 -10:00| | 1006 | Kevin Thompson| 65 |2022-12-30 00:40:00.437748 -11:00| | 1007 | Scott Henry | 32 |2022-03-19 14:22:01.034146 -01:00| +-----------+---------------+------+---------------------------------+
CREATE OR REPLACE TRIGGER empecher_doublons
BEFORE INSERT ON clients
FOR EACH ROW
DECLARE
count_cl NUMBER;
BEGIN
-- Vérifier si le nouveau nom du client existe déjà
SELECT COUNT(*) INTO count_cl FROM clients WHERE nom = :NEW.nom;
-- Si une valeur dupliquée est trouvée, une erreur est soulevée.
IF count_cl > 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Le nom du client existe déjà.');
END IF;
END;
/
Nous allons tester notre trigger:
INSERT INTO clients (client_id, nom, age, date_modification) VALUES (1008, 'Alex Barbara', 25, NULL);
Sortie:
Le nom du client existe déjà.
5. Écrire un code en PL/SQL pour créer un trigger qui empêche l’insertion de nouvelles lignes si le total(ou la somme) des valeurs du montant d’achat dépasse 400$.
Table: commandes +--------+----------------+-------------+-----------+----------------+ | cmd_id | montant_achat | date_cmd | client_id | fournisseur_id | +--------+----------------+-------------+-----------+----------------+ | 8001 | 125.02 | 2024-10-06 | 1005 | 6002 | | 8002 | 60.38 | 2024-02-22 | 1005 | 6002 | | 8003 | 95.65 | 2024-05-11 | 1002 | 6001 | | 8004 | 59.32 | 2024-12-22 | 1006 | 6003 | | 8005 | 74.33 | 2024-11-30 | 1006 | 6003 | | 8006 | 188.22 | 2024-01-05 | 1003 | 6001 | +--------+----------------+-------------+-----------+----------------+
CREATE OR REPLACE TRIGGER verifier_montant_achat
BEFORE INSERT ON commandes
FOR EACH ROW
DECLARE
montant_total NUMBER;
montant_max NUMBER := 400;
BEGIN
-- Calculer le montant total de la commande
SELECT NVL(SUM(montant_achat), 0) INTO montant_total
FROM commandes
WHERE cmd_id = :NEW.cmd_id;
-- Vérifier si l'insertion de la nouvelle ligne dépasse le seuil
IF montant_total + :NEW.montant_achat > montant_max THEN
RAISE_APPLICATION_ERROR(-20001, 'Le montant total de la commande dépasse le seuil.');
END IF;
END;
/
Nous allons tester notre trigger:
INSERT INTO commandes(cmd_id, montant_achat, date_cmd, client_id, fournisseur_id) VALUES (8008, 1000, '2024-10-06', 1005, 6002);
Sortie:
Le montant total de la commande dépasse le seuil.
6. Écrire un code en PL/SQL pour mettre en œuvre un déclencheur qui calcule et met à jour automatiquement la colonne « total_cumule » en se basant sur la colonne « montant » chaque fois que de nouvelles lignes sont insérées. Résultat souhaité:
Table: commandes +--------+---------+--------------+ | cmd_id | montant | total_cumule | +--------+---------+--------------+ | 1 | 10 | 10 | | 2 | 20 | 30 | <-- 20 + 10 | 3 | 70 | 100 | <-- 70 + 30 +--------+---------+--------------+
CREATE OR REPLACE TRIGGER update_montant_total
BEFORE INSERT ON commandes
FOR EACH ROW
BEGIN
IF :NEW.total_cumule IS NULL THEN
SELECT NVL(MAX(total_cumule), 0) + :NEW.montant
INTO :NEW.total_cumule
FROM commandes;
ELSE
:NEW.total_cumule := :NEW.total_cumule + :NEW.montant;
END IF;
END;
/
Nous allons tester notre trigger:
INSERT INTO commandes (cmd_id, montant) VALUES (1, 10); INSERT INTO commandes (cmd_id, montant) VALUES (2, 20); INSERT INTO commandes (cmd_id, montant) VALUES (3, 70); SELECT * FROM commandes;
Sortie:
+--------+---------+--------------+ | cmd_id | montant | total_cumule | +--------+---------+--------------+ | 1 | 10 | 10 | | 2 | 20 | 30 | | 3 | 70 | 100 | +--------+---------+--------------+
- 32 Exercices Corrigés PL/SQL: Principes de base
- Exercices Corrigés PL/SQL: Instruction IF-THEN-ELSE
- Exercices Corrigés PL/SQL: Types de données
- Exercices Corrigés PL/SQL: Fonctions de chaînes de caractères
- Exercices Corrigés PL/SQL: Curseurs
- Exercices Corrigés PL/SQL: Triggers
- Exercices Corrigés PL/SQL: Exceptions
