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.
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;
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?
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
answered 27 Jan '14, 11:08