Suppressing DDL Triggers

Examples of DDL Triggers

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

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.

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