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

>
> > A detail record can not exist without a master.
>
> Exactly my point! But the master record is actually being deleted
> before the detail.

Wrong. They occur in a single atomic operation. Of course, one byte
will be written before another at the disk level, but if a failure
occurred between deleting the master and deleting the detail, the
transaction would be uncommitted, and when you restarted the database
(providing the hard disk survived whatever caused the failure), the
delete from T1 would be rolled back.

>
> > the rule says that when a record is deleted from T1,
> > any records in T2 that point to T1 must be deleted.
>
> However the record from T1 is being deleted first, followed by the
> record from T2.
>
> > Your before delete trigger raises the exception.
>
> It is raising the exception because the record in T1 does not exist
> anymore.
>
> Isn't this a violation of referential integrity?
>

You need to read about Atomicity (the A in ACID).

Each operation in Firebird is atomic (in fact each transaction is
atomic as well).

So you issue 'delete from T1 where ID = 1'.

1. Record ID 1 removed from T1
2. Foreign Keys that point to T1 are deleted

You can not do anything between issuing the command and 2 completing.
If something happens to prevent the delete (Such as your exception),
then the entire operation is undone (otherwise referential integrity
would indeed be violated in this transaction).

That is why your delete from T1 fails, because Firebird will not let
you delete the master record and prevent the detail record from being
deleted.

Other transactions will not see the result of your delete until your
transaction commits, and snapshot transactions starting before you
commit will NEVER see the result of the delete. All transactions
starting after you commit will see the results of your delete.

Adam