RI oddness

Hi,

I just got trapped by one of my own features in the
referential integrity area.

The problem is, that the trigger run on the FK row at UPDATE
allways checks and locks the referenced PK, even if the FK
attributes didn't change. That's because if there'd be an ON
DELETE SET DEFAULTS and someone deletes a PK consisting of
all the FK's column defaults, we wouldn't notice and let it
pass through.

The bad thing on it is now, if I have one XACT that locks the
PK row first, then locks the FK row, and I have another XACT
that just want's to update another field in the FK row, that
second XACT must lock the PK row in the first place or this
entire thing leads to deadlocks. If one table has alot of FK
constraints, this causes not really wanted lock contention.

The clean way to get out of it would be to skip non-FK-change
events in the UPDATE trigger and do alot of extra work in the
SET DEFAULTS trigger. Actually it'd be to check if we're
actually deleting the FK defaults values from the PK table,
and if so we'd have to check if references exist by doing
another NO ACTION kinda test.

Any other smart idea?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com