FirebirdSQL logo
 VIEWPROCEDURE 

Un déclencheur est un type spécial de procédure stockée qui n’est pas appelé directement, mais dont l’exécution est déclenchée par l’occurrence de l’un des événements associés à une table ou à une vue spécifique, ou par l’occurrence de l’un des événements de la base de données.

Un déclencheur est un type spécial de procédure stockée qui n’est pas appelé directement mais qui est exécuté lorsqu’un événement spécifique se produit dans la table ou la vue associée. Un déclencheur DML est spécifique à une et une seule relation (table ou vue) et à une phase dans le temps de l’événement (BEFORE ou AFTER). Il peut être configuré pour s’exécuter pour un événement spécifique (insertion, mise à jour, suppression) ou pour une combinaison de deux ou trois de ces événements.

En dehors des déclencheurs DML, il existe aussi des * Les déclencheurs pour les événements de la base de données qui se produisent au début ou à la fin d’une connexion ou d’une transaction. * Les déclencheurs DDL, qui se produisent avant ou après l’exécution d’un ou plusieurs types d’instructions DDL.

CREATE TRIGGER

Utilisé pour:

Création d’un nouveau trigger.

Disponible en

DSQL, ESQL

Syntaxe
CREATE TRIGGER trigname {
    <relation_trigger_legacy>
  | <relation_trigger_sql2003>
  | <database_trigger>
  | <ddl_trigger> }
<routine body>

<relation_trigger_legacy> ::=
  FOR {tablename | viewname}
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <mutation_list>
  [POSITION number]

<relation_trigger_sql2003> ::=
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <mutation_list>
  ON {tablename | viewname}
  [POSITION number]

<database_trigger> ::=
  [ACTIVE | INACTIVE]
  ON db_event
  [POSITION number]

<ddl_trigger> ::=
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <ddl_events>
  [POSITION number]

<mutation_list> ::= <mutation> [OR <mutation> [OR <mutation>]]

<mutation> ::=  INSERT | UPDATE | DELETE

<db_event> ::=
    CONNECT | DISCONNECT
  | TRANSACTION {START | COMMIT | ROLLBACK}


<ddl_events> ::= {
    ANY DDL STATEMENT
  | <ddl_event_item> [{OR <ddl_event_item>} ...]
}

<ddl_event_item> ::=
    {CREATE | ALTER | DROP} TABLE
  | {CREATE | ALTER | DROP} PROCEDURE
  | {CREATE | ALTER | DROP} FUNCTION
  | {CREATE | ALTER | DROP} TRIGGER
  | {CREATE | ALTER | DROP} EXCEPTION
  | {CREATE | ALTER | DROP} VIEW
  | {CREATE | ALTER | DROP} DOMAIN
  | {CREATE | ALTER | DROP} ROLE
  | {CREATE | ALTER | DROP} SEQUENCE
  | {CREATE | ALTER | DROP} USER
  | {CREATE | ALTER | DROP} INDEX
  | {CREATE | DROP} COLLATION
  | ALTER CHARACTER SET
  | {CREATE | ALTER | DROP} PACKAGE
  | {CREATE | DROP} PACKAGE BODY
  | {CREATE | ALTER | DROP} MAPPING

<routine-body> ::=
    <psql-routine-spec>
  | <external-routine-spec>

<psql-routine-spec> ::=
  [<rights-clause>] <psql-routine-body>


<rights-clause> ::=
  SQL SECURITY {DEFINER | INVOKER}

<psql-routine-body> ::=
  Voir Syntaxe du corps du module

<external-routine-spec> ::=
  <external-routine-reference>
  [AS <extbody>]

<external-routine-reference> ::= EXTERNAL NAME '<extname>' ENGINE <engine>

<extname> ::= '<module-name>!<routine-name>[!<misc-info>]'
Table 1. Paramètres de la commande CREATE TRIGGER
Paramètre Description

trigname

Nom du trigger. Peut contenir jusqu’à 63 caractères.

relation_trigger_legacy

Déclaration du trigger de la table (héritée).

relation_trigger_sql2003

La déclaration du trigger de la table selon la norme SQL-2003.

database_trigger

Déclaration d’un trigger de base de données.

ddl_trigger

Déclaration de déclenchement DDL.

tablename

Nom de la table

viewname

Nom de la vue

mutation_list

Liste des événements de la table.

mutation

Un des événements sur la table.

db_event

Un événement de connexion ou de transaction.

ddl_events

Liste des événements de changement de métadonnées.

ddl_event_item

Un des événements de la modification des métadonnées.

number

L’ordre dans lequel le trigger est actionné. De 0 à 32767.

extbody

Le corps du déclencheur externe. La chaîne littérale peut être utilisée par l’UDR à diverses fins.

