When are Exceptions raised in trigger

[Former Member]

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?

[VolkerBarth]

I'm not sure I understand the problem, however, may the option "wait_for_commit" make a difference here? - At least it can be set connection-specific, and that may prevent a FK violation error appear until a COMMIT is made... - So, if that option is set, I would expect that the above trigger would not raise an exception.

[Former Member]

Thanks for your reply... I did not know this option and I have checked it, but it is set to 'Off' on our connections...turning it on, didn't affect the outcome. Option optimistic_wait_for_commit is also turned off.

The '23503' SQLSTATE is expected and used to be set on deleting the product table, now it seems to be set after deleting the sales table, so at the end of the transaction.

[MCMartin]

Do you have a foreign key with "delete cascade"? Because then the foreign key will already try to delete your product which fails, before your trigger is fired

[Former Member]

No, it's set to restrict on delete.