--- In firebird-support@yahoogroups.com, "lobolo2000"
<lobolo2000@...> wrote:

>
> Shouldn't FB2 delete master records after detail records if an 'on
> delete cascade' clause is used (otherwise the referential integrity
> would be violated)?

Of course not.

The integrity constraint is the other way around. A detail record can
not exist without a master.

>
> An example to illustrate the point:
>
> CREATE EXCEPTION EX_ERROR 'Master record should be deleted after
> detail record';
>
> CREATE TABLE T1(
> U1 BIGINT PRIMARY KEY);
>
> CREATE TABLE T2(
> U2 BIGINT PRIMARY KEY,
> U3 BIGINT REFERENCES T1(U1) ON DELETE CASCADE);
>
> SET TERM ^;
>
> CREATE TRIGGER TR2 FOR T2
> ACTIVE BEFORE DELETE POSITION 0 AS
> BEGIN
> IF (NOT EXISTS(SELECT U1 FROM T1 WHERE T1.U1=1)) THEN
> EXCEPTION EX_ERROR;
> END^
>
> SET TERM ;^
>
> COMMIT;
>
> INSERT INTO T1 VALUES(1);
>
> INSERT INTO T2 VALUES(1,1);
>
> DELETE FROM T1;

Not sure about the syntax, maybe valid but I always declare it as a
constraint, but the rule says that when a record is deleted from T1,
any records in T2 that point to T1 must be deleted.

>
> COMMIT;
>
> The exception is triggered! Any ideas?
>

Well you are causing a delete from T2 by deleting the master record
from T1. Your before delete trigger raises the exception.

Look at your problem another way.

If I had a table of employees, and a table of countries, would you
delete the country just because you deleted an employee who came from
that country?

Adam