module-name

Nom du module externe.

routine-name

Le nom du point d’entrée dans le module.

misc-info

Informations diverses utilisées par le trigger externe

L’instruction CREATE TRIGGER crée un nouveau déclencheur. Un déclencheur peut être créé pour le ou les événements d’une relation (table ou vue), pour un ou des événements de changement de métadonnées, ou pour l’un des événements de la base de données.

L’opérateur CREATE TRIGGER, comme ses parents ALTER TRIGGER, CREATE OR ALTER TRIGGER et RECREATE TRIGGER sont des opérateurs composés contenant un en-tête et un corps.

L’en-tête spécifie le nom du déclencheur et contient le nom de la relation (pour les déclencheurs de table), la phase du déclencheur, l’événement (ou les événements) du déclencheur et la position. Le nom du déclencheur doit être unique parmi les autres noms de déclencheurs.

Droits d’exécution

L’option facultative SQL SECURITY vous permet de spécifier avec quels privilèges le trigger est exécuté. Si l’option INVOKER est sélectionnée, le déclencheur est exécuté avec les privilèges de l’utilisateur appelant. Si l’option DEFINER est sélectionnée, le déclencheur est exécuté avec les privilèges de l’utilisateur qui le définit (propriétaire). Ces privilèges seront complétés par les privilèges accordés au déclencheur lui-même en utilisant l’opérateur GRANT. Par défaut, le déclencheur hérite des privilèges d’exécution spécifiés pour la table. Les déclencheurs sur les événements de la base de données sont exécutés par défaut avec les privilèges de l’utilisateur qui les définit (propriétaire).

Corps du trigger

Le corps d’un déclencheur consiste en des déclarations facultatives de variables locales, de sous-routines et de curseurs nommés, et en une ou plusieurs instructions, ou blocs d’instructions, enfermés dans un bloc externe qui commence par le mot-clé BEGIN et se termine par le mot-clé END. Les déclarations et les instructions internes se terminent par un point-virgule ( ;).

Charactère de fin de commande

Certains éditeurs d’instructions SQL — notamment l’utilitaire isql de la suite Firebird, et peut-être certains éditeurs tiers — utilisent une convention interne qui exige que toutes les instructions soient terminées par un point-virgule.

Cela crée un conflit avec la syntaxe PSQL lors du codage dans ces environnements. Si vous n’êtes pas familier avec ce problème et sa solution, veuillez étudier les détails dans le chapitre PSQL de la brochure titré Changer le charactère de fin de commande dans `isql.

Déclencheurs DML (par table ou vue)

Les déclencheurs DML sont exécutés au niveau de la chaîne (enregistrement) chaque fois qu’une image de chaîne est modifiée. Ils peuvent également être définis pour les tables et les vues.

format de déclaration

La déclaration du trigger DML existe en deux variantes :

  • forme héritée ;

  • Compatible avec SQL-2003 (recommandé).

Actuellement, il est recommandé d’utiliser un format compatible avec SQL-2003.

Pour un trigger DML, il est obligatoire de spécifier une phase et un ou plusieurs événements.

Statut du trigger

Le déclencheur peut être dans l’un des deux états suivants : "ACTIF" ou "INACTIF". Seuls les déclencheurs actifs sont lancés. Par défaut, les déclencheurs sont créés dans l’état actif.

Phase

Le déclencheur peut être exécuté dans l’une des deux phases associées aux changements d’état des données demandées. Le mot clé BEFORE signifie que le déclencheur est invoqué avant le ou les événements concernés, AFTER — après que le ou les événements se soient produits.

Événements

Pour un déclencheur DML, l’un des événements de la table (vue) — INSERT (ajout), UPDATE (modification), DELETE (suppression) — ou plusieurs événements, séparés par le mot-clé OR, peuvent être spécifiés lorsque le déclencheur est appelé. Lors de la création d’un déclencheur, chaque événement (INSERT, UPDATE ou DELETE) ne doit pas être mentionné plus d’une fois.

Les variables de contexte INSERTING, UPDATING et DELETING de type logique peuvent être utilisées dans le corps du déclencheur pour déterminer l’événement qui a déclenché le déclencheur.

Ordre d’opération

Le mot-clé POSITION vous permet de définir l’ordre dans lequel les déclencheurs ayant la même phase et le même événement (ou groupes d’événements) sont exécutés. La position par défaut est 0. Si aucune position n’est définie pour les déclencheurs, ou si plusieurs déclencheurs ont la même valeur de position, les déclencheurs seront exécutés dans l’ordre alphabétique de leur nom.

Corps du trigger

Le mot clé `AS' est suivi du corps du trigger.

Déclaration des variables locales, des curseurs et des sous-routines

