Déclencheurs PostgreSQL

Les déclencheurs PostgreSQL sont des fonctions de rappel de la base de données, qui sont automatiquement exécutées/appelées lorsqu'un événement de base de données spécifié se produit.

Voici quelques points importants concernant les déclencheurs PostgreSQL :

  • Les déclencheurs PostgreSQL peuvent être déclenchés dans les situations suivantes :

    • Avant d'effectuer l'opération (avant de vérifier les contraintes et de tenter d'insérer, de mettre à jour ou de supprimer).
    • Après avoir effectué l'opération (après avoir vérifié les contraintes et terminé l'insertion, la mise à jour ou la suppression).
    • Opérations INSTEAD OF (lors d'une insertion, d'une mise à jour ou d'une suppression sur une vue).

  • L'attribut FOR EACH ROW du déclencheur est facultatif. S'il est sélectionné, il est appelé une fois pour chaque ligne modifiée par l'opération ; inversement, si FOR EACH STATEMENT est sélectionné, le déclencheur s'exécute une fois par instruction, quel que soit le nombre de lignes modifiées.

  • La clause WHEN et les opérations du déclencheur peuvent accéder à chaque élément de ligne lors de la référence à NEW.column-name et OLD.column-name pour les opérations d'insertion, de suppression ou de mise à jour. Ici, column-name est le nom d'une colonne de la table à laquelle le déclencheur est associé.

  • Si une clause WHEN existe, l'instruction PostgreSQL n'est exécutée que pour la ligne pour laquelle la clause WHEN est vraie ; s'il n'y a pas de clause WHEN, l'instruction PostgreSQL est exécutée pour chaque ligne.

  • Le mot-clé BEFORE ou AFTER détermine quand l'action du déclencheur est exécutée, décidant si l'action du déclencheur est exécutée avant ou après l'insertion, la modification ou la suppression de la ligne associée.

  • La table à modifier doit exister dans la même base de données que la table ou la vue à laquelle le déclencheur est attaché, et seul tablename doit être utilisé, et non database.tablename.

  • Les options de contrainte sont spécifiées lors de la création d'un déclencheur de contrainte. Celui-ci est identique à un déclencheur ordinaire, sauf que cette contrainte peut être utilisée pour ajuster le moment de déclenchement. Lorsqu'une contrainte implémentée par un déclencheur de contrainte est violée, une exception est levée.

Syntaxe

La syntaxe de base pour créer un déclencheur est la suivante :

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- 触发器逻辑....
];

Ici, event_name peut être une opération de base de données telle que INSERT, DELETE et UPDATE sur la table mentionnée table_name. Vous pouvez éventuellement spécifier FOR EACH ROW après le nom de la table.

Voici la syntaxe pour créer un déclencheur sur une ou plusieurs colonnes spécifiées d'une table pour les opérations UPDATE :

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- 触发器逻辑....
];

Exemples

Supposons une situation dans laquelle nous voulons maintenir une piste d'audit pour chaque enregistrement inséré dans la table COMPANY nouvellement créée (si elle existe déjà, supprimez-la et recréez-la) :

exampledb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Pour maintenir la piste d'audit, nous allons créer une nouvelle table nommée AUDIT. Chaque fois qu'une nouvelle entrée d'enregistrement est ajoutée dans la table COMPANY, un message de journal y sera inséré :

exampledb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

Ici, ID est l'ID de l'enregistrement AUDIT, EMP_ID est l'ID provenant de la table COMPANY, et DATE contiendra l'horodatage de la création de l'enregistrement dans COMPANY. Donc, maintenant, créons un déclencheur sur la table COMPANY comme suit :

exampledb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

auditlogfunc() est une fonction PostgreSQL dont la définition est la suivante :

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

Maintenant, commençons à insérer des données dans la table COMPANY :

exampledb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );

À ce stade, un enregistrement a été inséré dans la table COMPANY :

En même temps, un enregistrement est également inséré dans la table AUDIT, car nous avons créé un déclencheur lors de l'insertion dans la table COMPANY. De même, nous pouvons également créer des déclencheurs pour les mises à jour et les suppressions si nécessaire :

emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

Lister les déclencheurs

Vous pouvez lister tous les déclencheurs de la base de données actuelle à partir de la table pg_trigger :

exampledb=# SELECT * FROM pg_trigger;

Si vous souhaitez lister les déclencheurs d'une table spécifique, la syntaxe est la suivante :

exampledb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

Le résultat obtenu est le suivant :

 tgname
-----------------
 example_trigger
(1 row)

Supprimer les déclencheurs

La syntaxe de base pour supprimer un déclencheur est la suivante :

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

La commande pour supprimer le déclencheur example_trigger sur la table company ci-dessus est :

drop trigger example_trigger on company;
Autres extensions