Thanks Richard, i'll do that.

Good Day.

Sorry to interject, but I was thinking about implementing RAISERROR within
my sprocs for things like ForeignKey constraints & CHECK Constraints.
RAISERROR would have thrown an exception with my own unique error number,
thus enabling me to handle any problems for example -:

Event Foreign Key Constraint: 'The Supplier chosen may have been deleted."
Event CHECK Constarint: 'Please specifiy a Supplier Type between 1 & 3'.

OK, the last error could actually be handled in the BL layer but I dont want
my application to show a general error message that could quite possible
relate to any number of named constraints.

Would you set up your database using Declarative Referential Integrity as a
failsafe method but use the BL layer to check for these constraints prior to
Inserting, Updating & Deleting?

If the answer is yes then would you execute the statements as individual
transactions, i.e.

BLObjMethod_InsertSupplierProduct
{
   BLmethod_ValidateSupplierTypeRange(...);
   DALmethod_CheckSupplierExists(...); <-- DB access required.
   DALmethod_CheckProductTypeExists(...); <-- DB access required.
   ...
}

or would you attempt to control the transactions from the BL layer, i.e.

BLObjMethod_InsertSupplierProduct
{
   BLmethod_ValidateSupplierTypeRange(...);

   DALmethodBeginTrans();
   DALmethod_CheckSupplierExists(...); <-- DB access required.
   DALmethod_CheckProductTypeExists(...); <-- DB access required.
   DALmethodEndTrans();
   ...
}

or would you make your DAL a bit more intelligent, i.e.
DALObjMethod_InsertSupplierProduct
{
   methodBeginTrans();
   method_CheckSupplierExists(...); <-- DB access required.
   method_CheckProductTypeExists(...); <-- DB access required.
   methodEndTrans();
   ...
}

I hope the above makes some sense and your answers would be greatly
appreciated.
Just like Gai it seems hard to find definitive answers or the 'Very Best of
Practices'.

Kind Regards
Paul Johnson.