La section optionnelle <déclarations> décrit les variables locales de déclenchement, les curseurs nommés et les sous-programmes (sous-procédures et sous-fonctions). Pour plus de détails, voir "Langage procédural PSQL" dans DECLARE VARIABLE et DECLARE CURSOR, DECLARE PROCEDURE, DECLARE FUNCTION.

La section de déclaration facultative est nécessairement suivie d’une déclaration composée. Un opérateur composé consiste en une ou plusieurs instructions PSQL comprises entre les mots-clés BEGIN et END. Un opérateur composé peut contenir un ou plusieurs autres opérateurs composés. L’imbrication est limitée à 512 niveaux. N’importe lequel des BEGIN …​ END peuvent être vides, y compris le bloc principal.

Triggers externes

Un déclencheur peut être situé dans un module externe. Dans ce cas, au lieu du corps du déclencheur, son emplacement dans le module externe est spécifié à l’aide de la phrase EXTERNAL NAME. L’argument de cette commande est une chaîne de caractères contenant le nom du module externe, le nom de la procédure dans le module et les informations définies par l’utilisateur, séparés par un séparateur. La clause ENGINE spécifie le nom du moteur pour la gestion des connexions de modules externes. Dans Firebird, le moteur UDR est utilisé pour gérer les modules externes. Un littéral de chaîne peut être spécifié après le mot-clé AS — le "corps" d’un déclencheur externe, il peut être utilisé par un module externe à diverses fins.

Qui peut créer un déclencheur DML ?

Les déclencheurs DML peuvent être créés par :

  • Administrateurs

  • Propriétaire de la table (vue) ;

  • Utilisateurs ayant le privilège ALTER ANY {TABLE | VIEW}

Exemples

Example 1. Création d’un trigger DML de style Legacy
CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.CUST_NO IS NULL) THEN
    NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
END
Example 2. Création d’un trigger DML selon SQL-2003
CREATE TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END
Example 3. Création d’un trigger DML s’exécutant avec des droits définis par l’utilisateurCréation d’un trigger DML s’exécutant avec des droits définis par l’utilisateur
CREATE TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
SQL SECURITY DEFINER
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END
Example 4. Création d’un trigger DML pour plusieurs événements
CREATE TRIGGER TR_CUST_LOG
ACTIVE AFTER INSERT OR UPDATE OR DELETE
ON CUSTOMER POSITION 10
AS
BEGIN
  INSERT INTO CHANGE_LOG (LOG_ID,
                          ID_TABLE,
                          TABLE_NAME,
                          MUTATION)
  VALUES (NEXT VALUE FOR SEQ_CHANGE_LOG,
          OLD.CUST_NO,
          'CUSTOMER',
          CASE
            WHEN INSERTING THEN 'INSERT'
            WHEN UPDATING  THEN 'UPDATE'
            WHEN DELETING  THEN 'DELETE'
          END);
END
Voir aussi :

ALTER TRIGGER, DROP TRIGGER.

Déclencheurs sur un événement de la base de données

Un déclencheur peut être créé pour l’un des événements de la base de données :

  • CONNECT (connexion à la base de données ou après réinitialisation de la session) ;

  • DISCONNECT (déconnexion de la base de données ou avant la réinitialisation de la session) ;

  • TRANSACTION START (début de transaction) ;

  • TRANSACTION COMMIT (confirmation de la transaction) ;

  • TRANSACTION ROLLBACK (retour en arrière de la transaction).

La variable de contexte RESETTING peut être utilisée dans les déclencheurs des événements CONNECT et DISCONNECT pour distinguer la réinitialisation de la session de la connexion/déconnexion de la base de données.

Il n’est pas possible de spécifier plus d’un événement de base de données pour un déclencheur.

Exécution de déclencheurs d’événements de base de données et traitement des exceptions

Les déclencheurs sur les événements CONNECT et DISCONNECT sont exécutés dans une transaction spécialement créée à cet effet. Si aucune exception n’est levée lors du traitement du déclencheur, la transaction est acquittée. Les exceptions non vérifiées annuleront la transaction et :

  • dans le cas d’un déclencheur d’événement CONNECT, la connexion est rompue et des exceptions sont renvoyées au client ;

  • Pour un événement déclencheur DISCONNECT, la connexion est terminée comme prévu, mais les exceptions ne sont pas renvoyées au client.

Les déclencheurs d’événements CONNECT et DISCONNECT sont également déclenchés lorsque l’instruction de réinitialisation de l’environnement de session est exécutée. Voir la section ALTER SESSION RESET pour les particularités de la gestion des erreurs dans les déclencheurs des événements CONNECT et DISCONNECT.

