Déclencheurs SQL

Concepts de base des déclencheurs SQL

• Événement déclencheur : L'événement qui provoque l'exécution d'un déclencheur, comme les instructions INSERT, UPDATE ou DELETE.
• Action du déclencheur : L'action effectuée par le déclencheur, impliquant généralement des modifications sur une ou plusieurs tables.
• Événement déclencheur : L'événement qui déclenche l'exécution du déclencheur. Cela inclut les opérations de manipulation de données telles que l'insertion, la mise à jour ou la suppression d'enregistrements dans une table.
• Table de déclenchement : La table sur laquelle l'événement déclencheur est défini, par exemple la table en cours de modification ou d'accès.

Comment fonctionnent les déclencheurs SQL

Types de déclencheurs SQL et cas d'utilisation

Déclencheurs avant et après

• Déclencheurs avant(AVANT INSERT, AVANT UPDATE, AVANT DELETE) : Ces déclencheurs s'exécutent avant l' événement déclencheur. Ils peuvent être utilisés pour modifier les données avant qu'elles ne soient enregistrées dans la base de données, ce qui est utile pour la validation ou le nettoyage des données.
• Déclencheurs après(AFTER INSERT, AFTER UPDATE, AFTER DELETE) : Ces déclencheurs s'exécutent après l 'événement déclencheur. Ils peuvent être utiles pour les actions qui dépendent des modifications apportées par l'événement, comme le maintien d'un enregistrement des modifications ou la mise à jour de tables connexes.

Déclencheurs au niveau des lignes et des états

• Déclencheurs au niveau de la ligne(POUR CHAQUE LIGNE) : Ces déclencheurs sont exécutés une fois pour chaque ligne affectée par l'événement déclencheur. Ils sont utiles pour les opérations qui doivent être appliquées à des enregistrements individuels, comme la conservation d'un historique des modifications pour chaque ligne.
• Déclencheurs au niveau de la déclaration: Ces déclencheurs sont exécutés une fois pour chaque événement déclencheur, quel que soit le nombre de lignes affectées. Ils conviennent aux opérations qui doivent être exécutées une fois par événement, comme la gestion des tableaux récapitulatifs ou d'audit.

Travailler avec des déclencheurs SQL Exemples

