Automatisation des déclencheurs PostgreSQL

Introduction

Dans ce labo, vous apprendrez à automatiser des tâches dans PostgreSQL en utilisant des triggers (déclencheurs). Le labo se concentre sur la création d'une fonction de trigger en utilisant PL/pgSQL pour enregistrer automatiquement les modifications de salaire dans une table employees vers une table salary_changes distincte.

Vous serez guidé à travers l'écriture de la fonction de trigger, sa liaison à un événement de table (plus précisément, une mise à jour sur la table employees), le test du comportement du trigger, et enfin, la désactivation et la suppression du trigger.

Skills Graph

Créer les tables et la fonction de trigger

Dans cette étape, vous allez créer les tables employees et salary_changes, puis définir la fonction log_salary_change en utilisant PL/pgSQL.

Tout d'abord, connectez-vous à la base de données PostgreSQL en tant qu'utilisateur postgres :

sudo -u postgres psql

Maintenant, créez la table employees :

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

Cette commande crée une table avec une colonne id (clé primaire), name et salary.

Ensuite, créez la table salary_changes pour stocker l'historique des modifications de salaire :

CREATE TABLE salary_changes (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_date TIMESTAMP
);

Maintenant, écrivons la fonction de trigger en utilisant PL/pgSQL. Cette fonction sera exécutée avant une opération de mise à jour sur la table employees. Elle enregistrera les anciennes et nouvelles valeurs de salaire dans la table salary_changes.

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Ce code définit une fonction nommée log_salary_change. Elle vérifie si le salaire a changé. Si c'est le cas, elle insère une nouvelle ligne dans la table salary_changes avec l'ID de l'employé, l'ancien salaire, le nouveau salaire et l'horodatage (timestamp) actuel.

Créer le trigger

Dans cette étape, vous allez lier la fonction log_salary_change à la table employees afin qu'elle soit automatiquement exécutée lorsqu'une mise à jour de salaire se produit.

Créez le trigger en utilisant la commande SQL suivante :

CREATE TRIGGER salary_change_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

Cette commande crée un trigger nommé salary_change_trigger. Elle spécifie que le trigger doit être activé avant une opération UPDATE sur la table employees. La clause FOR EACH ROW indique que la fonction de trigger doit être exécutée pour chaque ligne qui est mise à jour.

Tester le trigger

Dans cette étape, vous allez tester le comportement du trigger. Vous insérerez des données dans la table employees, puis vous mettrez à jour le salaire pour voir si le trigger enregistre correctement les modifications dans la table salary_changes.

Tout d'abord, insérez des données initiales dans la table employees :

INSERT INTO employees (name, salary) VALUES ('Alice', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Bob', 60000.00);

Maintenant, mettez à jour le salaire d'Alice :

UPDATE employees SET salary = 55000.00 WHERE name = 'Alice';

Pour vérifier que le trigger a fonctionné correctement, interrogez la table salary_changes :

SELECT * FROM salary_changes;

Vous devriez voir une ligne dans la table salary_changes avec les informations sur la modification de salaire d'Alice.

Ensuite, mettez à jour le salaire de Bob avec la même valeur qu'il a déjà :

UPDATE employees SET salary = 60000.00 WHERE name = 'Bob';

Étant donné que le salaire ne change pas réellement, le trigger ne devrait pas insérer une nouvelle ligne dans la table salary_changes. Vérifions cela :

SELECT * FROM salary_changes;

Vous ne devriez toujours voir que la ligne relative à la modification de salaire d'Alice.

Enfin, insérez un nouvel employé :

INSERT INTO employees (name, salary) VALUES ('Charlie', 70000.00);

L'insertion d'un nouvel employé ne devrait pas déclencher le trigger log_salary_change, car il est uniquement configuré pour se déclencher lors des événements UPDATE. Confirmons cela en vérifiant à nouveau la table salary_changes :

SELECT * FROM salary_changes;

Vous ne devriez toujours voir que la ligne relative à la modification de salaire d'Alice.

Désactiver et supprimer le trigger

Dans cette étape, vous apprendrez comment désactiver et supprimer un trigger dans PostgreSQL.

Tout d'abord, désactivez le salary_change_trigger :

ALTER TABLE employees DISABLE TRIGGER salary_change_trigger;

Maintenant, mettez à jour le salaire d'Alice à nouveau :

UPDATE employees SET salary = 60000.00 WHERE name = 'Alice';

Vérifiez la table salary_changes :

SELECT * FROM salary_changes;

Vous ne devriez toujours voir que la ligne de la mise à jour précédente. Cela confirme que le trigger est désactivé.

Pour réactiver le trigger, vous pouvez utiliser la commande suivante :

ALTER TABLE employees ENABLE TRIGGER salary_change_trigger;

Enfin, pour supprimer définitivement le trigger, supprimez-le à l'aide de la commande suivante :

DROP TRIGGER salary_change_trigger ON employees;

Résumé

Dans ce labo, vous avez appris à automatiser des tâches dans PostgreSQL à l'aide de triggers (déclencheurs). Vous avez créé une fonction de trigger en utilisant PL/pgSQL pour enregistrer les modifications de salaire dans une table employees vers une table salary_changes. Vous avez écrit la fonction, l'avez liée à un événement de table, l'avez testée et l'avez désactivée/supprimée.