CREATE TRIGGER — Définir un nouveau trigger
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGERnom
{ BEFORE | AFTER | INSTEAD OF } {événement
[ OR ... ] } ONnom_table
[ FROMnom_table_referencee
] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ REFERENCING { { OLD | NEW } TABLE [ AS ]nom_relation_transition
} [ ... ] ] [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN (condition
) ] EXECUTE { FUNCTION | PROCEDURE }nom_fonction
(arguments
) oùévénement
fait partie de : INSERT UPDATE [ OFnom_colonne
[, ... ] ] DELETE TRUNCATE
CREATE TRIGGER
crée un nouveau trigger.
CREATE OR REPLACE TRIGGER
va soit créer un nouveau
trigger, soit remplacer un trigger existant. Le trigger est associé à
la table, à la vue ou à la table distante spécifiée et exécute la fonction
nom_fonction
lorsque
certaines opérations sont réalisées sur cette table.
Pour remplacer la définition actuelle d'un trigger existant, utilisez
CREATE OR REPLACE TRIGGER
, en indiquant le nom du
trigger existant et la table parent. Toutes les autres propriétés sont
remplacés.
L'appel du trigger peut avoir lieu avant que l'opération
ne soit tentée sur une ligne (avant la vérification des contraintes et
la tentative d'INSERT
, UPDATE
ou
DELETE
) ou une fois que l'opération est
terminée (après la vérification des contraintes et la fin de la commande
INSERT
, UPDATE
ou
DELETE
) ; ou bien en remplacement de l'opération
(dans le cas d'opérations INSERT, UPDATE ou DELETE sur une vue). Si le trigger est lancé avant
l'événement ou en remplacement de l'événement, le trigger peut ignorer l'opération sur la ligne
courante ou modifier la ligne en cours d'insertion (uniquement pour les
opérations INSERT
et UPDATE
). Si le
trigger est activé après l'événement, toute modification, dont celles effectuées par les
autres triggers, est « visible »
par le trigger.
Un trigger marqué FOR EACH ROW
est appelé pour
chaque ligne que l'opération modifie. Par exemple, un
DELETE
affectant dix lignes entraîne dix appels
distincts de tout trigger ON DELETE
sur la relation
cible, une fois par ligne supprimée. Au contraire, un trigger marqué
FOR EACH STATEMENT
ne s'exécute qu'une fois pour une
opération donnée, quelque soit le nombre de lignes modifiées (en
particulier, une opération qui ne modifie aucune ligne résulte toujours en
l'exécution des triggers FOR EACH STATEMENT
applicables).
Les triggers définis en remplacement (INSTEAD OF
) doivent obligatoirement être marqués
FOR EACH ROW
, et ne peuvent être définis que sur des vues.
Les triggers BEFORE
et AFTER
portant sur des vues
devront quant à eux être marqués FOR EACH STATEMENT
.
Les triggers peuvent également être définis pour l'événement TRUNCATE
,
mais ne pourront, dans ce cas, qu'être marqués FOR EACH STATEMENT
.
Le tableau suivant récapitule quels types de triggers peuvent être utilisés sur les tables, les vues et les tables distantes :
Déclenchement | Événement | Niveau ligne | Niveau instruction |
---|---|---|---|
BEFORE | INSERT /UPDATE /DELETE | Tables et tables distantes | Tables, vues et tables distantes |
TRUNCATE | -- | Tables et tables distantes | |
AFTER | INSERT /UPDATE /DELETE | Tables et tables distantes | Tables, vues et tables distantes |
TRUNCATE | -- | Tables et tables distantes | |
INSTEAD OF | INSERT /UPDATE /DELETE | Vues | -- |
TRUNCATE | -- | -- |
De plus, les triggers peuvent être définis pour être déclenchés suite à
l'exécution d'un TRUNCATE
, mais seulement dans le cas d'un
trigger FOR EACH STATEMENT
.
En outre, la définition d'un trigger peut spécifier une condition
WHEN
qui sera testée pour vérifier si le trigger doit
réellement être déclenché. Dans les triggers au niveau ligne, la condition
WHEN
peut examiner l'ancienne et/ou la nouvelle valeurs
des colonnes de la ligne. Les triggers au niveau instruction peuvent aussi
avoir des conditions WHEN
, bien que la fonctionnalité
n'est pas aussi utile pour elles car la condition ne peut pas faire
référence aux valeurs de la table.
Si plusieurs triggers du même genre sont définis pour le même événement, ils sont déclenchés suivant l'ordre alphabétique de leur nom.
Lorsque l'option CONSTRAINT
est spécifiée, cette
commande crée un trigger contrainte.
Ce nouvel
objet est identique aux triggers normaux excepté le fait que le moment
de déclenchement peut alors être ajusté via l'utilisation de SET CONSTRAINTS
.
Les triggers contraintes ne peuvent
être que de type AFTER ROW
sur des tables standards (pas
des tables distantes). Ils peuvent être déclenchés soit à la fin de
l'instruction causant l'événement, soit à la fin de la transaction ayant
contenu l'instruction de déclenchement ; dans ce dernier cas, ils sont
alors définis comme différés. L'exécution d'un
trigger différé peut également être forcée en utilisant l'option
SET CONSTRAINTS
. Le comportement attendu des
triggers contraintes est de générer une exception en cas de violation
de la contrainte qu'ils implémentent.
L'option REFERENCING
active la récupération des
relations de transition, qui sont des ensembles de
lignes incluant toutes les lignes insérées, supprimées ou modifiées par
l'instruction SQL en cours. Cette fonctionnalité donne au trigger une vue
globale de ce qu'a réalisé l'instruction, et non pas une vue ligne par
ligne. Cette option est seulement autorisée pour un trigger
AFTER
qui n'est pas un trigger de contrainte. De plus,
si le trigger est un trigger UPDATE
, il ne doit pas
indiquer une liste de nom_colonne
. OLD TABLE
peut seulement être indiqué une fois, et seulement pour un trigger qui est
déclenché par un UPDATE
ou un
DELETE
; il crée une relation de transition
contenant les images-avant de toutes les lignes
mises à jour ou supprimées par l'instruction. De la même façon,
NEW TABLE
ne peut être indiqué qu'une seule fois, et
seulement pour un trigger déclenché par un UPDATE
ou un
INSERT
; il crée une relation de transition
contenant les images-après de toutes les lignes
mises à jour ou insérées par l'instruction.
SELECT
ne modifie aucune ligne ; la création de
triggers sur SELECT
n'est donc pas possible. Les
règles et vues peuvent fournir des solutions fonctionnelles aux problèmes
qui nécessitent des triggers sur SELECT
.
Chapitre 39 présente de plus amples informations sur les triggers.
nom
Le nom du nouveau trigger. Il doit être distinct du nom de
tout autre trigger sur la table.
Le nom ne peut pas être qualifié d'un nom de schéma, le trigger héritant
du schéma de sa table. Pour un trigger contrainte, c'est également le nom à
utiliser lorsqu'il s'agira de modifier son comportement via la commande
SET CONSTRAINTS
.
BEFORE
AFTER
INSTEAD OF
Détermine si la fonction est appelée avant, après ou en remplacement de l'événement.
Un trigger contrainte ne peut être spécifié qu'AFTER
.
événement
Peut-être INSERT
, UPDATE
ou
DELETE
ou TRUNCATE
; précise
l'événement qui active le trigger. Plusieurs événements peuvent être
précisés en les séparant par OR
, sauf quand les
tables de transitions sont demandées.
Pour les triggers se déclenchant suite à un UPDATE
,
il est possible de spécifier une liste de colonnes utilisant cette
syntaxe :
UPDATE OFnom_colonne_1
[,nom_colonne_2
... ]
Le trigger se déclenchera seulement si au moins une des colonnes listées
est mentionnée comme cible de la commande UPDATE
ou si une des colonnes listées est une colonne générée qui dépend
d'une colonne cible d'une commande UPDATE
.
Les événements INSTEAD OF UPDATE
n'acceptent pas de
listes de colonnes. Une liste de colonnes ne peut pas être indiquée
lorsque les tables de transition sont nécessaires.
nom_table
Le nom (éventuellement qualifié du nom du schéma) de la table, de la vue ou de la table distante à laquelle est rattaché le trigger.
nom_table_referencee
Le nom d'une autre table (possiblement qualifiée par un nom de schéma) référencée par la contrainte. Cette option est à utiliser pour les contraintes de clés étrangères et n'est pas recommandée pour d'autres types d'utilisation. Elle ne peut être spécifiée que pour les triggers contraintes.
DEFERRABLE
NOT DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED
La spécification du moment de déclenchement par défaut. Voir la partie CREATE TABLE pour plus de détails sur cette option. Elle ne peut être spécifiée que pour les triggers contraintes.
REFERENCING
Ce mot-clé précède immédiatement la déclaration d'une ou deux noms de table fournissant l'accès aux relations de transition de l'instruction trigger.
OLD TABLE
NEW TABLE
Cette clause indique si le nom de la relation suivante est pour la relation de transition précédente ou suivante.
nom_relation_transition
Le nom (non qualifié) à utiliser au sein du trigger pour cette relation de transition.
FOR EACH ROW
FOR EACH STATEMENT
Précise si la fonction trigger doit être lancée
pour chaque ligne affectée par l'événement ou
simplement pour chaque instruction SQL. FOR EACH STATEMENT
est la valeur par défaut. Les triggers de contrainte ne peuvent être
spécifiés que pour FOR EACH ROW
.
condition
Une expression booléenne qui détermine si la fonction trigger sera
réellement exécutée. Si WHEN
est indiqué, la fonction
sera seulement appelée si la condition
renvoie true
.
Pour les triggers FOR EACH ROW
, la condition
WHEN
peut faire référence aux valeurs des colonnes
des ancienne et nouvelle lignes en utilisant la notation
OLD.
ou
nom_colonne
NEW.
, respectivement.
Bien sûr, le triggers sur nom_colonne
INSERT
ne peuvent pas
faire référence à OLD
et ceux sur
DELETE
ne peuvent pas faire référence à
NEW
.
Les triggers INSTEAD OF
ne supportent pas de condition
WHEN
.
Actuellement, les expressions WHEN
ne peuvent pas
contenir de sous-requêtes.
À noter que pour les triggers contraintes, l'évaluation de la clause
WHEN
n'est pas différée mais intervient immédiatement
après que l'opération de mise à jour de la ligne soit effectuée. Si la
condition n'est pas évaluée à vrai, alors le trigger n'est pas placé
dans la file d'attente des exécutions différées.
nom_fonction
Une fonction utilisateur, déclarée sans argument et renvoyant le type
trigger
, exécutée à l'activation du trigger.
Dans la syntaxe de CREATE TRIGGER
, les mots-clés
FUNCTION
et PROCEDURE
sont
équivalents mais la fonction référencée doit dans tous les cas être une
fonction, et non pas une procédure. L'utilisation du mot-clé
PROCEDURE
est ici historique et dépréciée.
arguments
Une liste optionnelle d'arguments séparés par des virgules à fournir à la fonction lors de l'activation du trigger. Les arguments sont des chaînes littérales constantes. Il est possible d'écrire ici de simples noms et des constantes numériques mais ils sont tous convertis en chaîne. L'accès aux arguments du trigger depuis la fonction peut différer de l'accès aux arguments d'une fonction standard ; la consultation des caractéristiques d'implantation du langage de la fonction peut alors s'avérer utile.
Pour créer ou remplacer un trigger sur une table, l'utilisateur doit posséder le droit
TRIGGER
sur la table. L'utilisateur doit aussi avoir
le droit EXECUTE
sur la fonction trigger.
Utiliser DROP TRIGGER
pour supprimer un trigger.
Créer un trigger niveau ligne sur une table partitionnée causera la
création d'un trigger « clone » identique sur chacune des
partitions existantes ; et toute partition créée ou attachée après
aura aussi un trigger identique. S'il existe déjà un trigger de même nom
sur une partition, une erreur est renvoyée sauf si CREATE OR
REPLACE TRIGGER
est utilisé, auquel cas ce trigger est remplacé
avec un trigger clone. Quand une partition est détachée de son parent, ses
triggers clone sont supprimés.
Un trigger sur colonne spécifique (définie en utilisant la syntaxe
UPDATE OF
) se
déclenchera quand une des colonnes indiquées est listée comme cible de la
liste
nom_colonne
SET
pour la commande UPDATE
. Il est
possible qu'une valeur de colonne change même si le trigger n'est pas
déclenché parce que les modifications au contenu de la ligne par les triggers
BEFORE UPDATE
ne sont pas pris en compte. De même, une
commande comme UPDATE ... SET x = x ...
déclenchera le
trigger sur la colonne x
, bien que la valeur de cette
colonne ne change pas.
Dans un trigger BEFORE
, la condition
WHEN
est évaluée juste avant l'exécution de la fonction,
donc utiliser WHEN
n'est pas matériellement différent
de tester la même condition au début de la fonction trigger. Notez en
particulier que la ligne NEW
vu par la condition est sa
valeur courante et possiblement modifiée par des triggers précédents. De
plus, la condition WHEN
d'un trigger
BEFORE
n'est pas autorisé à examiner les colonnes
système de la ligne NEW
(comme le ctid
),
car elles n'auront pas encore été initialisées.
Dans un trigger AFTER
, la condition
WHEN
est évaluée juste après la mise à jour de la ligne
et elle détermine si un événement doit déclencher le trigger à la fin de
l'instruction. Donc, quand la condition WHEN
d'un
trigger AFTER
ne renvoie pas true, il n'est pas
nécessaire de préparer un événement ou de relire la ligne à la fin de
l'instruction. Cela peut apporter une amélioration significative des
performances dans les instructions qui modifient de nombreuses lignes, si
le trigger a besoin d'être déclencher pour quelques lignes.
Dans certains cas, il est possible pour une seule commande SQL de
déclencher plus d'un type de trigger. Par exemple, un
INSERT
avec une clause ON CONFLICT DO
UPDATE
peut être la cause du déclenchement d'opérations
d'insertion et de mise à jour, donc il déclenchera l'exécution des deux
types de trigger. Les relations de transition fournies par les triggers
sont spécifique au type de l'événement. Donc un trigger
INSERT
ne verra que les lignes insérées, alors qu'un
UPDATE
ne verra que les lignes mises à jour.
Les mises à jour et suppressions de lignes causées par des actions dûes aux
clés étrangères, comme un ON UPDATE CASCADE
ou un
ON DELETE SET NULL
, sont traitées comme faisant partie
de la commande SQL qui les a causé (notez que ces actions ne sont jamais
différées). Les triggers adéquats sur la table impactée seront déclenchées,
donc cela fournit un autre moyen avec lequel une commande SQL pourrait
déclencher des triggers ne correspondant pas directement à son type. Dans
les cas simples, les triggers demandant les relations de transition verront
tous les changements causés dans leur table par une commande SQL simple
comme une relation de transition unique. Néanmoins, il existe des cas où la
présence d'un trigger AFTER ROW
réclament les relations
de transition causera que les actions des clés étrangères déclenchées par
une commande SQL simple soient séparées en plusieurs étapes, chacune avec
ses propres relations de transition. Dans de tels cas, tout trigger de
niveau instruction présent se déclenchera une fois par ensemble de relation
de transition créé, s'assurant ainsi que les triggers voient bien chaque
ligne affectée dans une seul relation de transition.
Modifier une table partitionnée ou une table avec des enfants héritées
déclenche les triggers au niveau requête attachés à cette table
spécifiquement nommée, mais pas les triggers au niveau requête de ses
partitions ou tables filles. Par contre, les triggers au niveau ligne sont
déclenchés pour pour toutes les partitions et tables enfants affectées. Si
un trigger au niveau requête a été défini avec des relations de
transactions nommées par une clause REFERENCING
, alors
les images avant et après des lignes sont visibles pour toutes les
partitions affectées et pour toutes les tables filles. Dans le cas de
l'héritage, les images de ligne incluent seulement les colonnes présentes
dans la table où le trigger est attaché.
Actuellement, les triggers niveau ligne avec des relations de transition ne
peuvent pas être définis sur des partitions ou des tables d'héritage. De
plus, les triggers sur des tables partitionnées ne peuvent pas être
INSTEAD OF
.
Actuellement, l'option OR REPLACE
n'est pas supportée
par les triggers de contrainte.
Remplacer un trigger existant dans une transaction qui a déjà réalisée des actions de mises à jour sur la table du trigger n'est pas recommandé. Les décisions de déclenchement du trigger, ou des portions de ces décisions, qui ont déjà été réalisées ne seront pas reconsidérées, donc les effets pourraient être surprenant.
Il existe quelques fonctions triggers natives pouvant être utilisées pour résoudre des problèmes communs sans avoir à écrire son propre code trigger ; voir Section 9.28.
Les triggers de niveau instruction sur une vue sont déclenchés uniquement
si l'action sur la vue est géré par un trigger niveau ligne
INSTEAD OF
. Si l'action est gérée par une règle
INSTEAD
, alors toute instruction émise par la règle est
exécutée à la place de l'instruction originale nommant la vue, pour que les
triggers qui seront déclenchés soient ceux des tables nommées dans les
instructions de remplacement. De façon similaire, si la vue est en mise à
jour automatique, alors l'action est gérée en réécrivant automatiquement
l'instruction en une action sur la table de base de la vue pour que les
triggers niveau instruction de la table de base soient déclenchés.
Exécutez la fonction check_account_update
quand une
ligne de la table accounts
est sur le point d'être mise
à jour :
CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update();
Modifiez la définition du trigger pour seulement exécuter la fonction
si la colonne balance
est indiquée comme cible dans
la commande UPDATE
:
CREATE OR REPLACE TRIGGER check_update BEFORE UPDATE OF balance ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update();
Cette forme exécute la fonction seulement si la colonne
balance
a réellement changé de valeur :
CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) EXECUTE FUNCTION check_account_update();
Appelle une fonction pour tracer les mises à jour de la table
accounts
, mais seulement si quelque chose a changé :
CREATE TRIGGER log_update AFTER UPDATE ON accounts FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE FUNCTION log_account_update();
Éxecute la fonction view_insert_row
pour chacune des lignes
à insérer dans la table sous-jacente à la vue my_view :
CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view FOR EACH ROW EXECUTE FUNCTION view_insert_row();
Exécute la fonction check_transfer_balances_to_zero
pour chaque commande pour confirmer que les lignes de
transfert
engendrent un net de zéro :
CREATE TRIGGER transfer_insert AFTER INSERT ON transfer REFERENCING NEW TABLE AS inserted FOR EACH STATEMENT EXECUTE FUNCTION check_transfer_balances_to_zero();
Exécute la fonction check_matching_pairs
pour chaque
ligne pour confirmer que les changement sont fait sur des pairs
correspondantes au même moment (par la même commande) :
CREATE TRIGGER paired_items_update AFTER UPDATE ON paired_items REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab FOR EACH ROW EXECUTE PROCEDURE check_matching_pairs();
Section 39.4 contient un exemple complet d'une fonction trigger écrit en C.
L'instruction CREATE TRIGGER
de
PostgreSQL implante un sous-ensemble du standard
SQL. Les fonctionnalités manquantes sont :
Bien que les tables de transition pour les triggers
AFTER
triggers sont spécifiés en utilisant la clause
REFERENCING
de la manière standard, les variables de
lignes utilisées dans les triggers FOR EACH ROW
peuvent ne pas être spécifiées dans la clause
REFERENCING
. Ils sont disponibles d'une façon qui
dépend du langage dans lequel la fonction trigger est écrite, mais
est fixe sur un langage. Certains langages se comportent effectivement
comme s'il y avait une clause REFERENCING
contenant
OLD ROW AS OLD NEW ROW AS NEW
.
Le standard autorise l'utilisation de tables de transition avec les
triggers UPDATE
spécifique à une colonne mais dans ce
cas, l'ensemble des lignes qui doit être visible dans les tables de
transition dépend de la liste de colonnes du trigger. Ceci n'est pas
encore implémenté dans PostgreSQL.
PostgreSQL n'autorise comme action déclenchée
que l'exécution d'une fonction utilisateur. Le standard SQL, en revanche,
autorise l'exécution d'autres commandes SQL, telles que
CREATE TABLE
. Cette limitation de PostgreSQL
peut être facilement contournée par la création d'une fonction
utilisateur qui exécute les commandes désirées.
Le standard SQL définit l'ordre de création comme ordre de lancement des triggers multiples. PostgreSQL utilise l'ordre alphabétique de leur nom, jugé plus pratique.
Le standard SQL précise que les triggers BEFORE DELETE
sur des
suppressions en cascade se déclenchent après la fin du
DELETE
en cascade.
PostgreSQL définit que BEFORE DELETE
se déclenche toujours avant l'action de suppression, même lors
d'une action en cascade. Cela semble plus cohérent. Il existe aussi un
comportement non standard quand les triggers BEFORE
modifient les lignes ou empêchent les mises à jour causées par une action
référente. Ceci peut amener à des violations de contraintes ou au stockage
de données qui n'honorent pas la contrainte référentielle.
La capacité à préciser plusieurs actions pour un seul trigger avec
OR
est une extension PostgreSQL.
La possibilité d'exécuter un trigger suite à une commande
TRUNCATE
est une extension
PostgreSQL du standard SQL, tout comme la
possibilité de définir des triggers de niveau instruction sur des vues.
CREATE CONSTRAINT TRIGGER
est une extension spécifique à
PostgreSQL du standard SQL.
L'option OR REPLACE
est aussi une extension.