PL/pgSQL peut être utilisé pour définir des
fonctions trigger sur les modifications de données ou sur les événements
en base. Une fonction trigger est créée avec la commande, en la déclarant
comme une fonction sans argument et avec un type en retour
trigger
(pour les triggers sur les modifications de données)
ou event_trigger
(pour les triggers sur les événements en
base). Des variables locales spéciales, nommées
TG_
sont
automatiquement définies pour décrire la condition qui a déclenché l'appel.
quelquechose
Un trigger sur modification de données est
déclaré comme une fonction sans arguments et renvoyant le type
trigger
. Notez que la fonction doit être déclarée sans
arguments même si elle s'attend à recevoir des arguments spécifiés dans
CREATE TRIGGER
-- ce type d'argument est passé via
TG_ARGV
, comme indiqué ci-dessous.
Quand une fonction PL/pgSQL est appelée en tant que trigger, plusieurs variables spéciales sont créées automatiquement dans le bloc de plus haut niveau. Ce sont :
NEW
Type de données RECORD
; variable contenant la nouvelle
ligne de base de données pour les opérations INSERT
/
UPDATE
dans les triggers de niveau ligne. Cette
variable est NULL dans un trigger de niveau instruction et
pour les opérations DELETE
.
OLD
Type de données RECORD
; variable contenant l'ancienne ligne de
base de données pour les opérations UPDATE
/DELETE
dans les triggers de niveau ligne. Cette variable est NULL
dans les triggers de niveau instruction et pour les opérations
INSERT
.
TG_NAME
Type de données name
; variable qui contient le nom du trigger réellement
lancé.
TG_WHEN
Type de données text
; une chaîne, soit BEFORE
soit AFTER
, soit INSTEAD OF
selon la définition du trigger.
TG_LEVEL
Type de données text
; une chaîne, soit ROW
soit
STATEMENT
, selon la définition du trigger.
TG_OP
Type de données text
; une chaîne, INSERT
,
UPDATE
, DELETE
ou
TRUNCATE
indiquant pour quelle opération le trigger a été lancé.
TG_RELID
Type de données oid
; l'ID de l'objet de la table qui a causé
le déclenchement du trigger.
TG_RELNAME
Type de données name
; le nom de la table qui a causé
le déclenchement. C'est obsolète et pourrait disparaître dans une
prochaine version. À la place, utilisez TG_TABLE_NAME
.
TG_TABLE_NAME
Type de données name
; le nom de la table qui a
déclenché le trigger.
TG_TABLE_SCHEMA
Type de données name
; le nom du schéma de la table qui
a appelé le trigger.
TG_NARGS
Type de données integer
; le nombre d'arguments donnés à la fonction
trigger dans l'instruction CREATE TRIGGER
.
TG_ARGV[]
Type de donnée text
; les arguments de l'instruction
CREATE TRIGGER
.
L'index débute à 0. Les indices invalides (inférieurs à 0 ou supérieurs ou égaux
à tg_nargs
) auront une valeur NULL.
Une fonction trigger doit renvoyer soit NULL
soit une
valeur record ayant
exactement la structure de la table pour laquelle le trigger a été lancé.
Les triggers de niveau ligne lancés BEFORE
peuvent renvoyer NULL
pour indiquer au gestionnaire de trigger de sauter le reste de l'opération pour
cette ligne (les triggers suivants ne sont pas lancés, et les
INSERT
/UPDATE
/DELETE
ne se font pas pour cette
ligne). Si une valeur non NULL est renvoyée alors l'opération se déroule avec cette
valeur ligne. Renvoyer une valeur ligne différente de la valeur originale de
NEW
modifie la ligne qui sera insérée ou mise à jour.
De ce fait, si la fonction de trigger veut que l'action réussisse sans modifier la
valeur de rangée, NEW
(ou une valeur égale) doit être
renvoyée. Pour modifier la rangée à être stockée, il est possible de
remplacer les valeurs directement dans NEW
et renvoyer le
NEW
modifié ou de générer un nouvel enregistrement à
renvoyer. Dans le cas d'un before-trigger sur une commande
DELETE
, la valeur renvoyée n'a aucun effet
direct mais doit être non-nulle pour permettre à l'action trigger de
continuer. Notez que NEW
est nul dans le cadre des
triggers DELETE
et que renvoyer ceci n'est pas recommandé
dans les cas courants. Une pratique utile dans des triggers DELETE
serait de renvoyer OLD
.
Les triggers INSTEAD OF
(qui sont toujours des
triggers au niveau ligne et peuvent seulement être utilisés sur des
vues) peuvent renvoyer NULL pour signaler qu'ils n'ont fait aucune
modification et que le reste de l'opération pour cette ligne doit
être ignoré (autrement dit, les triggers suivants ne sont pas
déclenchés et la ligne n'est pas comptée dans le statut des lignes
affectées pour la requête
INSERT
/UPDATE
/DELETE
).
Une valeur différente de NULL doit être renvoyée pour indiquer que
le trigger a traité l'opération demandée. Pour les opérations
INSERT
et UPDATE
, la valeur
de retour doit être NEW
, que la fonction trigger
peut modifier pour supporter une clause RETURNING
d'une requête INSERT
ou UPDATE
(ceci affectera aussi la valeur de ligne passée aux triggers suivants
ou passée à l'alias spécial EXCLUDED
dans une
instruction INSERT
dotée d'une clause ON
CONFLICT DO UPDATE
). Pour les opérations DELETE
,
la valeur de retour doit être OLD
.
La valeur de retour d'un trigger de niveau rangée
déclenché AFTER
ou un trigger de niveau instruction
déclenché BEFORE
ou AFTER
est
toujours ignoré ; il pourrait aussi bien être NULL. Néanmoins, tous les
types de triggers peuvent toujours annuler l'opération complète en
envoyant une erreur.
L'Exemple 43.3 montre un exemple d'une fonction trigger dans PL/pgSQL.
Exemple 43.3. Une fonction trigger PL/pgSQL
Cet exemple de trigger assure qu'à chaque moment où une ligne est insérée ou mise à jour dans la table, le nom de l'utilisateur courant et l'heure sont estampillés dans la ligne. Et cela vous assure qu'un nom d'employé est donné et que le salaire est une valeur positive.
CREATE TABLE emp ( nom_employe text, salaire integer, date_dermodif timestamp, utilisateur_dermodif text ); CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$ BEGIN -- Verifie que nom_employe et salary sont donnés IF NEW.nom_employe IS NULL THEN RAISE EXCEPTION 'nom_employe ne peut pas être NULL'; END IF; IF NEW.salaire IS NULL THEN RAISE EXCEPTION '% ne peut pas avoir un salaire', NEW.nom_employe; END IF; -- Qui travaille pour nous si la personne doit payer pour cela ? IF NEW.salaire < 0 THEN RAISE EXCEPTION '% ne peut pas avoir un salaire négatif', NEW.nom_employe; END IF; -- Rappelons-nous qui a changé le salaire et quand NEW.date_dermodif := current_timestamp; NEW.utilisateur_dermodif := current_user; RETURN NEW; END; $emp_stamp$ LANGUAGE plpgsql; CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE FUNCTION emp_stamp();
Une autre façon de tracer les modifications sur une table implique la création d'une nouvelle table qui contient une ligne pour chaque insertion, mise à jour ou suppression qui survient. Cette approche peut être vue comme un audit des modifications sur une table. L'Exemple 43.4 montre un exemple d'une fonction d'audit par trigger en PL/pgSQL.
Exemple 43.4. Une fonction d'audit par trigger en PL/pgSQL
Cet exemple de trigger nous assure que toute insertion,
modification ou suppression d'une ligne dans la table
emp
est enregistrée dans la
table emp_audit
. L'heure et le nom de l'utilisateur
sont conservées dans la ligne avec le type d'opération réalisé.
CREATE TABLE emp ( nom_employe text NOT NULL, salaire integer ); CREATE TABLE emp_audit( operation char(1) NOT NULL, tampon timestamp NOT NULL, id_utilisateur text NOT NULL, nom_employe text NOT NULL, salaire integer ); CREATE OR REPLACE FUNCTION audit_employe() RETURNS TRIGGER AS $emp_audit$ BEGIN -- -- Ajoute une ligne dans emp_audit pour refléter l'opération réalisée -- sur emp, -- utilise la variable spéciale TG_OP pour cette opération. -- IF (TG_OP = 'DELETE') THEN INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*; END IF; RETURN NULL; -- le résultat est ignoré car il s'agit d'un trigger AFTER END; $emp_audit$ language plpgsql; CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE FUNCTION audit_employe();
Une variation de l'exemple précédent utilise une vue joignant la table principale et la table d'audit pour montrer les derniers enregistrements modifiés. Cette approche enregistre toujours toutes les modifications sur la table mais présente aussi une vue simple de l'audit, n'affichant que le date et heure de la dernière modification pour chaque enregistrement. Exemple 43.5 montre un exemple d'un trigger d'audit sur une vue avec PL/pgSQL.
Exemple 43.5. Une fonction trigger en PL/pgSQL sur une vue pour un audit
Cet exemple utilise un trigger sur une vue pour la rendre
modifiable, et s'assure que toute insertion, mise à jour ou
suppression d'une ligne dans la vue est enregistrée (pour l'audit)
dans la table emp_audit
. La date et l'heure courante ainsi que le
nom de l'utilisateur sont enregistrés, avec le type d'opération
réalisé pour que la vue affiche la date et l'heure de la dernière
modification de chaque ligne.
CREATE TABLE emp ( nom_employe text PRIMARY KEY, salaire integer ); CREATE TABLE emp_audit( operation char(1) NOT NULL, id_utilisateur text NOT NULL, nom_employe text NOT NULL, salaire integer, dmodif timestamp NOT NULL ); CREATE VIEW emp_vue AS SELECT e.nom_employe, e.salaire, max(ea.dmodif) AS derniere_modification FROM emp e LEFT JOIN emp_audit ea ON ea.nom_employe = e.nom_employe GROUP BY 1, 2; CREATE OR REPLACE FUNCTION miseajour_emp_vue() RETURNS TRIGGER AS $$ BEGIN -- -- Perform the required operation on emp, and create a row in emp_audit -- to reflect the change made to emp. -- IF (TG_OP = 'DELETE') THEN DELETE FROM emp WHERE nom_employe = OLD.nom_employe; IF NOT FOUND THEN RETURN NULL; END IF; OLD.derniere_modification = now(); INSERT INTO emp_audit VALUES('D', current_user, OLD.*); RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN UPDATE emp SET salary = NEW.salary WHERE nom_employe = OLD.nom_employe; IF NOT FOUND THEN RETURN NULL; END IF; NEW.derniere_modification = now(); INSERT INTO emp_audit VALUES('U', current_user, NEW.*); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO emp VALUES(NEW.nom_employe, NEW.salaire); NEW.derniere_modification = now(); INSERT INTO emp_audit VALUES('I', current_user, NEW.*); RETURN NEW; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER emp_audit INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_vue FOR EACH ROW EXECUTE FUNCTION miseajour_emp_vue();
Une utilisation des triggers est le maintien d'une table résumée d'une autre table. Le résumé résultant peut être utilisé à la place de la table originale pour certaines requêtes -- souvent avec des temps d'exécution bien réduits. Cette technique est souvent utilisée pour les statistiques de données où les tables de données mesurées ou observées (appelées des tables de faits) peuvent être extrêmement grandes. L'Exemple 43.6 montre un exemple d'une fonction trigger en PL/pgSQL maintenant une table résumée pour une table de faits dans un système de données (data warehouse).
Exemple 43.6. Une fonction trigger PL/pgSQL pour maintenir une table résumée
Le schéma détaillé ici est partiellement basé sur l'exemple du Grocery Store provenant de The Data Warehouse Toolkit par Ralph Kimball.
-- -- Tables principales - dimension du temps de ventes. -- CREATE TABLE time_dimension ( time_key integer NOT NULL, day_of_week integer NOT NULL, day_of_month integer NOT NULL, month integer NOT NULL, quarter integer NOT NULL, year integer NOT NULL ); CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key); CREATE TABLE sales_fact ( time_key integer NOT NULL, product_key integer NOT NULL, store_key integer NOT NULL, amount_sold numeric(12,2) NOT NULL, units_sold integer NOT NULL, amount_cost numeric(12,2) NOT NULL ); CREATE INDEX sales_fact_time ON sales_fact(time_key); -- -- Table résumé - ventes sur le temps. -- CREATE TABLE sales_summary_bytime ( time_key integer NOT NULL, amount_sold numeric(15,2) NOT NULL, units_sold numeric(12) NOT NULL, amount_cost numeric(15,2) NOT NULL ); CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key); -- -- Fonction et trigger pour amender les colonnes résumées -- pour un UPDATE, INSERT, DELETE. -- CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$ DECLARE delta_time_key integer; delta_amount_sold numeric(15,2); delta_units_sold numeric(12); delta_amount_cost numeric(15,2); BEGIN -- Travaille sur l'ajout/la suppression de montant(s). IF (TG_OP = 'DELETE') THEN delta_time_key = OLD.time_key; delta_amount_sold = -1 * OLD.amount_sold; delta_units_sold = -1 * OLD.units_sold; delta_amount_cost = -1 * OLD.amount_cost; ELSIF (TG_OP = 'UPDATE') THEN -- interdit les mises à jour qui modifient time_key - -- (probablement pas trop cher, car DELETE + INSERT est la façon la plus -- probable de réaliser les modifications). IF ( OLD.time_key != NEW.time_key) THEN RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key; END IF; delta_time_key = OLD.time_key; delta_amount_sold = NEW.amount_sold - OLD.amount_sold; delta_units_sold = NEW.units_sold - OLD.units_sold; delta_amount_cost = NEW.amount_cost - OLD.amount_cost; ELSIF (TG_OP = 'INSERT') THEN delta_time_key = NEW.time_key; delta_amount_sold = NEW.amount_sold; delta_units_sold = NEW.units_sold; delta_amount_cost = NEW.amount_cost; END IF; -- Insertion ou mise à jour de la ligne de résumé avec les nouvelles valeurs. <<insert_update>> LOOP UPDATE sales_summary_bytime SET amount_sold = amount_sold + delta_amount_sold, units_sold = units_sold + delta_units_sold, amount_cost = amount_cost + delta_amount_cost WHERE time_key = delta_time_key; EXIT insert_update WHEN found; BEGIN INSERT INTO sales_summary_bytime ( time_key, amount_sold, units_sold, amount_cost) VALUES ( delta_time_key, delta_amount_sold, delta_units_sold, delta_amount_cost ); EXIT insert_update; EXCEPTION WHEN UNIQUE_VIOLATION THEN -- do nothing END; END LOOP insert_update; RETURN NULL; END; $maint_sales_summary_bytime$ LANGUAGE plpgsql; CREATE TRIGGER maint_sales_summary_bytime AFTER INSERT OR UPDATE OR DELETE ON sales_fact FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime(); INSERT INTO sales_fact VALUES(1,1,1,10,3,15); INSERT INTO sales_fact VALUES(1,2,1,20,5,35); INSERT INTO sales_fact VALUES(2,2,1,40,15,135); INSERT INTO sales_fact VALUES(2,3,1,10,1,13); SELECT * FROM sales_summary_bytime; DELETE FROM sales_fact WHERE product_key = 1; SELECT * FROM sales_summary_bytime; UPDATE sales_fact SET units_sold = units_sold * 2; SELECT * FROM sales_summary_bytime;
Les triggers AFTER
peuvent aussi utiliser les
tables de transition pour inspecter l'ensemble
complet de lignes modifiées par l'instruction déclencheur. La commande
CREATE TRIGGER
donne des noms à la ou aux tables de
transition. La fonction peut ensuite se référer à ces noms comme s'il
s'agissait de tables temporaires en lecture seule. Exemple 43.7 montre un exemple.
Exemple 43.7. Auditer avec les tables de transition
Cet exemple produit les mêmes résultats que Exemple 43.4 mais au lieu d'utiliser un
trigger qui se déclenche pour chaque ligne, il utilise un trigger qui se
déclenche une fois par instruction, après avoir récupérer les
informations intéressantes dans une table de transition. Cela peut être
significativement plus rapide que l'approche du trigger par ligne lorsque
l'instruction déclencheur a modifié beaucoup de lignes. Notez qu'il est
nécessaire de faire une déclaration séparée du trigger pour chaque type
d'événement car les clauses REFERENCING
doivent être
différentes dans chaque cas. Mais ceci ne nous empêche pas d'utiliser une
seule fonction trigger si nous le souhaitons. (En pratique, il pourrait
être préférable d'utiliser trois fonctions séparées et d'éviter les tests
à l'exécution sur la variable TG_OP
.)
CREATE TABLE emp ( empname text NOT NULL, salary integer ); CREATE TABLE emp_audit( operation char(1) NOT NULL, stamp timestamp NOT NULL, userid text NOT NULL, empname text NOT NULL, salary integer ); CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$ BEGIN -- -- Create rows in emp_audit to reflect the operations performed on emp, -- making use of the special variable TG_OP to work out the operation. -- IF (TG_OP = 'DELETE') THEN INSERT INTO emp_audit SELECT 'D', now(), user, o.* FROM old_table o; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO emp_audit SELECT 'U', now(), user, n.* FROM new_table n; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO emp_audit SELECT 'I', now(), user, n.* FROM new_table n; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $emp_audit$ LANGUAGE plpgsql; CREATE TRIGGER emp_audit_ins AFTER INSERT ON emp REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit(); CREATE TRIGGER emp_audit_upd AFTER UPDATE ON emp REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit(); CREATE TRIGGER emp_audit_del AFTER DELETE ON emp REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
PL/pgSQL peut être utilisé pour définir des
triggers sur des événements. PostgreSQL requiert
qu'une fonction qui doit appelée en tant que trigger d'événement soit
déclarée sans argument et avec un type event_trigger
en
retour.
Quand une fonction PL/pgSQL est appelée en tant que trigger d'événement, plusieurs variables spéciales sont créées automatiquement dans son bloc de niveau haut. Les voici :
TG_EVENT
Type de données text
; une chaîne représentant
l'événement pour lequel le trigger est déclenché.
TG_TAG
Type de données text
; variable contenant la balise
commande pour laquelle le trigger a été déclenché.
Exemple 43.8 montre un exemple d'une fonction pour un trigger d'événement écrit en PL/pgSQL.
Exemple 43.8. Une fonction PL/pgSQL pour un trigger d'événement
Cet exemple de trigger lève simplement un message NOTICE
à chaque fois qu'une commande supportée est exécutée.
CREATE OR REPLACE FUNCTION rapporte() RETURNS event_trigger AS $$ BEGIN RAISE NOTICE 'rapporte: % %', tg_event, tg_tag; END; $$ LANGUAGE plpgsql; CREATE EVENT TRIGGER rapporte ON ddl_command_start EXECUTE FUNCTION rapporte();