Les déclencheurs sur les événements de transaction sont déclenchés au début de la transaction, à sa confirmation ou à son retour. Les exceptions non vérifiées sont traitées en fonction du type d’événement :

  • pour l’événement TRANSACTION START, l’exception est renvoyée au client et la transaction est annulée ;

  • pour l’événement TRANSACTION COMMIT, l’exception est retournée au client, l’action effectuée par le déclencheur et la transaction sont annulées ;

  • Pour l’événement TRANSACTION ROLLBACK, l’exception n’est pas renvoyée au client et la transaction est annulée comme prévu.

Pièges

Il résulte de ce qui précède qu’il n’y a pas de moyen direct de savoir quel déclencheur (DISCONNECT ou ROLLBACK) a soulevé l’exception. Il est également clair que vous ne pouvez pas vous connecter à la base de données lorsqu’il y a une exception dans le déclencheur d’événements CONNECT, et le démarrage d’une transaction est également annulé lorsqu’il y a une exception dans le déclencheur d’événements TRANSACTION START. Dans les deux cas, la base de données est effectivement verrouillée jusqu’à ce que vous désactiviez les déclencheurs de la base de données et corrigiez le code erroné.

Désactiver les triggers

De nouvelles clés ont été ajoutées à certains utilitaires de ligne de commande Firebird pour désactiver les triggers sur la base de données :

gbak -nodbtriggers
isql -nodbtriggers
nbackup -T

Ces clés ne peuvent être utilisées que par SYSDBA ou le propriétaire de la base de données.

Confirmation de la transaction en deux phases

Dans le cas de transactions à deux phases, les déclencheurs de l’événement TRANSACTION START sont déclenchés dans la phase de préparation, et non dans la phase de livraison.

Avertissements

  1. Les déclencheurs des événements de base de données DISCONNECT et ROLLBACK ne seront pas déclenchés lorsque les clients sont désactivés via les tables de surveillance (DELETE FROM MON$ATTACHMENTS).

  2. L’utilisation de l’instruction IN AUTONOMOUS TRANSACTION DO dans les déclencheurs d’événements de la base de données liés aux transactions (COMMIT, ROLLBACK, START) peut provoquer une boucle.

Qui peut créer des déclencheurs pour les événements de la base de données ?

Des déclencheurs pour les événements de la base de données peuvent être créés :

  • Administrateurs

  • Propriétaire de la base de données ;

  • Utilisateurs avec le privilège ALTER DATABASE.

Exemples

Example 1. Créez un trigger sur un événement de connexion à une base de données pour enregistrer l’événement.
CREATE TRIGGER tr_log_connect
INACTIVE ON CONNECT POSITION 0
AS
BEGIN
  INSERT INTO LOG_CONNECT (ID,
                           USERNAME,
                           ATIME)
  VALUES (NEXT VALUE FOR SEQ_LOG_CONNECT,
          CURRENT_USER,
          CURRENT_TIMESTAMP);
END
Example 2. Créer un déclencheur pour un événement de connexion à une base de données afin de contrôler l’accès.
CREATE EXCEPTION E_INCORRECT_WORKTIME 'La journée de travail n''a pas encore commencé';

CREATE TRIGGER TR_LIMIT_WORKTIME ACTIVE
ON CONNECT POSITION 1
AS
BEGIN
  IF ((CURRENT_USER <> 'SYSDBA') AND
      NOT (CURRENT_TIME BETWEEN time '9:00' AND time '17:00')) THEN
     EXCEPTION E_INCORRECT_WORKTIME;
END
Voir aussi :

ALTER TRIGGER, DROP TRIGGER.

Déclencheurs pour les événements de changement de métadonnées

Les déclencheurs d’événements de changement de métadonnées (déclencheurs DDL) sont conçus pour fournir des restrictions qui seront appliquées aux utilisateurs qui tentent de créer, modifier ou supprimer un objet DDL. Leur autre objectif est de fournir un journal des changements de métadonnées.

Les déclencheurs pour les événements de changement de métadonnées sont une sous-espèce des déclencheurs d’événements de base de données.