Syntaxe de création d'un déclencheur SQL

 CREATE TRIGGER nom_du_gâcheur {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name [FOR EACH ROW] trigger_body ;

• CREATE TRIGGER: Il s'agit de la commande utilisée pour créer un nouveau déclencheur.
• nom_du_déclencheur: Un nom unique pour le déclencheur.
• BEFORE|AFTER: Le timing du déclencheur, qui détermine le moment où l'action du déclencheur est exécutée par rapport à l'événement déclencheur.
• INSERT|UPDATE|DELETE: L'événement déclencheur qui active le déclencheur.
• ON nom_de_table: Spécifie la table sur laquelle créer le déclencheur.
• FOR EACH ROW: Modificateur facultatif pour les déclencheurs au niveau des lignes.
• trigger_body: Le code qui sera exécuté lorsque le déclencheur est activé.

Cas pratique : Création d'un déclencheur

 CREATE TRIGGER trg_salary_history AFTER UPDATE OF salary ON employees FOR EACH ROW INSERT INTO employees_salary_history (employee_id, old_salary, new_salary, changed_on) VALUES (:old.employee_id, :old.salary, :new.salary, CURRENT_TIMESTAMP) ;

Modification et suppression des déclencheurs SQL

Modification d'un déclencheur SQL Exemple

 ALTER TRIGGER trg_salary_history AFTER UPDATE OF salary ON employees FOR EACH ROW INSERT INTO employees_salary_history (employee_id, old_salary, new_salary, changed_on, updated_by) VALUES (:old.employee_id, :old.salary, :new.salary, CURRENT_TIMESTAMP, USER) ;

Suppression d'un déclencheur SQL Exemple

 DROP TRIGGER trg_salary_history ;

Ce code supprime le déclencheur 'trg_salary_history' de la base de données. Note que l'abandon d'un déclencheur n'affecte pas la table ou les données de la table, il supprime seulement la fonctionnalité du déclencheur.

Déclencheurs SQL et fonctions

Quand utiliser les déclencheurs et quand utiliser les fonctions ?

• Utiliser les déclencheurs : Choisis les déclencheurs lorsque tu veux automatiser des tâches en réponse à des événements de manipulation de données, effectuer des contrôles de cohérence entre les tables ou conserver des enregistrements historiques.
• Utiliser les fonctions : Envisage les fonctions lorsque tu as besoin d'encapsuler une logique réutilisable, d'effectuer des calculs complexes ou de manipuler des données sans déclencher d'événement. Les fonctions doivent être utilisées lorsque la logique exige de la souplesse et ne dépend pas d'événements spécifiques aux tables.

Considérations sur les performances

• Les déclencheurs : Étant donné que les déclencheurs s'exécutent automatiquement, ils peuvent introduire une surcharge de traitement et ralentir les opérations de manipulation des données. Une utilisation excessive des déclencheurs ou la mise en œuvre d'une logique complexe dans les déclencheurs peut avoir un impact négatif sur les performances de la base de données.
• Les fonctions : Les fonctions peuvent être optimisées en termes de performances car elles sont appelées explicitement et leur temps d'exécution est généralement plus rapide. Cependant, des fonctions mal écrites peuvent également avoir un impact sur les performances globales du système si elles sont gourmandes en ressources ou si elles sont appelées fréquemment dans le cadre d'opérations de manipulation de données.

Recommandations pour l'utilisation des déclencheurs et des fonctions

• Déclencheurs :
  1. Évite d'utiliser trop de déclencheurs, car cela peut nuire aux performances de la base de données.
  2. La logique des déclencheurs doit être simple, propre et axée sur un seul objectif.
  3. Utilise les déclencheurs principalement pour renforcer l'intégrité et la cohérence des données entre les tables liées.
• Fonctions :
  1. Optimise les performances des fonctions en minimisant la consommation de ressources et en évitant les appels de fonctions imbriquées.
  2. Encapsule les calculs complexes et la logique de manipulation des données dans des fonctions pour une meilleure organisation et réutilisation du code.
  3. Utilise les fonctions intégrées autant que possible, car elles sont généralement plus efficaces que les fonctions définies par l'utilisateur.

Travailler avec les déclencheurs SQL Tables insérées

Objectif de la table insérée

• Stocker de nouvelles valeurs pour les lignes insérées ou mises à jour, ce qui permet au déclencheur de référencer ou de comparer ces valeurs.
• Faciliter la mise en œuvre de la logique commerciale, de l'application des contraintes et des fonctions d'audit en fournissant un moyen d'examiner les modifications apportées.
• Améliorer l'intégrité des données en permettant au déclencheur de détecter les divergences entre les anciennes et les nouvelles valeurs de données et d'appliquer les actions appropriées avant de valider les modifications.

Utilisation des déclencheurs SQL Inserted Table

• Opérations INSERT : La table insérée contient toutes les lignes nouvellement ajoutées.
• Opérations UPDATE : Le tableau inséré contient les lignes mises à jour avec leurs nouvelles valeurs.
• Opérations DELETE : La table insérée n'est pas utilisée, car les lignes supprimées sont stockées dans une autre table temporaire appelée table "supprimée".

Exemples pratiques avec les tables insérées des déclencheurs SQL

Voici quelques exemples d'utilisation des déclencheurs SQL.

Exemple : Enregistrement des modifications apportées à une table

 CREATE TRIGGER trg_salary_log AFTER UPDATE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO salary_audit (employee_id, old_salary, new_salary, modified_at) VALUES (:old.employee_id, :old.salary, :new.salary, CURRENT_TIMESTAMP) ; END;

Exemple : Assurer l'intégrité des données avec les déclencheurs SQL insérés

 CREATE TRIGGER trg_validate_order BEFORE INSERT ON orders FOR EACH ROW BEGIN DECLARE @stock INT ; SELECT stock INTO @stock FROM products WHERE product_id = :new.product_id ; IF @stock < :new.quantity THEN ROLLBACK ; -- Si la quantité demandée est supérieure au stock disponible, annuler la transaction RETURN ; END IF ; END ;