PostgreSQL – TRIGGERS

PostgreSQL Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs.

Voici les points importants concernant les déclencheurs PostgreSQL −

• Le déclencheur PostgreSQL peut être spécifié pour 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 l’INSERTION, la MISE À JOUR ou la SUPPRESSION soit tentée)

  • Une fois l’opération terminée (après que les contraintes sont vérifiées et que l’INSERTION, la MISE À JOUR ou la SUPPRESSION soient terminées)

  • 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 qui l’opération se modifie. En revanche, un déclencheur marqué POUR CHAQUE INSTRUCTION ne s’exécute qu’une seule fois pour une opération donnée, quel que soit le nombre de lignes qu’il modifie.

• Les deux, la clause WHEN et les actions de déclenchement, peuvent accéder à des éléments de la ligne insérée, supprimée ou mise à jour à l’aide de références du formulaire NEW.nom de la colonne et ANCIEN.nom de colonne, où nom de 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 utiliser simplement tablename, pas database.nom de la table.

• Une option de CONTRAINTE 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 CONTRAINTES DÉFINIES. 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 déclencheur est la suivante −

CREATE TRIGGER trigger_name event_nameON table_name;

Ici, event_name peut être une opération d’INSERTION, de SUPPRESSION, de MISE À JOUR et de TRONCATURE de la base de données sur la table nom_de table mentionnée. Vous pouvez éventuellement spécifier POUR CHAQUE LIGNE après le nom de la table.

Voici la syntaxe de création d’un déclencheur lors d’une opération de MISE À JOUR sur une ou plusieurs colonnes spécifiées d’une table comme suit −

CREATE TRIGGER trigger_name UPDATE OF column_nameON table_name;

Exemple

Considérons un cas où nous voulons conserver un essai d’audit pour chaque enregistrement inséré dans la table COMPANY, que nous créerons nouvellement comme suit (Déposez 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 conserver l’essai d’audit, nous allons créer une nouvelle table appelée AUDIT où des messages de journal seront insérés chaque fois qu’il y aura une entrée dans la table de l’ENTREPRISE pour un nouvel enregistrement −

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

Ici, ID est l’ID de l’enregistrement d’AUDIT et EMP_ID est l’ID, qui proviendra de la table de l’ENTREPRISE, et DATE gardera l’horodatage lorsque l’enregistrement sera créé dans la table de l’ENTREPRISE. Alors maintenant, créons un déclencheur sur la table de l’ENTREPRISE comme suit –

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

Où auditlogfunc() est une procédure PostgreSQL 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 réel. Commençons par insérer un enregistrement dans la table de l’ENTREPRISE, ce qui devrait entraîner la création d’un enregistrement de journal d’audit dans la table d’AUDIT. Créons donc un enregistrement dans la table de l’ENTREPRISE 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 de l’ENTREPRISE, qui est la suivante −

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

En même temps, un enregistrement sera créé dans la table d’AUDIT. Cet enregistrement est le résultat d’un déclencheur, que nous avons créé lors de l’opération d’INSERTION sur la table de l’ENTREPRISE. De même, vous pouvez créer vos déclencheurs lors des opérations de MISE À JOUR et de SUPPRESSION en fonction de vos besoins.

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

Liste DES DÉCLENCHEURS

Vous pouvez répertorier tous les déclencheurs de la base de données actuelle à partir de la table pg_trigger comme suit −

testdb=# SELECT * FROM pg_trigger;

L’instruction PostgreSQL ci-dessus répertorie tous les déclencheurs.

Si vous souhaitez lister 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 ci−dessus répertorie également une seule entrée comme suit −

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

DÉCLENCHEURS de suppression

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

testdb=# DROP TRIGGER trigger_name;