recreate table tmain(id int primary key); commit;
recreate table tdetl(id int primary key, pid int); commit; -- references tmain
insert into tmain values(1); commit;
set term ^;
execute block as begin
  begin execute statement 'create sequence g'; when any do begin end end
end
^
set term ;^
commit;
alter sequence g restart with 0;
create exception ex_no_parent_row 'attempt to insert/modify field with value which has no parent.'; 
commit;

set term ^;
create or alter trigger tdetl_biu for tdetl active before insert or update as
begin
  if (not exists(select * from tmain m where m.id = new.pid)) then 
    exception ex_no_parent_row;
end
^
set term ;^
commit;

insert into tdetl select gen_id(g,1), iif( gen_id(g,0) <= 4, 1, 2) from rdb$types rows 5; 
commit;

2014-08-31T20:11:13.4560 (20014:0x7fa6802e93f8) EXECUTE_TRIGGER_START
        /var/db/fb25/oltp25.fdb (ATT_1679, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb25sc/bin/isql:25603
                (TRA_92564, CONCURRENCY | WAIT | READ_WRITE)
        TDETL_BIU FOR TDETL (BEFORE INSERT)

2014-08-31T20:11:13.4560 (20014:0x7fa6802e93f8) FAILED EXECUTE_TRIGGER_FINISH
        /var/db/fb25/oltp25.fdb (ATT_1679, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb25sc/bin/isql:25603
                (TRA_92564, CONCURRENCY | WAIT | READ_WRITE)
        TDETL_BIU FOR TDETL (BEFORE INSERT)
      0 ms, 6 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
RDB$EXCEPTIONS                                    1

2014-08-31T20:11:13.4560 (20014:0x7fa6802e93f8) FAILED EXECUTE_STATEMENT_FINISH
        /var/db/fb25/oltp25.fdb (ATT_1679, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb25sc/bin/isql:25603
                (TRA_92564, CONCURRENCY | WAIT | READ_WRITE)

Statement 3720:
-------------------------------------------------------------------------------
insert into tdetl select gen_id(g,1), iif( gen_id(g,0) <= 4, 1, 2) from rdb$types rows 5
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (RDB$TYPES NATURAL)
0 records fetched
      0 ms, 1 write(s), 121 fetch(es), 31 mark(s)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
RDB$TYPES                               5
RDB$EXCEPTIONS                                    1
 TMAIN                                             4 
TDETL                                                                 4                   4

2014-08-31T20:11:13.4560 (20014:0x7fa6802e93f8) ERROR AT jrd8_execute
        /var/db/fb25/oltp25.fdb (ATT_1679, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb25sc/bin/isql:25603
335544517 : exception 158
335544382 : EX_NO_PARENT_ROW
335544382 : attempt to insert/modify field with value which has no parent.
335544842 : At trigger 'TDETL_BIU' line: 3, col: 63

recreate table tdetl_casc(id int primary key using index tdetl_casc_pk, pid int);
recreate table tmain_casc(id int primary key using index tmain_casc_pk);
alter table tdetl_casc add
    constraint tdetl_casc_fk foreign key (pid)
    references tmain_casc(id)
    on delete cascade
    on update cascade
    using index tdetl_pid;
commit;
insert into tmain_casc values(1);
commit;

set term ^;
execute block as
begin
  begin
  execute statement 'drop trigger tmain_trig_ad';
  when any do begin end
  end
  begin
  execute statement 'drop trigger tdetl_trig_biu';
  when any do begin end
  end
end
^
set term ;^
commit;
recreate table tdetl_trig(id int primary key using index tdetl_trig_pk, pid int);
recreate table tmain_trig(id int primary key using index tmain_trig_pk);
commit;
recreate exception ex_no_parent 'no parent key found';

create index tdetl_trig_pid on tdetl_trig(pid);
commit;

set term ^;
create or alter trigger tdetl_trig_biu for tdetl_trig
active before insert or update position 0
as
begin
  if ( NOT exists(select * from tmain_trig m where m.id = new.pid) ) then
    exception ex_no_parent;
end
^

create or alter trigger tmain_trig_ad for tmain_trig
active after delete position 0
as
begin
  delete from tdetl_trig d where d.pid = old.id;
end
^
set term ;^
commit;

insert into tmain_trig values(1);
commit;

SQL> select * from tmain_casc; commit;

          ID
============
           1

SQL> select  count(*) from tdetl_casc; commit;

                COUNT
=====================
                    0

2014-09-01T00:06:20.8580 (27769:0x7f9ae1c6bf90) TRACE_INIT
        SESSION_4


2014-09-01T00:06:20.8600 (27769:0x7f9ae1c6bf90) EXECUTE_STATEMENT_START
        oltp30 ( ATT_720 , SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31141
                (TRA_443449, CONCURRENCY | NOWAIT | READ_WRITE)

Statement 978:
-------------------------------------------------------------------------------
execute block as
declare i int = 0;
declare n int = 1000000;
begin
  while (i < n ) do insert into tdetl_casc(id, pid) values( :i, 1 ) returning :i+1 into i;
end


2014-09-01T00:06:27.9210 (27769:0x7f9ae1c69ec8) TRACE_INIT
        SESSION_4


2014-09-01T00:06:27.9360 (27769:0x7f9ae1c69ec8) EXECUTE_STATEMENT_START
        oltp30 ( ATT_722 , SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31387
                (TRA_443451, CONCURRENCY | NOWAIT | READ_WRITE)

Statement 979:
-------------------------------------------------------------------------------
delete from tmain_casc where id=1

2014-09-01T00:06:29.0100 (27769:0x7f9ae1c69ec8)  FAILED  EXECUTE_STATEMENT_FINISH
        oltp30 ( ATT_722 , SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31387
                (TRA_443451, CONCURRENCY | NOWAIT | READ_WRITE)

Statement 979:
-------------------------------------------------------------------------------
delete from tmain_casc where id=1
0 records fetched
   1073 ms, 839397 fetch(es), 2 mark(s)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
RDB$INDICES                                       2
RDB$RELATION_CONSTRAINTS                          1
TMAIN_CASC                                        1                             1         1

2014-09-01T00:06:29.0100 (27769:0x7f9ae1c69ec8) ERROR AT JStatement::execute
        oltp30 (ATT_722, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31387
335544466 : violation of FOREIGN KEY constraint "TDETL_CASC_FK" on table "TDETL_CASC"
335544839 : Foreign key references are present for the record
335545072 : Problematic key value is ("ID" = 1)

2014-09-01T00:06:31.1610 (27769:0x7f9ae1c6bf90)  FAILED  EXECUTE_STATEMENT_FINISH
        oltp30 ( ATT_720 , SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31141
                (TRA_443449, CONCURRENCY | NOWAIT | READ_WRITE)

Statement 978:
-------------------------------------------------------------------------------
execute block as
declare i int = 0;
declare n int = 1000000;
begin
  while (i < n ) do insert into tdetl_casc(id, pid) values( :i, 1 ) returning :i+1 into i;
end

0 records fetched
  10301 ms, 3893 write(s), 11309314 fetch(es), 2656018 mark(s)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
RDB$INDICES                                       2
RDB$RELATION_CONSTRAINTS                          1
TDETL_CASC                                                       375649              375649

2014-09-01T00:06:31.1610 (27769:0x7f9ae1c6bf90) ERROR AT JStatement::execute
        oltp30 (ATT_720, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31141
335544466 : violation of FOREIGN KEY constraint "TDETL_CASC_FK" on table "TDETL_CASC"
335544838 : Foreign key reference target does not exist
335545072 : Problematic key value is ("PID" = 1)

Trace session ID 5 started
2014-09-01T00:25:40.0670 (27769:0x7f9ae1c61b90) TRACE_INIT
        SESSION_5


2014-09-01T00:25: 40.069 0 (27769:0x7f9ae1c61b90) EXECUTE_STATEMENT_START
        oltp30 (ATT_720, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31141
                (TRA_443466, CONCURRENCY | NOWAIT | READ_WRITE)

Statement 984:
-------------------------------------------------------------------------------
execute block as
declare i int = 0;
declare n int = 1000000;
begin
  while (i < n ) do insert into tdetl_casc(id, pid) values( :i, 1 ) returning :i+1 into i;
end


2014-09-01T00:25: 40.671 0 (27769:0x7f9ae1c6bf90) TRACE_INIT
        SESSION_5


2014-09-01T00:25:40.6730 (27769:0x7f9ae1c6bf90) EXECUTE_STATEMENT_START
        oltp30 (ATT_722, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31387
                (TRA_443468, CONCURRENCY | NOWAIT | READ_WRITE)

Statement 985:
-------------------------------------------------------------------------------
delete from tmain_casc where id=1

2014-09-01T00:25:40.9600 (27769:0x7f9ae1c61b90) FAILED EXECUTE_STATEMENT_FINISH
        oltp30 (ATT_720, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31141
                (TRA_443466, CONCURRENCY | NOWAIT | READ_WRITE)

Statement 984:
-------------------------------------------------------------------------------
execute block as
declare i int = 0;
declare n int = 1000000;
begin
  while (i < n ) do insert into tdetl_casc(id, pid) values( :i, 1 ) returning :i+1 into i;
end

0 records fetched
    891 ms, 681 write(s), 1009179 fetch(es), 236992 mark(s)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
RDB$INDICES                                       2                                                           
RDB$RELATION_CONSTRAINTS                          1                                                           
TDETL_CASC                                                        33521               33521                   

2014-09-01T00:25:40.9600 (27769:0x7f9ae1c61b90) ERROR AT JStatement::execute
        oltp30 (ATT_720, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31141
335544466 : violation of FOREIGN KEY constraint "TDETL_CASC_FK" on table "TDETL_CASC"
335544838 : Foreign key reference target does not exist
335545072 : Problematic key value is ("PID" = 1)

2014-09-01T00:25:41.0130 (27769:0x7f9ae1c6bf90)  EXECUTE_STATEMENT_FINISH 
        oltp30 (ATT_722, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:31387
                (TRA_443468, CONCURRENCY | NOWAIT | READ_WRITE)

Statement 985:
-------------------------------------------------------------------------------
delete from tmain_casc where id=1
0 records fetched
    340 ms, 98643 fetch(es), 1 mark(s)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
TMAIN_CASC                                        1                             1