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

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

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

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

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

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

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

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!

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

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!

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

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!

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;

-- 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 set_cust_no INACTIVE;

ALTER TRIGGER set_cust_no POSITION 14;

ALTER TRIGGER TR_CUST_LOG
INACTIVE AFTER INSERT OR UPDATE;

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