Caractéristiques :

  1. Les déclencheurs BEFORE sont exécutés avant les modifications des tables du système. Les déclencheurs AFTER sont exécutés après les modifications apportées aux tables du système.

  2. Lorsqu’une instruction DDL déclenche un déclencheur dans lequel une exception est levée (BEFORE ou AFTER, intentionnellement ou non), l’instruction ne sera pas corrigée. En d’autres termes, les exceptions peuvent être utilisées pour s’assurer que l’instruction DDL est remplacée si certaines conditions ne sont pas remplies.

  3. Les actions de déclenchement DDL ne sont exécutées que lorsque la transaction dans laquelle l’instruction DDL concernée est exécutée est validée. N’oubliez jamais que dans un trigger AFTER, seul ce qui peut être fait après une commande DDL, sans commiter automatiquement les transactions, est possible. Vous ne pouvez pas, par exemple, créer une table dans le déclencheur et l’utiliser là.

  4. Pour les instructions CREATE OR ALTER …​, le déclencheur est déclenché une fois pour un événement CREATE ou ALTER, selon que l’objet existait ou non auparavant. Pour les opérateurs RECREATE, le trigger est déclenché pour l’événement DROP si l’objet existait et ensuite pour l’événement CREATE.

  5. Si l’objet de métadonnées n’existe pas, les déclencheurs des événements ALTER et DROP ne sont normalement pas déclenchés. Les exceptions sont décrites dans la clause 6.

  6. L’exception à la règle 5 sont les déclencheurs BEFORE {ALTER | DROP} USER, qui seront appelés même si le nom d’utilisateur n’existe pas. Cela est dû au fait que ces commandes sont exécutées pour une base de données de sécurité pour laquelle aucune vérification de l’existence de l’utilisateur n’est effectuée avant leur exécution. Ce comportement est susceptible d’être différent pour les utilisateurs embarqués, donc n’écrivez pas de code qui en dépende.

  7. Si une exception est levée après le début de l’exécution de la commande DDL et avant l’exécution du déclencheur AFTER, celui-ci n’est pas exécuté.

  8. Les déclencheurs individuels {CREATE | ALTER | DROP} ne sont pas lancés pour les procédures et les fonctions au sein des packages. {PROCÉDURE | FONCTION}.

  9. L’instruction ALTER DOMAIN old name TO new name définit les variables contextuelles OLD_OBJECT_NAME et NEW_OBJECT_NAME dans les déclencheurs BEFORE et AFTER. La variable contextuelle OBJECT_NAME contiendra l’ancien nom de l’objet de métadonnées dans le déclencheur BEFORE et le nouveau nom dans le déclencheur AFTER.

Si ANY DDL STATEMENT est spécifié comme événement, le déclencheur sera invoqué lorsque l’un des événements DDL se produira.

L’espace des noms DDL_TRIGGER

Pendant que le déclencheur DDL est en cours d’exécution, l’espace de noms DL_TRIGGER est disponible pour être utilisé dans la fonction RDB$GET_CONTEXT. Son utilisation est également autorisée dans les procédures stockées et les fonctions appelées par des déclencheurs DDL.

Le contexte DDL_TRIGGER agit comme une pile. Avant de déclencher un déclencheur DDL, les valeurs liées à la commande en cours d’exécution sont placées sur cette pile. Lorsque le déclenchement est terminé, les valeurs sont éjectées. Ainsi. Dans le cas d’instructions DDL en cascade, lorsque chaque commande DDL utilisateur déclenche un déclencheur DDL, et que ce déclencheur déclenche d’autres commandes DDL, avec EXECUTE STATEMENT, les valeurs des variables dans l’espace de noms DDL_TRIGGER correspondront à la commande qui a déclenché le dernier déclencheur DDL dans la pile d’appels.

Variables disponibles dans l’espace de noms DDL_TRIGGER

  • EVENT_TYPE - type d’événement (CREATE, ALTER, DROP)

  • OBJECT_TYPE - type d’objet (TABLE, VIEW etc.)

  • DDL_EVENT - nom de l’événement (<ddl event item>),

    où <ddl event item> = EVENT_TYPE || '' || OBJECT_TYPE

  • OBJECT_NAME - nom de l’objet de métadonnées

  • OLD_OBJECT_NAME - nom de l’objet de métadonnées avant le renommage.

  • NEW_OBJECT_NAME - nom de l’objet de métadonnées après le renommage.

  • SQL_TEXT - texte de la requête SQL

Supprimer un déclencheur

De nouvelles clés ont été ajoutées à certains utilitaires de ligne de commande Firebird pour désactiver les trigger dans la base de données:

gbak -nodbtriggers
isql -nodbtriggers
nbackup -T

Ces clés ne peuvent être utilisées que par SYSDBA ou le propriétaire de la base de données.

Qui peut créer des triggers pour les événements de changement de métadonnées ?

