shell del C:\\MIX\\firebird\\QA\\fbt\-repo\\tmp\\c2724\.fdb 2\>nul;
set names utf8;
create database '/3333:C:\\MIX\\firebird\\QA\\fbt\-repo\\tmp\\c2724\.fdb' default character set utf8;

create sequence ddl\_seq;

create table ddl\_log \(
    id bigint not null primary key,
    moment timestamp not null,
    user\_name varchar\(31\) not null,
    event\_type varchar\(25\) not null,
    object\_type varchar\(25\) not null,
    ddl\_event varchar\(25\) not null,
    object\_name varchar\(31\) not null,
    old\_object\_name varchar\(31\),
    new\_object\_name varchar\(31\),
    sql\_text blob sub\_type text not null,
    ok char\(1\) not null,
    result\_info blob sub\_type text \-\- the same for 'varchar\(255\)'
\);
commit;

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,
                             old\_object\_name, new\_object\_name, sql\_text, ok, result\_info\)
            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', 'OLD\_OBJECT\_NAME'\),
                    rdb$get\_context\('DDL\_TRIGGER', 'NEW\_OBJECT\_NAME'\),
                    rdb$get\_context\('DDL\_TRIGGER', 'SQL\_TEXT'\),
                    'N',
                    'Κάτι συνέβη\. Θα πρέπει να ελέγξετε'\) \-\- Something was wrong\. One need to check this\.
            returning id into id;
        rdb$set\_context\('USER\_SESSION', 'trig\_ddl\_log\_id', id\);
    end
end
^

\-\- Note: the above trigger will fire for this DDL command\. It's 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', 
        result\_info = 'Τα πάντα ήταν επιτυχής' \-\- Everything has completed successfully
        where id = rdb$get\_context\('USER\_SESSION', 'trig\_ddl\_log\_id'\);
end
^
set term ;^
commit;

\-\- So lets delete the record about trig\_ddl\_log\_after creation\.
delete from ddl\_log;
commit;

set names none; \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-  charset  =  \.N\.O\.N\.E\.
connect '/3333:C:\\MIX\\firebird\\QA\\fbt\-repo\\tmp\\c2724\.fdb';    

create domain dm\_name varchar\(50\) check \(value in \('αμορτισέρ', 'κόμβο', 'σωλήνα', 'φέροντα', 'βραχίονα'\)\);

\-\- This will be logged one time \(as T1 did not exist, RECREATE acts as CREATE\) with OK = Y\.
recreate table t1 \(
     saller\_id integer  \-\- αναγνωριστικό εμπόρου // ID of saler
    ,customer\_id integer  \-\- αναγνωριστικό πελάτη // ID of customer
    ,product\_name dm\_name
\);
    
commit;
set list on;
select id, ddl\_event, object\_name, old\_object\_name, new\_object\_name, sql\_text, ok, result\_info
from ddl\_log order by id;