Free Press

Un « déclencheur » est défini comme tout événement qui met en mouvement une action.

Dans PostgreSQL, si vous voulez prendre des mesures sur des événements spécifiques de la base de données, tels que INSERT, UPDATE, DELETE, ou TRUNCATE, alors la fonctionnalité de déclenchement peut être utile car elle invoquera la fonction requise sur les événements définis.

Le déclencheur sera associé à la table, la vue ou la table étrangère spécifiée et exécutera la fonction spécifiée lorsque certaines opérations sont effectuées sur cette table. Selon l’exigence, nous pouvons créer un déclencheur AVANT, APRÈS ou AU-DESSUS des événements/opérations.

Types de déclencheurs

1. Déclencheur au niveau de la rangée : Si le déclencheur est marqué FOR EACH ROW alors la fonction de déclenchement sera appelée pour chaque ligne qui se fait modifier par l’événement.

Par exemple : Si nous mettons à jour 100 lignes dans la table, la fonction de déclenchement UPDATE sera appelée 100 fois, une fois pour chaque ligne mise à jour.

2. Déclencheur au niveau de l’énoncé : L’option FOR EACH STATEMENT n’appellera la fonction de déclenchement qu’une seule fois pour chaque déclaration, quel que soit le nombre de lignes qui se modifient.

Création d’un déclencheur

La commande SQL CREATE TRIGGER crée un déclencheur sur l’objet spécifié.

La syntaxe de la commande CREATE TRIGGER est la suivante :

CREATE TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event }ON table_name ] transition_relation_name } ] { ROW | STATEMENT } ]EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments ) where event can be one of: INSERTUPDATE ]DELETETRUNCATE

Pour une description plus détaillée et les options disponibles, consultez la documentation PostgreSQL à https://www.postgresql.org/docs/12/sql-createtrigger.html.

1. Déclencheur d’événement INSERT

Le déclencheur d’événement INSERT est appelé lorsqu’un nouvel enregistrement est ajouté à une table en utilisant l’instruction INSERT.

Exemple

Regardons un exemple de création d’un nouveau déclencheur. Dans cet exemple, nous allons créer une nouvelle table nommée « Employee » comme suit :

CREATE TABLE "Employee"("EmployeeId" INT NOT NULL,"LastName" VARCHAR(20) NOT NULL,"FirstName" VARCHAR(20) NOT NULL,"Title" VARCHAR(30),"ReportsTo" INT,"BirthDate" TIMESTAMP,"HireDate" TIMESTAMP,"Address" VARCHAR(70),"City" VARCHAR(40),"State" VARCHAR(40),"Country" VARCHAR(40),"PostalCode" VARCHAR(10),"Phone" VARCHAR(24),"Fax" VARCHAR(24),"Email" VARCHAR(60),CONSTRAINT "PK_Employee" PRIMARY KEY ("EmployeeId"));CREATE TABLE "Employee_Audit"("EmployeeId" INT NOT NULL,"LastName" VARCHAR(20) NOT NULL,"FirstName" VARCHAR(20) NOT NULL,"UserName" VARCHAR(20) NOT NULL,"EmpAdditionTime" VARCHAR(20) NOT NULL,);

Nous allons créer un déclencheur qui peut ajouter une entrée dans la table « Employee_Audit » si un nouvel enregistrement d’employé est inséré dans la table « Employee ». Nous allons enregistrer les détails du nom d’utilisateur, qui va ajouter l’enregistrement dans la table « Employee ».

Ajouter une fonction de déclencheur et la commande CREATE TRIGGER:

CREATE OR REPLACE FUNCTION employee_insert_trigger_fnc() RETURNS trigger AS$$BEGIN INSERT INTO "Employee_Audit" ( "EmployeeId", "LastName", "FirstName","UserName" ,"EmpAdditionTime") VALUES(NEW."EmployeeId",NEW."LastName",NEW."FirstName",current_user,current_date);RETURN NEW;END;$$LANGUAGE 'plpgsql';CREATE TRIGGER employee_insert_trigger AFTER INSERT ON "Employee" FOR EACH ROW EXECUTE PROCEDURE employee_insert_trigger_fnc();

Une fois que nous créons le déclencheur INSERT ci-dessus sur la table, il ajoutera une nouvelle entrée à la table « Employee_Audit » avec ces détails:

trigger_demo=# INSERT INTO "Employee" VALUES(10,' Adams','Andrew','Manager',1,'1962-02-18 00:00:00','2010-08-14 00:00:00','11120 Jasper Ave NW','Edmonton','AB','Canada','T5K 2N1','+1 780 428-9482','+1 780 428-3457','[email protected]');INSERT 0 1trigger_demo=# select * from "Employee" where "EmployeeId" =10;--------------------EmployeeId | 10LastName | AdamsFirstName | AndrewTitle | ManagerReportsTo | 1BirthDate | 1962-02-18 00:00:00HireDate | 2010-08-14 00:00:00Address | 11120 Jasper Ave NWCity | EdmontonState | ABCountry | CanadaPostalCode | T5K 2N1Phone | +1 780 428-9482Fax | +1 780 428-3457Email | [email protected]_demo=#trigger_demo=# select * from "Employee_Audit" ;----+-----------EmployeeId | 10LastName | AdamsFirstName | AndrewUserName | postgresEmpAdditionTime | 2019-11-10

2. Déclencheur d’événement UPDATE

Le déclencheur d’événement UPDATE est appelé au moment de l’exécution de l’instruction UPDATE.

Exemple

CREATE TRIGGER verify_user_for_update BEFORE UPDATE ON "Employee" FOR EACH ROWEXECUTE PROCEDURE employee_verify_user_priv();

3. Déclencheur d’événement DELETE

Voici le déclencheur d’événement DELETE, qui peut être ajouté sur les transactions qui SUPPRIMENT les enregistrements.

Exemple

CREATE TRIGGER employee_delete_trigger AFTER DELETE ON "Employee" FOR EACH ROW EXECUTE PROCEDURE aft_delete();

Suppression d’un déclencheur

DROP TRIGGER est utilisé pour supprimer un déclencheur. La syntaxe est très simple.

drop trigger employee_insert_trigger on "Employee" ;

Usages des déclencheurs

1. Audit : Vous pouvez utiliser les déclencheurs pour suivre les transactions de la table en enregistrant les détails de l’événement.

2. Forcer la contrainte de vérification : Vous pouvez créer un déclencheur par lequel vous pouvez vérifier les contraintes avant d’appliquer la transaction à la table.

3. Population automatique : En utilisant les déclencheurs, vous pouvez également auto-peupler les champs des tables par la manipulation de nouveaux enregistrements de transactions.

Points importants à retenir

1. Pour créer un déclencheur sur une table, l’utilisateur doit avoir le privilège TRIGGER sur la table et le privilège EXECUTE sur la fonction de déclenchement.

2. Vous pouvez vérifier le catalogue système « pg_trigger » pour les informations de déclenchement existantes dans la base de données.

3. Si vous créez plusieurs déclencheurs sur le même objet pour le même événement, ces déclencheurs seront déclenchés par ordre alphabétique de nom.

4.