Des déclencheurs pour les événements de changement de métadonnées peuvent être créés :

  • Administrateurs

  • Propriétaire de la base de données ;

  • Utilisateurs avec le privilège `ALTER DATABASE'.

Exemples

Example 1. Contrôle des objets de base de données à l’aide d’un déclencheur DDL
CREATE EXCEPTION e_invalid_sp_name 'Mauvais nom de la procédure stockée (devrait commencer par SP_)';

SET TERM !;

CREATE TRIGGER trig_ddl_sp BEFORE CREATE PROCEDURE
AS
BEGIN
  IF (rdb$get_context('DDL_TRIGGER', 'OBJECT_NAME')
      NOT STARTING 'SP_') THEN
    EXCEPTION e_invalid_sp_name;
END!

-- Test
CREATE PROCEDURE sp_test
AS
BEGIN
END!

CREATE PROCEDURE test
AS
BEGIN
END!
-- Statement failed, SQLSTATE = 42000
-- exception 1
-- -E_INVALID_SP_NAME
-- -Mauvais nom de la procédure stockée (devrait commencer par SP_)
-- -At trigger 'TRIG_DDL_SP' line: 4, col: 5
SET TERM ;!
Example 2. Opérateurs de contrôle de sécurité DDL
CREATE EXCEPTION e_access_denied 'Access denied';

SET TERM !;

CREATE TRIGGER trig_ddl BEFORE ANY DDL STATEMENT
AS
BEGIN
  IF (current_user <> 'SUPER_USER') THEN
    EXCEPTION e_access_denied;
END!

-- Test
CREATE PROCEDURE sp_test
AS
BEGIN
END!
-- The last command raises this exception and procedure SP_TEST is not created
-- Statement failed, SQLSTATE = 42000
-- exception 1
-- -E_ACCESS_DENIED
-- -Access denied
-- -At trigger 'TRIG_DDL' line: 4, col: 5
SET TERM ;!
Note

Dans Firebird, il existe des privilèges pour les opérateurs DDL, vous devez donc recourir à l’écriture d’un déclencheur DDL uniquement si le même effet ne peut pas être réalisé avec des méthodes standard.
Example 3. Utilisation de déclencheurs DDL pour enregistrer les modifications des métadonnées
CREATE SEQUENCE ddl_seq;

CREATE TABLE ddl_log (
  id BIGINT NOT NULL PRIMARY KEY,
  moment TIMESTAMP NOT NULL,
  user_name VARCHAR(63) NOT NULL,
  event_type VARCHAR(25) NOT NULL,
  object_type VARCHAR(25) NOT NULL,
  ddl_event VARCHAR(25) NOT NULL,
  object_name VARCHAR(63) NOT NULL,
  old_object_name VARCHAR(63),
  new_object_name VARCHAR(63),
  sql_text BLOB sub_type text NOT NULL,
  ok CHAR(1) NOT NULL
);

SET TERM !;

CREATE TRIGGER trig_ddl_log_before BEFORE ANY DDL STATEMENT
AS
  DECLARE id TYPE OF COLUMN ddl_log.id;
BEGIN
  -- Nous devons apporter des changements à la TRANSACTION AUTONOME,
  -- de cette façon, si une exception se produit et que la commande
  -- n'est pas exécutée, elle sera quand même enregistrée.
  IN AUTONOMOUS TRANSACTION DO
  BEGIN
    INSERT INTO ddl_log (
      id, moment, user_name, event_type, object_type, ddl_event,
      object_name, old_object_name, new_object_name, sql_text, ok)
    VALUES (NEXT VALUE FOR ddl_seq,
            current_timestamp, current_user,
            rdb$get_context('DDL_TRIGGER', 'EVENT_TYPE'),
            rdb$get_context('DDL_TRIGGER', 'OBJECT_TYPE'),
            rdb$get_context('DDL_TRIGGER', 'DDL_EVENT'),
            rdb$get_context('DDL_TRIGGER', 'OBJECT_NAME'),
            rdb$get_context('DDL_TRIGGER', 'OLD_OBJECT_NAME'),
            rdb$get_context('DDL_TRIGGER', 'NEW_OBJECT_NAME'),
            rdb$get_context('DDL_TRIGGER', 'SQL_TEXT'),
            'N')
    RETURNING id INTO id;
    rdb$set_context('USER_SESSION', 'trig_ddl_log_id', id);
  END
END!

-- Note :
-- Le déclencheur créé ci-dessus sera exécuté pour cette DDL.
-- Une bonne idée est d'utiliser -nodbtriggers
-- quand on travaille avec lui
CREATE TRIGGER trig_ddl_log_after AFTER ANY DDL STATEMENT
AS
BEGIN
  -- Dans ce cas, nous avons besoin d'une transaction autonome,
  -- parce que dans la transaction originale
  -- nous ne verrons pas l'enregistrement inséré dans la base de données
  -- AVANT le déclenchement d'une transaction hors ligne,
  -- si la transaction de l'utilisateur n'est pas lancée
  -- avec le mode d'isolation READ COMMITTED.
  IN AUTONOMOUS TRANSACTION DO
    UPDATE ddl_log SET ok = 'Y'
    WHERE
      id = rdb$get_context('USER_SESSION', 'trig_ddl_log_id');
END!

COMMIT!

SET TERM ;!

-- Nous supprimons l'enregistrement de la création trig_ddl_log_after.

DELETE FROM ddl_log;
COMMIT;

-- Test

-- Cette commande sera enregistrée une fois
-- (puisque T1 n'existe pas, RECREATE déclenchera l'événement CREATE)
-- avec OK = Y.

RECREATE TABLE t1 (
  n1 INTEGER,
  n2 INTEGER
);

-- L'opérateur ne s'exécutera pas car T1 existe déjà,
-- donc OK aura une valeur de N.

CREATE TABLE t1 (
  n1 INTEGER,
  n2 INTEGER
);

-- T2 n'existe pas. Cette action ne sera pas enregistrée.

DROP TABLE t2;

-- Cette action sera enregistrée deux fois
-- (puisque T1 existe, l'action RECREATE est traitée
-- comme DROP et CREATE) avec le champ OK = Y.

RECREATE TABLE t1 (
  n INTEGER
);

CREATE DOMAIN dom1 AS INTEGER;

ALTER DOMAIN dom1 TYPE BIGINT;

ALTER DOMAIN dom1 TO dom2;

COMMIT;

SELECT
  id,
  ddl_event,
  object_name as name,
  sql_text,
  ok
FROM ddl_log
ORDER BY id;
 ID DDL_EVENT                 OBJECT_NAME                      SQL_TEXT OK
=== ========================= ======================= ================= ======
  2 CREATE TABLE              T1                                   80:3 Y
====================================================
SQL_TEXT:
recreate table t1 (
    n1 integer,
    n2 integer
)
====================================================
  3 CREATE TABLE              T1                                   80:2 N
====================================================
SQL_TEXT:
create table t1 (
    n1 integer,
    n2 integer
)
====================================================
  4 DROP TABLE                T1                                   80:6 Y
====================================================
SQL_TEXT:
recreate table t1 (
    n integer
)
====================================================
  5 CREATE TABLE              T1                                   80:9 Y
====================================================
SQL_TEXT:
recreate table t1 (
    n integer
)
====================================================
Voir aussi :

ALTER TRIGGER, DROP TRIGGER.

ALTER TRIGGER

Utilisé pour:

Modification d’un déclencheur existant.

Disponible en

DSQL, ESQL

Syntaxe
ALTER TRIGGER trigname
[ACTIVE | INACTIVE]
[{BEFORE | AFTER} <mutation_list>]
[POSITION number]
[SQL SECURITY {DEFINER | INVOKER} | DROP SQL SECURITY]
[<routine-body>]

<mutation_list> ::= <mutation> [OR <mutation> [OR <mutation>]]

<mutation> ::= { INSERT | UPDATE | DELETE }

Description complète de l'opérateur Voir. CREATE TRIGGER.

Modifications autorisées

La déclaration de changement de déclencheur peut être modifiée :

  • Etat de l’activité (ACTIVE | INACTIVE) ;

  • Phase (BEFORE | AFTER) ;

  • Événement(s) ;

  • Position de la commande ;

  • Privilèges d’exécution des déclencheurs : appeler l’utilisateur (SQL SECURITY INVOKER), définir l’utilisateur (SQL SECURITY DEFINER) ou hériter de la table (DROP SQL SECURITY) ;

  • Code du corps du déclencheur.

Si un élément n’est pas spécifié, il reste inchangé.

Note

Un déclencheur DML ne peut pas être transformé en un déclencheur d’événement de base de données et vice versa.

Un événement dans un déclencheur de base de données ne peut pas être modifié.
Tip
N’oubliez pas

Le déclencheur avec le mot clé BEFORE vient avant l’événement correspondant, avec le mot clé AFTER vient après l’événement correspondant.

Un déclencheur DML peut contenir plus d’un événement (INSERT, UPDATE, DELETE). Les événements doivent être séparés par le mot clé OR. Chacun des événements ne peut être spécifié plus d’une fois.

Le mot clé POSITIONALTER TRIGGER, POSITION permet de spécifier un ordre d’exécution supplémentaire avec la même phase et le même événement. Par défaut, la position est 0. Si aucune position n’est spécifiée, ou si plusieurs déclencheurs ont le même numéro de position, les déclencheurs seront exécutés dans l’ordre alphabétique de leur nom.

Qui peut changer les déclencheurs ?

Les déclencheurs DML peuvent changer :

  • Administrateurs.

  • Propriétaire de la table (vue) ;

  • Les utilisateurs ayant le privilège `ALTER ANY {TABLE | VIEW}'.

