We have a tabel Sales which has a reference to table Products. If we delete the Sales entry, we want to delete de Products-entry. In the rare case that some other table might still have a reference to it, it decrements some counter in the products table.

Implementation

In the after delete trigger, we have a call to stored proc DeleteProduct();

Product table has restrict on delete constraint on its primary key.

The stored proc is defined something like this:

create procedure DeleteProduct(in @ProductsId integer)
begin
  declare @Cannot_Delete_1 EXCEPTION FOR SQLSTATE '23W05';
  declare @Cannot_Delete_2 EXCEPTION FOR SQLSTATE '23503';

  begin
    delete from Products where ProductsId = @ProductsId;
    exception  when @Cannot_Delete_1,@Cannot_Delete_2 then
    begin
        update Products set SomeRandomThing = SomeRandomThing - 1 where ProductsId = @ProductsId;
    end;
  end;
end;

Problem

This used to work, until recently (migration to ASA12?) some users start reporting problems when deleting sales with a product that is refered to from other tables: SQLSTATE 23503 is raised?!?!

Analyzing the problem seems to indicate that no exception is raised in the stored procedure (traces indicate that the SQL_STATE = 000000 in both the stored proc and trigger, but that the sql statement delete from Sales where SalesId = .... does raise the exception.

If I call the stored proc from InteractiveSql with the ProductId, the stored proc behaves as intended.

Inlining the code from the stored proc does not have an effect on the observed behaviour.

What is causing this behaviour?