IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

CREATE TRIGGER

CREATE TRIGGER — Définir un nouveau déclencheur

Synopsis

CREATE [ CONSTRAINT ] TRIGGER nom { BEFORE | AFTER | INSTEAD OF } { événement [ OR ... ] }
    ON nom_table
    [ FROM nom_table_referencee ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] nom_relation_transition } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE nom_fonction ( arguments )

événement fait partie de :

    INSERT
    UPDATE [ OF nom_colonne [, ... ] ]
    DELETE
    TRUNCATE
  

Description

CREATE TRIGGER crée un nouveau déclencheur. Le déclencheur est associé à la table, à la vue ou à la table distante 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) ; ou bien en remplacement de l'opération (dans le cas d'opérations INSERT, UPDATE ou DELETE sur une vue). Si le déclencheur est lancé avant l'événement ou en remplacement de 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 celles effectuées par les autres déclencheurs, 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). Notez qu'avec un INSERT utilisant une clause ON CONFLICT DO UPDATE, un trigger au niveau requête sera exécuté pour un INSERT ou un UPDATE.

Les déclencheurs 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 déclencheurs BEFORE et AFTER portant sur des vues devront quant à eux être marqués FOR EACH STATEMENT.

Les déclencheurs 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 déclencheurs 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
AFTER INSERT/UPDATE/DELETE Tables et tables distantes Tables, vues et tables distantes
TRUNCATE -- Tables
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 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.

Lorsque l'option CONSTRAINT est spécifiée, cette commande crée un déclencheur contrainte. Ce nouvel objet est identique aux déclencheurs normaux excepté le fait que le moment de déclenchement peut alors être ajusté via l'utilisation de SET CONSTRAINTS(7). Les déclencheurs contraintes ne peuvent être que de type AFTER ROW sur des tables. 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 déclencheur différé peut également être forcée en utilisant l'option SET CONSTRAINTS. Le comportement attendu des déclencheurs contraintes est de générer une exception en cas de violation de la contrainte qu'ils implémentent.

L'option REFERENCING est uniquement supportée pour un déclencheur AFTER qui n'est pas un déclencheur sur contrainte. OLD TABLE ne peut être spécifié qu'une seule fois, et uniquement sur un déclencheur qui peut se déclencher sur UPDATE ou DELETE. NEW TABLE ne peut être spécifiée qu'une seule fois, et uniquement sur un déclencheur qui peut se déclencher sur UPDATE ou INSERT

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 38, Déclencheurs (triggers) présente de plus amples informations sur les déclencheurs.

Paramètres

nom

Le nom du nouveau déclencheur. Il doit être distinct du nom de tout autre déclencheur sur la table. Le nom ne peut pas être qualifié d'un nom de schéma, le déclencheur héritant du schéma de sa table. Pour un déclencheur 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 déclencheur 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 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 commande UPDATE.

Les événements INSTEAD OF UPDATE ne supportent pas de listes de colonnes.

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 déclencheur.

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 déclencheurs contraintes.

DEFERRABLE, NOT DEFERRABLE, INITIALLY IMMEDIATE, INITIALLY DEFERRED

La spécification du moment de déclenchement par défaut. Voir la partie CREATE TABLE(7) pour plus de détails sur cette option. Elle ne peut être spécifiée que pour les déclencheurs contraintes.

REFERENCING

Cela précède immédiatement la déclaration d'une ou deux relations qui peuvent être utilisée pour lire les images avant et/ou après de toutes les lignes directement affectées par la commande déclencheuse. Un déclencheur AFTER EACH ROW est autorisué à utiliser à la fois ces relation nommées de transition ainsi que les noms de lignes (OLD et NEW) qui référencent chaque ligne individuellement pour lesquelles le déclencheur se déclenche.

OLD TABLE, NEW TABLE

Cela spécifié si les relatiopn nommées contient les images avant ou après modification des lignes qui sont affectées par la commande ayant déclenchée le déclencheur.

nom_relation_transition

Le nom (non qualifié) à utiliser au sein du déclencheur pour cette relation.

FOR EACH ROW, FOR EACH STATEMENT

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. Constraint triggers can only be specified 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.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.

Les déclencheurs INSTEAD OF ne supportent pas de condition WHEN.

Actuellement, les expressions WHEN ne peuvent pas contenir de sous-requêtes.

À noter que pour les déclencheurs 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 déclencheur 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 déclencheur.

arguments

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.

Notes

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 (définie en utilisant la syntaxe 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 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 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.

Modifier une table partitionnée ou une table avec des enfants héritées déclenche les triggers au niveau requête directement attachés à cette table, 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 toutes les partitions affectées et pour toutes les tables filles. 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 au niveau ligne avec des relations de transition ne peuvent être définis sur les partitions ou les tables filles.

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.

Exemples

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();
   

É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 PROCEDURE 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 PROCEDURE 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 38.4, « Un exemple complet de trigger » contient un exemple complet d'une fonction trigger écrit en C.

Compatibilité

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 déclencheurs AFTER triggers sont spécifiés en utilisant la clause REFERENCING de la manière standard, les variables de lignes utilisées dans les déclencheurs 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 déclencheur est écrite. Certains langages se comportent effectivement comme s'il y avait une clause REFERENCING contenant OLD ROW AS OLD NEW ROW AS NEW.

  • 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 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 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, tout comme la possibilité de définir des déclencheurs de niveau instruction sur des vues.

CREATE CONSTRAINT TRIGGER est une extension spécifique à PostgreSQL™ du standard SQL.

Voir aussi

ALTER TRIGGER(7), DROP TRIGGER(7), SET CONSTRAINTS(7), CREATE FUNCTION(7)