preventing duplicate row insertion from asp.net app

[aboesteanu]

Data is stored in SQL Server 2000. One table is Person another Item.
Each row in the Person table may have associated with it several rows
in the Item table. The Item table has a field called 'type'. Business
rule: for certain values of this field, at most one row may exist (per
Person) in the Item table.

The DB is accessed from an ASP.NET app and more than one user may try
to insert an Item of the same type (and for the same Person) at the
same type. It would be very easy to ensure the above business rule
using a trigger (on the Item table) or a stored procedure that would
do inside something like this:
IF (NOT EXISTS (SELECT ... FROM Item)) INSERT INTO Item...

Please note that, because of internal coding standards, I cannot
modify the DB and I cannot access the database from my app through
direct SQL statements (e.g., like the one above). I can only access
the Item table through stored procedures that perform inside them
simple selects and inserts on the table.

Assuming the stored procedures (for SELECT and INSERT on the Item
table) are called inside the same method, is there a way to satisfy
the above mentioned business rule (ensure mutual exclusion) at the app
level? Would specifying the isolation level as serializable at the app
level work? Is there a better solution?

Thank you,
Alex

 

[aboesteanu]

Jerry,
Unortunately, I might not be able to modify the DB. (Besides, I do not
see how the SPECIFIED business rule would be enforced through a
primary key.)
I would still need to know if there is a way to enforce the business
rule at the app level.

Thanks,
Alex

 

[Jerry III]

You can have a primary key spanning more than one column (which would be the
case of your specific rule).

And yes, there is a way, you will simply check if the value exists before
you try to insert it. There are multiple ways to do that and none will be
100% fool proof, if you forget to do it once (or someone else working with
the same data forgets to do it) or don't do it correctly (with regards to
multiple requests executing simultaneously) you will end up with data that
are "corrupted" based on your rules. You can do that checking with triggers,
stored procedures, or plain SQL, it's entirely up to you...

Jerry