FirebirdSQL logo
 VIEWPROCEDURE 

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.

Examples of 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
    )
    ====================================================

ALTER TRIGGER

Alters a trigger

Available in

DSQL, ESQL

Syntax
ALTER TRIGGER trigname
  [ACTIVE | INACTIVE]
  [{BEFORE | AFTER} <mutation_list>]
  [POSITION number]
  [{<psql_trigger> | <external-module-body>}]

<psql_trigger> ::=
  [<sql_security>]
  [<psql-module-body>]

<sql_security> ::=
    SQL SECURITY {INVOKER | DEFINER}
  | DROP SQL SECURITY

!! See syntax of CREATE TRIGGER for further rules !!

The ALTER TRIGGER statement only allows certain changes to the header and body of a trigger.

Permitted Changes to Triggers

  • Status (ACTIVE | INACTIVE)

  • Phase (BEFORE | AFTER) (of DML triggers)

  • Events (of DML triggers)

  • Position in the firing order

  • Modifications to code in the trigger body

If an element is not specified, it remains unchanged.

Note

A DML trigger cannot be changed to a database or DDL trigger.

It is not possible to change the event(s) or phase of a database or DDL trigger.

Note
Reminders

The BEFORE keyword directs that the trigger be executed before the associated event occurs;the AFTER keyword directs that it be executed after the event.

More than one DML event — INSERT, UPDATE, DELETE — can be covered in a single trigger.The events should be separated with the keyword OR.No event should be mentioned more than once.

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.

Who Can Alter a Trigger?

DML triggers can be altered by:

  • Administrators

  • The owner of the table (or view)

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

Database and DDL triggers can be altered by:

Examples using ALTER TRIGGER

  1. Deactivating the set_cust_no trigger (switching it to the inactive status).

    ALTER TRIGGER set_cust_no INACTIVE;
  2. Changing the firing order position of the set_cust_no trigger.

    ALTER TRIGGER set_cust_no POSITION 14;
  3. Switching the TR_CUST_LOG trigger to the inactive status and modifying the list of events.

    ALTER TRIGGER TR_CUST_LOG
    INACTIVE AFTER INSERT OR UPDATE;
  4. Switching the tr_log_connect trigger to the active status, changing its position and body.

    ALTER TRIGGER tr_log_connect
    ACTIVE POSITION 1
    AS
    BEGIN
      INSERT INTO LOG_CONNECT (ID,
                               USERNAME,
                               ROLENAME,
                               ATIME)
      VALUES (NEXT VALUE FOR SEQ_LOG_CONNECT,
              CURRENT_USER,
              CURRENT_ROLE,
              CURRENT_TIMESTAMP);
    END

CREATE OR ALTER TRIGGER

Creates a trigger if it doesn’t exist, or alters a trigger

Available in

DSQL

Syntax
CREATE OR ALTER TRIGGER trigname
  { <relation_trigger_legacy>
  | <relation_trigger_sql>
  | <database_trigger>
  | <ddl_trigger> }
  {<psql_trigger> | <external-module-body>}

!! See syntax of CREATE TRIGGER for further rules !!

The CREATE OR ALTER TRIGGER statement creates a new trigger if it does not exist;otherwise it alters and recompiles it with the privileges intact and dependencies unaffected.

Example of CREATE OR ALTER TRIGGER

Creating a new trigger if it does not exist or altering it if it does exist
CREATE OR ALTER 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

DROP TRIGGER

Drops a trigger

Available in

DSQL, ESQL

Syntax
DROP TRIGGER trigname
Table 1. DROP TRIGGER Statement Parameter
Parameter Description

trigname

Trigger name

The DROP TRIGGER statement drops (deletes) an existing trigger.

Who Can Drop a Trigger?

DML triggers can be dropped by:

  • Administrators

  • The owner of the table (or view)

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

Database and DDL triggers can be dropped by:

Example of DROP TRIGGER

Deleting the set_cust_no trigger
DROP TRIGGER set_cust_no;

RECREATE TRIGGER

Drops a trigger if it exists, and creates a trigger

Available in

DSQL

Syntax
RECREATE TRIGGER trigname
  { <relation_trigger_legacy>
  | <relation_trigger_sql>
  | <database_trigger>
  | <ddl_trigger> }
  {<psql_trigger> | <external-module-body>}

!! See syntax of CREATE TRIGGER for further rules !!

The RECREATE TRIGGER statement creates a new trigger if no trigger with the specified name exists;otherwise the RECREATE TRIGGER statement tries to drop the existing trigger and create a new one.The operation will fail on COMMIT if the trigger is in use.

Warning

Be aware that dependency errors are not detected until the COMMIT phase of this operation.

Example of RECREATE TRIGGER

Creating or recreating the set_cust_no trigger.

RECREATE 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