"select..into" inside Firebird stored procedure

I use Firebird 2.5 64bit edition. I have two tables Master (A) and Detail (B) and I set cascade update and delete for B so if I delete a record in the master any related records in the detail will be deleted as well

I setup an an After Delete trigger for table B that executes and passes parameters to a stored procedure

That stored procedure has this SQL:

  select STATUS from A
  where A.PK_id = :PK_id
  INTO :var_status;

The problem is that I always get NULL for the variable var_status although I checked it in SQL editor and I get 1 which is the correct value, I also checked (using IBexpert debugger) the passed parameter :PK_id and it is also correct!

Why do I get the wrong value stored in this varia开发者_开发百科ble.

The probable problem is that you are using AFTER DELETE and the record is not there anymore. Here's the order of actions:

• Record gets deleted from A
• Deletion from A cascades to deletion on B
• The AFTER DELETE trigger in B gets called.
• From within the trigger, you try to access data not from B, but from A. It's not there anymore.

Remember that the trigger runs inside a transaction. So, probably, when you run the same SELECT, you can access the value because you are in another transaction and the original transaction has not been committed yet.

This is not so trivial actually. Here are a few options to solve your issue:

• Check if your business rules can be changed so that you can run that part of code not in a trigger in B, but in A, where the status will be available. After all, there's something you need to do because of A, not B.
• Ultimately, you can remove the cascade delete and handle the deletion of B from within the AFTER DELETE trigger in A. That way, everything will be in the same block of code.