DML triggers can be created by:

• Administrators

• The owner of the table (or view)

• Users with the ALTER ANY TABLE or — for a view — ALTER ANY VIEW privilege

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.

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 ON customer POSITION 0
     |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
      |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
   

4. With DEFINER set for trigger tr_ins, user US needs only the INSERT privilege on tr. If it were set for INVOKER, either the user or the trigger would also need the INSERT privilege on table t.

      |create table tr (i integer);
      |create table t (i integer);
      |set term ^;
      |create trigger tr_ins for tr after insert SQL SECURITY DEFINER
      |as
      |begin
      |  insert into t values (NEW.i);
      |end^
      |set term ;^
      |grant insert on table tr to user us;
      | 
      |commit;
      | 
      |connect 'localhost:/tmp/29.fdb' user us password 'pas';
      |insert into tr values(2);
   

   The result would be the same if SQL SECURITY DEFINER were specified for table TR:

      |create table tr (i integer) SQL SECURITY DEFINER;
      |create table t (i integer);
      |set term ^;
      |create trigger tr_ins for tr after insert
      |as
      |begin
      |  insert into t values (NEW.i);
      |end^
      |set term ;^
      |grant insert on table tr to user us;
      | 
      |commit;
      | 
      |connect 'localhost:/tmp/29.fdb' user us password 'pas';
      |insert into tr values(2);
   

Database triggers can be created by:

• Administrators

• Users with the ALTER DATABASE privilege

CONNECT and DISCONNECT triggers are executed in a transaction created specifically for this purpose. This transaction uses the default isolation level, i.e. snapshot (concurrency), write and wait. 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
   

DDL triggers can be created by:

• Administrators

• Users with the ALTER DATABASE privilege

A DDL trigger is a type of database trigger. See the section called “Suppressing Database Triggers” how to suppress database — and DDL — triggers.

1. Here is how you might use a DDL trigger to enforce a consistent naming scheme, in this case, stored procedure names should begin with the prefix SP_:

      |set auto on;
      |create exception e_invalid_sp_name 'Invalid SP name (should start with 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!
      | 
      |-- The last command raises this exception and procedure TEST is not created
      |-- Statement failed, SQLSTATE = 42000
      |-- exception 1
      |-- -E_INVALID_SP_NAME
      |-- -Invalid SP name (should start with SP_)
      |-- -At trigger 'TRIG_DDL_SP' line: 4, col: 5
      | 
      |set term ;!
   

2. Implement custom DDL security, in this case restricting the running of DDL commands to certain users:

      |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

   Firebird has privileges for executing DDL statements, so writing a DDL trigger for this should be a last resort, if the same effect cannot be achieved using privileges.

3. Use a trigger to log DDL actions and attempts:

      |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,
      |  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
      |  -- We do the changes in an AUTONOMOUS TRANSACTION, so if an exception happens
      |  -- and the command didn't run, the log will survive.
      |  in autonomous transaction do
      |  begin
      |    insert into ddl_log (id, moment, user_name, event_type, object_type,
      |                         ddl_event, 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', 'SQL_TEXT'),
      |              'N')
      |      returning id into id;
      |    rdb$set_context('USER_SESSION', 'trig_ddl_log_id', id);
      |  end
      |end!
   

   The above trigger will fire for this DDL command. It’s a good idea to use -nodbtriggers when working with them!

      |create trigger trig_ddl_log_after after any ddl statement
      |as
      |begin
      |  -- Here we need an AUTONOMOUS TRANSACTION because the original transaction
      |  -- will not see the record inserted on the BEFORE trigger autonomous
      |  -- transaction if user transaction is not 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 ;!
      | 
      |-- Delete the record about trig_ddl_log_after creation.
      |delete from ddl_log;
      |commit;
   

   Test

      |-- This will be logged one time
      |-- (as T1 did not exist, RECREATE acts as CREATE) with OK = Y.
      |recreate table t1 (
      |  n1 integer,
      |  n2 integer
      |);
      | 
      |-- This will fail as T1 already exists, so OK will be N.
      |create table t1 (
      |  n1 integer,
      |  n2 integer
      |);
      | 
      |-- T2 does not exist. There will be no log.
      |drop table t2;
      | 
      |-- This will be logged twice
      |-- (as T1 exists, RECREATE acts as DROP and CREATE) with OK = Y.
      |recreate table t1 (
      |  n integer
      |);
      | 
      |commit;
   

      |select id, ddl_event, object_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
      |)
      |====================================================
   

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”, DDL Triggers in Chapter Procedural SQL (PSQL) Statements