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

>
> > It is impossible to end up with a record in the detail table without
> a
> > corresponding record in the master table with Firebird's
> > implementation.
>
> Absolutely true.
>
> But the question is: should we be able to execute statements while the
> detail records have no master record, or in more general terms when
> referential integrity is violated though temporarily?
>
> Example: Consider having a flag field in the master record that
> allows/prevents deletion of detail records. In the before delete
> trigger of the detail table, the master record's flag is checked to
> decide whether to allow the deletion or throw an exception. If a
> delete of the master record is done (triggering a cascaded delete of
> detail records) the detail records would find out that they are
> orphans. Of course this can be easily circumvented with a before
> delete trigger on the master table, but this is besides the point.
>

I don't think that is a problem, it does not matter (IMO of course) if
consistency is broken within a single atomic operation, providing that
the consistency break is undone before the atomic operation is complete.

Looking at a parallel between atomic operations and transactions which
are also atomic, I think an example of how it works with transactions
may be easier to understand. Think about a transfer of money between
two accounts.

create procedure transfermoney
(
acc1 integer,
acc2 integer,
amt integer
)
as
begin
update account set balance = balance - :amt where ID = :acc1;
update account set balance = balance + :amt where ID = :acc2;
end
^

execute procedure transfermoney(1,2,100);

Obviously, you can argue that consistency is violated temporarily
within the stored procedure between the update statements. But the
stored procedure is executed as an atomic operation, so it either
entirely succeeds or entirely fails. (it wont 'entirely succeed' until
the transaction commits).

If I was to raise an exception between the two update statements, then
the entire work of the stored procedure would be undone.

I think to argue that it must be done in a before delete sense is a
bit fussy considering it has no impact on the result in any situation,
even a theoretical situation.

Don't forget to that because of MGA, your transaction will not
necessarily be able to see all the detail records. The current
implementation deals nicely with that.

Adam