Les déclencheurs pour les événements de la base de données et les déclencheurs d’événements pour les changements de métadonnées peuvent changer :

  • Administrateurs.

  • Propriétaire de la base de données ;

  • Un utilisateur avec le privilège `ALTER DATABASE'.

Exemples

Example 1. Désactiver (mettre inactif) le déclencheur
ALTER TRIGGER set_cust_no INACTIVE;
Example 2. Changer la position du déclencheur
ALTER TRIGGER set_cust_no POSITION 14;
Example 3. Mettre le déclencheur dans un état inactif et modifier la liste des événements
ALTER TRIGGER TR_CUST_LOG
INACTIVE AFTER INSERT OR UPDATE;
Example 4. Modification des privilèges d’exécution des déclencheurs

Une fois cette instruction exécutée, le déclencheur sera exécuté avec les privilèges de l’utilisateur qui le définit (propriétaire).

ALTER TRIGGER TR_CUST_LOG
SQL SECURITY DEFINER;
Example 5. Suppression des privilèges d’exécution des déclencheurs

Après avoir supprimé les privilèges d’exécution du déclencheur, celui-ci sera exécuté avec les privilèges hérités de la table. Si la table n’a pas de privilèges d’exécution définis, le déclencheur sera exécuté avec les privilèges de l’utilisateur appelant.

ALTER TRIGGER TR_CUST_LOG
DROP SQL SECURITY;
Example 6. Faire passer le déclencheur à l’état actif, en modifiant sa position et son corps.
ALTER TRIGGER tr_log_connect
ACTIVE POSITION 1
AS
BEGIN
  INSERT INTO LOG_CONNECT (ID,
                           USERNAME,
                           ROLENAME,
                           ATIME)
  VALUES (NEXT VALUE FOR SEQ_LOG_CONNECT,
          CURRENT_USER,
          CURRENT_ROLE,
          CURRENT_TIMESTAMP);
END
Voir aussi :

CREATE TRIGGER, CREATE OR ALTER TRIGGER, RECREATE TRIGGER.

CREATE OR ALTER TRIGGER

Utilisé pour:

Créez un nouveau déclencheur ou modifiez un déclencheur existant.

Disponible en

DSQL, ESQL

Syntaxe
CREATE OR ALTER TRIGGER trigname {
    <relation_trigger_legacy>
  | <relation_trigger_sql2003>
  | <database_trigger>
  | <ddl_trigger> }
<routine-body>

Pour une description complète de l'opérateur, voir CREATE TRIGGER.

L’instruction CREATE OR ALTER TRIGGER crée un nouveau trigger s’il n’existe pas, ou le modifie et le recompile sinon, avec les droits et dépendances existants préservés.

Exemples

Example 1. Création d’un nouveau déclencheur ou modification d’un déclencheur existant
CREATE OR ALTER TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END
Voir aussi :

CREATE TRIGGER, ALTER TRIGGER, RECREATE TRIGGER.

DROP TRIGGER

Utilisé pour:

Suppression d’un déclencheur existant.

Disponible en

DSQL, ESQL

Syntaxe
DROP TRIGGER trigname
Table 1. Paramètres de la commande DROP TRIGGER
Paramètre Description

trigname

Nom du déclencheur.

L’instruction `DROP TRIGGER' supprime un trigger existant.

