Hello all,


I've just run some tests using pre_erase triggers
to implement cascade/set null/set default for foreign
key constraints. Those tests suggest that the internal
logic works just as well with pre-erase triggers as with
post-erase triggers. Pre-erase triggers for foreign
key constraints also give more consistent results if
the referencing table has pre-operation triggers.

The description of the tests follows. The synopsis
is that the execution of the constraint recognizes
uncommitted data in referencing tables that depends
on a record in the referenced table and blocks the
delete from the referenced table. It also blocks
attempts to insert new data in the referencing table
after a delete has started in the referenced table.

I've probably missed something, but it seems as
if it would be safe to change the trigger type on
foreign key triggers.

Regards,


Ann

The test used two tables, t1 and t2. T1 has one field
called f1 which is its primary key. T2 has two fields,
f1 which is the primary key for t2 and f2 which references
t1(f1) with "on delete cascade". I twiddled the system
table to make the CHECK_n trigger a pre_erase rather than
a post erase trigger. T1 contains one row with the value
1. T2 starts with one row with the value (1,1). All
that data is committed.

In each test, there was a breakpoint on the call to
VI0_erase from erase in exe.cpp. The tests hit the
breakpoint for the records in both tables, but continued
through the records in t2, stopping only for t1.


For the first test, the order of operations was

Transaction 1 Transaction 2

insert into t2 (2, 1);
delete from t1 where f1 = 1;
perform pre-operation triggers;
wait
commit;
resume

Transaction 2 got an error on its delete from t1;

The second test:

Transaction 1 Transaction 2

delete from t1 where f1 = 1;
perform pre-operation triggers;
wait
insert into t2 (2, 1);
<blocked>
resume
commit;

Transaction 1 got foreign key constraint error on its insert.