FirebirdSQL logo
 VIEWPROCEDURE 

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

DDL Triggers

DDL triggers allow restrictions to be placed on users who attempt to create, alter or drop a DDL object.Their other purposes is to keep a metadata change log.

DDL triggers fire on specified metadata changes events in a specified phase.BEFORE triggers run before changes to system tables.AFTER triggers run after changes in system tables.

Important

The event type [BEFORE | AFTER] of a DDL trigger cannot be changed.

In a sense, DDL triggers are a sub-type of database triggers.

Who Can Create a DDL Trigger?

DDL triggers can be created by:

Suppressing DDL Triggers

A DDL trigger is a type of database trigger.See [fblangref50-ddl-trgr-dbtrigger-notrgr] how to suppress DDL — and database — triggers.