Documentation PostgreSQL 14.22 » Référence » Commandes SQL » CREATE TRIGGER

Précédent | Niveau supérieur | Suivant
CREATE TRANSFORM | Sommaire | CREATE TYPE

## CREATE TRIGGER

CREATE TRIGGER — Définir un nouveau déclencheur

## Synopsis

    CREATE [ OR REPLACE ] [ 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 { FUNCTION | PROCEDURE } nom_fonction ( arguments )

    où événement fait partie de :

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

## Description

`CREATE TRIGGER` crée un nouveau déclencheur. `CREATE OR REPLACE TRIGGER` va soit créer un nouveau trigger, soit remplacer un trigger existant. Le déclencheur 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 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).

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`. Les déclencheurs 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 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` 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` sur une table standard (pas une table distante). Ce trigger ne doit pas être un trigger de contrainte. De plus, si le trigger est un trigger `UPDATE`, il ne doit pas indiquer une liste de `nom_colonne` lors de l'utilisation de cette option. `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 déclencheurs 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 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`, 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 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` 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 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 pour plus de détails sur cette option. Elle ne peut être spécifiée que pour les déclencheurs 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 déclencheur.

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

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 `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é 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.

## 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 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.

## 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, 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 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. L'option `OR REPLACE` est aussi une extension.

## Voir aussi

ALTER TRIGGER, DROP TRIGGER, SET CONSTRAINTS, CREATE FUNCTION