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.
A DDL trigger is a type of database trigger.See [fblangref50-ddl-trgr-dbtrigger-notrgr] how to suppress DDL — and database — 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
)
====================================================
[fblangref50-ddl-trgr-alter], [fblangref50-ddl-trgr-crtalter], [fblangref50-ddl-trgr-recreate], [fblangref50-ddl-trgr-drop], DDL Triggers in Chapter Procedural SQL (PSQL) Statements