In a given table I have two records that are related to each other. In the first a pointer points to the second, and in the second the pointer points to the first. The requirement is to delete either, and by trigger action to delete the other.
I have tried deleting the second in the after delete trigger of the first record by using
Delete from table where key = <second record>
thinking that since this is after delete by the time the trigger runs for deletion of the second record, the first record would no longer exist. However the result is that neither record is deleted.
I have tried deleting the cross reference from the second record in the before delete trigger of the first record using
Update table set cross reference to 0 where key = <second record>
to stop the trigger having any affect when called the second time. Still neither record is deleted.
It is as if the changes are not actually applied to the database until all actions have been recorded, hence the first approach fails because when the second trigger runs the first record does still exist, and in the second case the change to the second record is not applied by the time the second trigger runs. Thank goodness Firebird recognises the un-implied loop and stops.
The script is over 2,000,000 lines and must have a thousand situations where a record is deleted, so please don't ask me to change the script. I have googled this many times but all references to delete are either simple references or for multiple records known at the time the script is run. And remember this is Firebird 2.5.
Does anybody know how to do this from the triggers?
Create table Tab (Tab_key integer not null primary key, Cross_ref integer);
Insert into Tab (Tab_key, cross_ref) values (1,2);
Insert into Tab (Tab_key, cross_ref) values (2,1);
Create trigger A_Delete for Tab After Delete
as
begin
Delete from Tab where Cross_ref= old.Tab_Key;
end
Delete from Tab where Tab_Key = 1; /*Should delete both records*/
Must be a grey moment, I have just tried this simple example and it works. So my code is wrong or something else in my code is conflicting.