WayToLearnX

WayToLearnX

exercices-corriges-pl-sql
Exercice PL/SQL Corrigé 

Exercices Corrigés PL/SQL: Triggers

Thomas Aucun commentaire , , , , , ,

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          |
+--------+---------+--------------+

 

 

Vous pourrez aussi aimer

exercices-corriges-pl-sql

Exercices Corrigés PL/SQL: Types de données

Thomas 0
exercices-corriges-pl-sql

Exercices Corrigés PL/SQL: Instruction IF-THEN-ELSE

Thomas 0
exercices-corriges-pl-sql

Exercices Corrigés PL/SQL: Exceptions

Thomas 0

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *