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?

asked 27 Jan '14, 08:59

dannyg's gravatar image

dannyg
31114
accept rate: 0%

edited 27 Jan '14, 10:05

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.

(27 Jan '14, 10:26) Volker Barth
Replies hidden

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.

(27 Jan '14, 10:42) dannyg

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

permanent link

answered 27 Jan '14, 11:08

Martin's gravatar image

Martin
8.6k116150237
accept rate: 14%

No, it's set to restrict on delete.

(28 Jan '14, 02:03) dannyg
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×409
×60
×18

question asked: 27 Jan '14, 08:59

question was seen: 1,488 times

last updated: 28 Jan '14, 02:03