DELETE CASCADE and triggers

[johngordon75]

SERVER version WI-V6.3.5.33330 Firebird 3.0

Create 2 tables with relations. Here the create script

SET SQL DIALECT 3;

/*
drop TABLE TABLE2;
drop TABLE TABLE1;

drop DOMAIN T_INT_ID;
drop EXCEPTION E_DEBUG_EXCEPT;
*/

CREATE DOMAIN T_INT_ID AS INTEGER NOT NULL;

CREATE EXCEPTION E_DEBUG_EXCEPT 'E_DEBUG_EXCEPT';

CREATE TABLE TABLE1 (
    ID  T_INT_ID
);

ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (ID);


CREATE TABLE TABLE2 (
    ID  T_INT_ID,
    TABLE1_ID T_INT_ID
);

ALTER TABLE TABLE2 ADD CONSTRAINT PK_TABLE2 PRIMARY KEY (ID);

ALTER TABLE TABLE2 ADD CONSTRAINT FK_TABLE2_1 FOREIGN KEY (TABLE1_ID) REFERENCES TABLE1 ON DELETE CASCADE;

SET TERM ^ ;

CREATE OR ALTER TRIGGER TABLE2_BD0 FOR TABLE2
ACTIVE BEFORE DELETE POSITION 0
AS
begin
  if( not exists(select 1 from table1 where table1.id=old.TABLE1_ID) ) then exception E_DEBUG_EXCEPT;
end
^

SET TERM ; ^

INSERT INTO TABLE1 (ID) VALUES (1);
INSERT INTO TABLE1 (ID) VALUES (2);
INSERT INTO TABLE1 (ID) VALUES (3);

INSERT INTO TABLE2 (ID,TABLE1_ID) VALUES (1,1);
INSERT INTO TABLE2 (ID,TABLE1_ID) VALUES (2,2);
INSERT INTO TABLE2 (ID,TABLE1_ID) VALUES (3,3);


commit;

now delete record

delete from TABLE2 where id=3

works fine

and now delete record with cascade delete

delete from TABLE1 where id=1

and got the error (we have no master record???)

E_DEBUG_EXCEPT.
E_DEBUG_EXCEPT.
At trigger 'TABLE2_BD0' line: 5, col: 77
At trigger 'CHECK_37

So, we can see next (wrong?) behavior

1. TABLE1 BEFORE DELETE TRIGGER
2. TABLE1 DELETE RECORD
3. TABLE1 AFTER TRIGGER
4. CASCADE DELETE OF TABLE2
5. TABLE2 BEFORE DELETE TRIGGER
6. TABLE2 DELETE RECORD
7. TABLE2 AFTER DELETE TRIGGER

Looks like the BUG?

Old version of Interbase and Firebird work with right steps:

1. TABLE1 BEFORE DELETE TRIGGER
2. CASCADE DELETE OF TABLE2
3. TABLE2 BEFORE DELETE TRIGGER
4. TABLE2 DELETE RECORD.
5. TABLE2 AFTER DELETE TRIGGER
6. TABLE1 DELETE RECORD
7. TABLE1 AFTER DELETE TRIGGER

and database restored from backup of old Interbase/firebird has right behavior. Only new created database (and new tables in old database) has such new behavior.

I can restore normal behavior when manually delete records from TABLE2 in the TABLE1 BEFORE DELETE trigger. But so there is no point use CASCADE delete in such case.

[aafemt]

No, this is correct and intended behavior. This is the only way to prevent insertion of new detail records during deletion of the master.

[johngordon75]

insertion of new detail records during deletion of the master.

But this is the logical error of database developer. What is the purpose of such actions?