FirebirdSQL logo
 VIEWPROCEDURE 
Suppressing Database Triggers

Some Firebird command-line tools have been supplied with switches that an administrator can use to suppress the automatic firing of database triggers.So far, they are:

gbak -nodbtriggers
isql -nodbtriggers
nbackup -T
Two-phase Commit

In a two-phase commit scenario, TRANSACTION COMMIT triggers fire in the prepare phase, not at the commit.

Some Caveats
  1. The use of the IN AUTONOMOUS TRANSACTION DO statement in the database event triggers related to transactions (TRANSACTION START, TRANSACTION ROLLBACK, TRANSACTION COMMIT) may cause the autonomous transaction to enter an infinite loop

  2. The DISCONNECT and TRANSACTION ROLLBACK event triggers will not be executed when clients are disconnected via monitoring tables (DELETE FROM MON$ATTACHMENTS)

Examples of CREATE TRIGGER for “Database Triggers”

  1. Creating a trigger for the event of connecting to the database that logs users logging into the system.The trigger is created as inactive.

    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
  2. Creating a trigger for the event of connecting to the database that does not permit any users, except for SYSDBA, to log in during off hours.

    CREATE EXCEPTION E_INCORRECT_WORKTIME 'The working day has not started yet.';
    
    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