Automatisation des Déclencheurs SQLite

Introduction

Dans ce labo, vous explorerez l'automatisation des déclencheurs (triggers) SQLite. Vous apprendrez à créer, valider et gérer des déclencheurs, qui exécutent automatiquement des instructions SQL en réponse à des événements spécifiques sur une table. Ce labo vous guidera dans la création de journaux d'audit et la gestion efficace des déclencheurs.

Skills Graph

Créer des tables : employees et employee_audit

Dans cette étape, vous allez créer deux tables : employees pour stocker les données des employés et employee_audit pour enregistrer les mises à jour de salaire.

Tout d'abord, ouvrez l'interpréteur (shell) SQLite dans votre terminal.

sqlite3 /home/labex/project/test.db

Cette commande ouvre l'interpréteur SQLite et se connecte à un fichier de base de données nommé test.db. Si le fichier n'existe pas, SQLite le créera.

Maintenant, créez la table employees avec la structure suivante :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary REAL
);

Cette commande SQL crée une table nommée employees avec trois colonnes :

• id : Un entier qui sert de clé primaire (primary key) pour chaque employé.
• name : Un champ texte pour stocker le nom de l'employé.
• salary : Un nombre réel pour stocker le salaire de l'employé.

Ensuite, créez la table employee_audit :

CREATE TABLE employee_audit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    old_salary REAL,
    new_salary REAL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Cette table stockera un journal des modifications de salaire. Les colonnes sont :

• id : Un identifiant unique pour chaque entrée d'audit, s'incrémentant automatiquement.
• employee_id : L'ID de l'employé dont le salaire a été modifié.
• old_salary : Le salaire avant la mise à jour.
• new_salary : Le salaire après la mise à jour.
• updated_at : Un horodatage (timestamp) indiquant quand la mise à jour a eu lieu.

Insérer des exemples de données dans employees

Dans cette étape, vous allez insérer des exemples de données dans la table employees. Ces données seront utilisées pour tester le déclencheur (trigger) que vous créerez à l'étape suivante.

Exécutez les commandes SQL suivantes pour insérer trois enregistrements d'employés :

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);

Ces commandes ajoutent trois lignes à la table employees, avec les valeurs id, name et salary spécifiées.

Pour confirmer que les données ont été ajoutées correctement, exécutez cette commande pour afficher tous les enregistrements de la table :

SELECT * FROM employees;

Résultat attendu :

1|Alice|50000.0
2|Bob|60000.0
3|Charlie|70000.0

Ce résultat affiche l'id, le name et le salary pour chaque enregistrement.

Créer un déclencheur : salary_update_trigger

Maintenant, vous allez créer un déclencheur (trigger) qui enregistre automatiquement les mises à jour de salaire dans la table employee_audit.

Exécutez la commande SQL suivante pour créer le déclencheur :

CREATE TRIGGER salary_update_trigger
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO employee_audit (employee_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
END;

Cette commande crée un déclencheur nommé salary_update_trigger. Ce déclencheur sera exécuté automatiquement après toute mise à jour de la colonne salary de la table employees.

• AFTER UPDATE OF salary ON employees : Spécifie que le déclencheur sera activé après une mise à jour de la colonne salary dans la table employees.
• BEGIN ... END : Entoure les instructions SQL à exécuter lorsque le déclencheur est activé.
• INSERT INTO employee_audit ... : Insère un nouvel enregistrement dans la table employee_audit avec l'ID de l'employé, l'ancien salaire et le nouveau salaire.
  • OLD.id : Fait référence à l'id de la ligne avant la mise à jour.
  • OLD.salary : Fait référence au salary avant la mise à jour.
  • NEW.salary : Fait référence au salary après la mise à jour.

Tester le déclencheur (Trigger)

Dans cette étape, vous allez tester le déclencheur en mettant à jour le salaire d'un employé, puis en vérifiant qu'un nouvel enregistrement est ajouté à la table employee_audit.

Mettons à jour le salaire de Bob :

UPDATE employees SET salary = 65000.00 WHERE id = 2;

Cette commande met à jour la colonne salary pour l'employé avec l'id 2 (Bob) à 65000.00.

Maintenant, interrogez la table employee_audit pour voir si le déclencheur a fonctionné :

SELECT * FROM employee_audit;

Résultat attendu :

1|2|60000.0|65000.0|...

Vous devriez voir une ligne dans la table employee_audit avec l'ID de l'employé Bob, son ancien salaire et son nouveau salaire. La colonne updated_at affichera l'horodatage (timestamp) de la mise à jour. L'horodatage exact variera en fonction du moment où vous avez exécuté la mise à jour.

Supprimer le déclencheur (Trigger)

Dans cette étape, vous apprendrez à supprimer un déclencheur de votre base de données SQLite.

Pour supprimer le salary_update_trigger, exécutez la commande suivante :

DROP TRIGGER salary_update_trigger;

Cette commande supprime le salary_update_trigger de la base de données.

Pour vérifier que le déclencheur a été supprimé, vous pouvez interroger la table sqlite_master :

SELECT name FROM sqlite_master WHERE type='trigger' AND name='salary_update_trigger';

Cette requête devrait renvoyer un ensemble de résultats vide, indiquant que le déclencheur n'existe plus.

Maintenant, essayons de mettre à jour le salaire d'Alice pour confirmer que le déclencheur a bien été supprimé :

UPDATE employees SET salary = 55000.00 WHERE id = 1;

Ensuite, vérifiez la table employee_audit :

SELECT * FROM employee_audit;

Vous devriez constater qu'aucune nouvelle entrée n'a été ajoutée à la table employee_audit, ce qui confirme que le déclencheur a été supprimé avec succès.

Enfin, quittez l'interpréteur de commandes (shell) SQLite :

.exit

Résumé

Dans ce TP (travaux pratiques), vous avez appris à créer des déclencheurs (triggers) SQLite qui exécutent automatiquement des instructions SQL en réponse à des événements de mise à jour sur une table spécifique. Vous avez créé une table employees et une table employee_audit. Vous avez ensuite créé un déclencheur nommé salary_update_trigger qui enregistre les mises à jour de salaire dans la table employee_audit. Enfin, vous avez appris à supprimer un déclencheur.