Does table constraints execute in the same transaction?
I have a transaction with Read Committed isolation level which inserts some rows in a table. The table has a constraint on it that calls a function which in turn selects some rows from the same table.
It looks like the function runs without knowing anything about the transaction and the select in the function returns rows in the table which were there prior to the transaction.
Is there a workaround or am I missing anything? Thanks.
Here are the codes for the transaction and the constraint:
insert into Treasury.DariaftPardakhtDarkhastFaktor
(DarkhastFaktor, DariaftPardakht, Mablagh, CodeVazeiat,
ZamaneTakhsiseFaktor, MarkazPakhsh, ShomarehFaktor, User)
values
(@DarkhastFaktor, @DariaftPardakht, @Mablagh, @CodeVazeiat,
@ZamaneTakhsiseFaktor, @MarkazPakhsh, @ShomarehFaktor, @User);
constraint expression (enforce for inserts and updates):
([Treasury].[ufnCheckDarkhastFaktorMablaghConstraint]([DarkhastFaktor])=(1))
ufnCheckDarkhastFaktorMablaghConstraint:
returns bit
as
begin
declare @SumMablagh float
declare @Mablagh float
select @SumMablagh = isnull(sum(Mablagh), 0)
from Treasury.DariaftPardakhtDarkhastFaktor
where DarkhastFaktor= @DarkhastFaktor
select @Mablagh = isnull(MablaghKhalesFaktor, 0)
from Sales.DarkhastFaktor
where DarkhastFaktor= @DarkhastFaktor
if @Mablagh - @SumMablagh < -1
return 0
return 1
end
Best Answer
Check constraints are not enforced for delete operations, see http://msdn.microsoft.com/en-us/library/ms188258.aspx
Edit - to answer your question on workaround, you can use a delete trigger to roll back if your function call shows an invariant is broken.
Edit #2 - @reticent, if you are adding rows then the function called by the check constraint should in fact see the rows. If it didn't, check constraints would be useless. Here is a simple example, you will find that the first 2 inserts succeed and the third fails as expected: