Hello Team IBO

I have not been able to solve the following problem and would appreciated advice. (D5, IBO 3.6Cf, Win2K, IB 6.0.0.627)

I have tables SITES and SITE_DATA. Field SITE_DATA(SITE) has a foreign key constraint with field SITES(SITE). If a site 'Auckland' is deleted from SITES then a cascade delete should occur and all records of site 'Auckland' in SITE_DATA should be deleted.

When I perform a 'delete from sites where site='Auckland'' using IBConsole there is no problem. The SITES record is deleted and all the 'Auckland' records in SITE_DATA are deleted.

In my application I have a connection cn, a transaction trWork, and an IB_DSQL dsqlWork.

If I specify dsqlWork to use a default transaction (its IB_Transaction is <default>) then programatically doing a delete of site 'Auckland' from table SITES which has "Auckland' records in SITE_DATA proceeds with no error.

Here is the procedure I use.

procedure DoSQLWork( mySQL : String);
begin
with DataModule1.trWork do
begin
try
StartTransaction;
with DataModule1.ibDSQLWork do
begin
SQL.Clear;
SQL.Add(mySQL);
Prepare;
Execute;
Unprepare;{IB folklore says do this to avoid a bug. Haven't found a good reference to this bug.}
end;{with ibDSQLWork}
finally
Commit;
end;{try}
end;{with trWork}
end;{DoSQLWork}

If I specify that dsqlWork use transaction trWork (Its IB_Transaction property is trWork) then the deletion yields a violation of the foreign key constraint at the point of ibDsqlWork.Execute. ISC Error Code 33554466, a violation of the foreign key relation. No records in either table are deleted. Perform the deletion a second time and the deletion is done without error. The error is produced no matter what the tr.AutoCommit value is.

DoSQLWork is used for tasks requiring immediate effect.

I assume the properties of my explicit tr are different than the <default> transaction but haven't dug deeper.

Any help appreciated.

My solution is to set the ibDSQL.Work.IB_Transaction to <default> but I don't know if this makes sense, and it may leave a hidden problem dormant.

Thanks

Russell



[Non-text portions of this message have been removed]