Qui peut supprimer les déclencheurs ?

Les déclencheurs DML peuvent supprimer :

  • Administrateurs.

  • Propriétaire de la table (vue) ;

  • Les utilisateurs ayant le privilège `ALTER ANY {TABLE | VIEW}'.

Les déclencheurs pour les événements de la base de données et les déclencheurs d’événements pour les changements de métadonnées peuvent être supprimés :

  • Administrateurs.

  • Propriétaire de la base de données ;

  • Un utilisateur avec le privilège `ALTER DATABASE'.

Exemples

Example 1. Supprimer le Trigger
DROP TRIGGER set_cust_no;
Voir aussi :

CREATE TRIGGER, ALTER TRIGGER.

RECREATE TRIGGER

Utilisé pour:

Créez un nouveau déclencheur ou recréez un déclencheur existant.

Disponible en

DSQL, ESQL

Syntaxe
RECREATE TRIGGER trigname {
    <relation_trigger_legacy>
  | <relation_trigger_sql2003>
  | <database_trigger>
  | <ddl_trigger> }
<routine-body>

Description complète de l'opérateur Voir CREATE TRIGGER.

L’opérateur RECREATE TRIGGER crée un nouveau trigger si le trigger avec le nom spécifié n’existe pas, sinon l’opérateur RECREATE TRIGGER essaiera de le supprimer et d’en créer un nouveau.

Exemples

Example 1. Créer ou recréer un trigger
RECREATE TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END
Voir aussi :

CREATE TRIGGER, DROP TRIGGER, CREATE OR ALTER TRIGGER.