How to check in Firebird if a datarow can be deleted?

Question

I have a Firebird database with a table A, including ID primary key.
And several other tables like: table B, including A_ID foreign key to A.ID (on delete NO ACTION).

Now I want to check in a stored procedure if a row in A can be deleted.
Not actually delete it, only if it would be possible without errors.

But I don't want to check every other table that has a foreign key to A.

What I need is something like:

1.  

   safepoint X;  
   delete from A where ID = 1;  
   when any do  
   begin  
     return = 'false';  
   end  
   rollback to X;  
   

   But savepoints are not allowed in stored procedures.

Or

2. a way to check for an A.ID if there are rows in other tables referencing it.
   Without selecting every other table manually.

Is there a way to do this?

Answer 1

You could try to use the autonomous transactions introduced in Firebird 2.5 to do the check, and simply rollback afterwards. However this will probably introduce a deadlock problem because the row will be 'modified' by a different transaction than the parent transaction.

Ask yourself: why bother. Just try to delete and give the user a message if deletion wasn't possible.