Firebird supports two forms of declaration for relation triggers:

• The original, legacy syntax

• The SQL:2003 standard-compliant form (recommended)

The SQL:2003 standard-compliant form is the recommended one.

A relation trigger specifies — among other things — a phase and one or more events.

Phase concerns the timing of the trigger with regard to the change-of-state event in the row of data:

• A BEFORE trigger is fired before the specified database operation (insert, update or delete) is carried out

• An AFTER trigger is fired after the database operation has been completed

A relation trigger definition specifies at least one of the DML operations INSERT, UPDATE and DELETE, to indicate one or more events on which the trigger should fire. If multiple operations are specified, they must be separated by the keyword OR. No operation may occur more than once.

Within the statement block, the Boolean context variables INSERTING, UPDATING and DELETING can be used to test which operation is currently executing.

The keyword POSITION allows an optional execution order (firing order) to be specified for a series of triggers that have the same phase and event as their target. The default position is 0. If multiple triggers have the same position and phase, those triggers will be executed in an undefined order, while respecting the total order by position and phase.

The optional declarations section beneath the keyword AS in the header of the trigger is for defining variables and named cursors that are local to the trigger. For more details, see DECLARE VARIABLE and DECLARE CURSOR in the Procedural SQL chapter.

The local declarations (if any) are the final part of a trigger’s header section. The trigger body follows, where one or more blocks of PSQL statements are enclosed in a structure that starts with the keyword BEGIN and terminates with the keyword END.

Only the owner of the view or table and administrators have the authority to use CREATE TRIGGER.

1. Creating a trigger in the legacy form, firing before the event of inserting a new record into the CUSTOMER table occurs.

     |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
   

2. Creating a trigger firing before the event of inserting a new record into the CUSTOMER table in the SQL:2003 standard-compliant form.

     |CREATE TRIGGER set_cust_no
     |ACTIVE BEFORE INSERT POSITION 0 ON customer
     |AS
     |BEGIN
     |  IF (NEW.cust_no IS NULL) THEN
     |    NEW.cust_no = GEN_ID(cust_no_gen, 1);
     |END
   

3. Creating a trigger that will file after either inserting, updating or deleting a record in the CUSTOMER table.

      |CREATE TRIGGER TR_CUST_LOG
      |ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 10
      |ON CUSTOMER
      |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
   

CONNECT and DISCONNECT triggers are executed in a transaction created specifically for this purpose. If all goes well, the transaction is committed. Uncaught exceptions cause the transaction to roll back, and

• for a CONNECT trigger, the connection is then broken and the exception is returned to the client

• for a DISCONNECT trigger, exceptions are not reported. The connection is broken as intended

TRANSACTION triggers are executed within the transaction whose start, commit or rollback evokes them. The action taken after an uncaught exception depends on the event:

• In a TRANSACTION START trigger, the exception is reported to the client and the transaction is rolled back

• In a TRANSACTION COMMIT trigger, the exception is reported, the trigger’s actions so far are undone and the commit is cancelled

• In a TRANSACTION ROLLBACK trigger, the exception is not reported and the transaction is rolled back as intended.

  |gbak -nodbtriggers
  |isql -nodbtriggers
  |nbackup -T

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
   

See alsoSection 5.7.2, “ALTER TRIGGER”, Section 5.7.3, “CREATE OR ALTER TRIGGER”, Section 5.7.5, “RECREATE TRIGGER”, Section 5.7.4, “DROP TRIGGER”