FirebirdSQL logo
 VIEWPROCEDURE 

Firing Order of Triggers

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 no positions are specified, or if several triggers have a single position number, the triggers will be executed in the alphabetical order of their names.

Examples of CREATE TRIGGER for Tables and Views

  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 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

Triggers can be defined to fire upon “database events”;a mixture of events that act across the scope of a session (connection), and events that act across the scope of an individual transaction:

  • CONNECT

  • DISCONNECT

  • TRANSACTION START

  • TRANSACTION COMMIT

  • TRANSACTION ROLLBACK

[fblangref50-ddl-trgr-ddltrigger] are a subtype of database triggers, covered in a separate section.

Who Can Create a Database Trigger?

Database triggers can be created by: