PostgreSQL - DÉCLENCHEURS

PostgreSQL Triggers sont des fonctions de rappel de 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 des points importants sur les déclencheurs PostgreSQL -

• Le déclencheur PostgreSQL peut être spécifié pour se déclencher

  • Avant que l'opération ne soit tentée sur une ligne (avant que les contraintes ne soient vérifiées et que INSERT, UPDATE ou DELETE soit tenté)

  • Une fois l'opération terminée (une fois que les contraintes sont vérifiées et que INSERT, UPDATE ou DELETE est terminé)

  • Au lieu de l'opération (dans le cas d'insertions, de mises à jour ou de suppressions sur une vue)

• Un déclencheur marqué POUR CHAQUE LIGNE est appelé une fois pour chaque ligne que l'opération modifie. En revanche, un déclencheur marqué POUR CHAQUE STATEMENT ne s'exécute qu'une seule fois pour une opération donnée, quel que soit le nombre de lignes qu'il modifie.

• Tant la clause WHEN que les actions de déclenchement peuvent accéder aux éléments de la ligne en cours d'insertion, de suppression ou de mise à jour à l'aide des références du formulaire NEW.column-name et OLD.column-name, où nom-colonne est le nom d'une colonne de la table à laquelle le déclencheur est associé.

• Si une clause WHEN est fournie, les instructions PostgreSQL spécifiées ne sont exécutées que pour les lignes pour lesquelles la clause WHEN est vraie. Si aucune clause WHEN n'est fournie, les instructions PostgreSQL sont exécutées pour toutes les lignes.

• Si plusieurs déclencheurs du même type sont définis pour le même événement, ils seront déclenchés dans l'ordre alphabétique par nom.

• Le mot clé BEFORE, AFTER ou INSTEAD OF détermine quand les actions de déclenchement seront exécutées par rapport à l'insertion, la modification ou la suppression de la ligne associée.

• Les déclencheurs sont automatiquement supprimés lorsque la table à laquelle ils sont associés est supprimé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 il faut simplement utiliser tablename, ne pas database.tablename.

• Une option CONSTRAINT lorsqu'elle est spécifiée crée un déclencheur de contrainte . C'est la même chose qu'un déclencheur normal sauf que le moment du déclenchement du déclencheur peut être ajusté à l'aide de SET CONSTRAINTS. Les déclencheurs de contraintes sont censés déclencher une exception lorsque les contraintes qu'ils implémentent sont violées.

Syntaxe

La syntaxe de base de la création d'un trigger est comme suit -

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];

Ici, event_namepeut être une opération de base de données INSERT, DELETE, UPDATE et TRUNCATE sur la table mentionnéetable_name. Vous pouvez éventuellement spécifier FOR EACH ROW après le nom de la table.

Voici la syntaxe de création d'un déclencheur sur une opération UPDATE sur une ou plusieurs colonnes spécifiées d'une table comme suit:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic goes here....
];

Exemple

Prenons un cas où nous voulons garder un essai d'audit pour chaque enregistrement inséré dans la table COMPANY, que nous créerons comme suit (Supprimez la table COMPANY si vous l'avez déjà).

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

Pour garder un essai d'audit, nous allons créer une nouvelle table appelée AUDIT où les messages du journal seront insérés chaque fois qu'il y a une entrée dans la table COMPANY pour un nouvel enregistrement -

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

Ici, ID est l'ID d'enregistrement AUDIT, et EMP_ID est l'ID, qui proviendra de la table COMPANY, et DATE conservera l'horodatage lorsque l'enregistrement sera créé dans la table COMPANY. Alors maintenant, créons un déclencheur sur la table COMPANY comme suit -

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

Où auditlogfunc () est un PostgreSQL procedure et a la définition 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, nous allons commencer le travail proprement dit. Commençons par insérer un enregistrement dans la table COMPANY, ce qui devrait entraîner la création d'un enregistrement de journal d'audit dans la table AUDIT. Alors créons un enregistrement dans la table COMPANY comme suit -

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

Cela créera un enregistrement dans la table COMPANY, qui est comme suit -

id | name | age | address      | salary
----+------+-----+--------------+--------
  1 | Paul |  32 | California   |  20000

En même temps, un enregistrement sera créé dans la table AUDIT. Cet enregistrement est le résultat d'un déclencheur, que nous avons créé lors de l'opération INSERT sur la table COMPANY. De même, vous pouvez créer vos déclencheurs sur les opérations UPDATE et DELETE en fonction de vos besoins.

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

Liste des TRIGGERS

Vous pouvez lister tous les déclencheurs de la base de données actuelle à partir de pg_trigger tableau comme suit -

testdb=# SELECT * FROM pg_trigger;

L'instruction PostgreSQL ci-dessus répertoriera tous les déclencheurs.

Si vous souhaitez répertorier les déclencheurs sur une table particulière, utilisez la clause AND avec le nom de la table comme suit -

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

L'instruction PostgreSQL donnée ci-dessus listera également une seule entrée comme suit -

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

Abandonner les TRIGGERS

Voici la commande DROP, qui peut être utilisée pour supprimer un déclencheur existant -

testdb=# DROP TRIGGER trigger_name;