CREATE TRIGGER — Définir un nouveau déclencheur
CREATE TEXT SEARCH TEMPLATE
CREATE TRIGGER nom { BEFORE | AFTER } { evenement [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE nom_fonction ( arguments )
CREATE TRIGGER crée un nouveau déclencheur. Le déclencheur est associé à la table spécifiée et exécute la fonction nom_fonction lorsque certains événements surviennent.
L'appel du déclencheur 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). Si le déclencheur est lancé avant l'événement, le déclencheur 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 déclencheur est activé après l'événement, toute modification, dont les autres triggers, est « visible » par le déclencheur.
Un déclencheur 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 déclencheur ON DELETE sur la relation cible, une fois par ligne supprimée. Au contraire, un déclencheur 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 déclencheurs FOR EACH STATEMENT applicables).
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.
De plus, la définition d'un trigger peut spécifier une condition WHEN qui sera testée pour voir 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 déclencheurs 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.
SELECT ne modifie aucune ligne ; la création de déclencheurs sur SELECT n'est donc pas possible. Les règles et vues sont plus appropriées dans ce cas.
Chapitre 36, Déclencheurs (triggers) présente de plus amples informations sur les déclencheurs.
Le nom du nouveau déclencheur. Il doit être distinct du nom de tout autre déclencheur sur la table.
Détermine si la fonction est appelée avant ou après l'événement.
Peut-être INSERT, UPDATE ou DELETE ou TRUNCATE ; précise l'événement qui active le déclencheur. Plusieurs événements peuvent être précisés en les séparant par OR.
Pour les triggers se déclenchant suite à un UPDATE, il est possible de spécifier une liste de colonnes utilisant cette syntaxe :
UPDATE OF nom_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 mise à jour.
Le nom (éventuellement qualifié du nom du schéma) de la table à laquelle est rattaché le déclencheur.
Précise si la procédure du déclencheur 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.
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.nom_colonne ou NEW.nom_colonne, respectivement. Bien sûr, le triggers sur INSERT ne peuvent pas faire référence à OLD et ceux sur DELETE ne peuvent pas faire référence à NEW.
Actuellement, les expressions WHEN ne peuvent pas contenir de sous-requêtes.
Une fonction utilisateur, déclarée sans argument et renvoyant le type trigger, exécutée à l'activation du déclencheur.
Une liste optionnelle d'arguments séparés par des virgules à fournir à la fonction lors de l'activation du déclencheur. 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 un déclencheur 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(7) pour supprimer un déclencheur.
Un trigger sur colonne spécifique (FOR UPDATE OF nom_colonne) se déclenchera quand une des colonnes indiquées est listée comme cible de la liste SET pour la commande UPDATE. Il est possible qu'une valeur de colonne change même si le trigger n'est pas déclenché parceque 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 changera 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 l'oid), 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 les versions de PostgreSQL™ antérieures à la 7.3, il était nécessaire de déclarer un type opaque de retour pour les fonctions déclencheur, plutôt que trigger. Pour pouvoir charger d'anciens fichiers de sauvegarde, CREATE TRIGGER accepte qu'une fonction déclare une valeur de retour de type opaque, mais il affiche un message d'avertissement et change le type de retour déclaré en trigger.
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 PROCEDURE check_account_update();
Idem, mais avec une exécution de la fonction seulement si la colonne balance est spécifiée comme cible de la commande UPDATE :
CREATE TRIGGER check_update
BEFORE UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE PROCEDURE 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 PROCEDURE 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 PROCEDURE log_account_update();
Section 36.4, « Un exemple complet de trigger » 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 :
SQL permet de définir des alias pour les lignes « old » et « new » ou pour les tables utilisée dans la définition des actions déclenchées (c'est-à-dire CREATE TRIGGER ... ON nomtable REFERENCING OLD ROW AS unnom NEW ROW AS unautrenom...). PostgreSQL™ autorise l'écriture de procédures de déclencheurs dans tout langage l'utilisateur. De ce fait, l'accès aux données est géré spécifiquement pour chaque langage.
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 déclencheurs multiples. PostgreSQL™ utilise l'ordre alphabétique de leur nom, jugé plus pratique.
Le standard SQL précise que les déclencheurs 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 prévisible 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 déclencheur avec OR est une extension PostgreSQL™.
La possibilité d'exécuter un trigger suite à une commande TRUNCATE est une extension PostgreSQL™ du standard SQL.