I don't think there's really a right answer to this - but I will try to give
my $.02 on the subject. When you split up the work in an n-tier architecture,
there are many different things to do based upon your environment and your
application. You can start out by being rigid on certain techniques, but you
are going to find that you will have to bend the rules a bit in different
scenarios.
#1 - make use of your database engine where appropriate. There are definite
performance gains to be had when you use the database engine to do certain
activities. In other words there are times when you don't want to bring over
data to your middle tier to process it because of speed. A lot of times
writing stored procedures are the way to go when you have to do database
activities with a lot of volume.
#2 - make sure you know where your code is. If you are working in an
environment with multiple developers, a lot of times it's nice to have ALL of
the logic in the middle tier, because everyone knows where to look for the
code. If you start writing code in triggers and stored procedures, make sure
the team (and yourself) knows that this is occurring. There's nothing worse
than debugging a routine and 'magic' seems to be happening to your data. And
of course it's happening due to a stored procedure or trigger that everyone
has forgotten was in the database.
#3 - a lot of times you can't do it in the database. For example let's say
you have some complex logic that needs to occur on every row of a cascading
delete. Sometimes doing this in the database can be a nightmare and lead to
bad recursion potential, etc. Or during this process you need to return some
key values to some other functions etc. You may be forced not to put your
cascades in the database, and you may have to drop back to the middle tier to
perform the complex logic. (however this might not be true anymore in SQL
2005 - i have yet to explore this)
A good way to approach this might be to put all of your cascading deletes in
the database to start off with. Then, as situations arise, move the ones that
can't be done in your database out to the middle tier...
....I'm not sure there is a right answer here for every situation. You want
to try to make some standards for your project, and try to adhere to them in
most situations as best you can. Real world development however likes to
intrude on even the most sincere plans and you may end up violating your
standards once or twice (or your norms) simply to get the user what they
really need.
--
Franklin M. Gauer III
Applications Development Manager
Integrated Companies, Inc.
"Nemisis" wrote:
Hi everyone,
Wonder if anyone can shed some light on how i should setup cascade
deletes of object data within my database.
One option is to use SQL triggers, is this a good idea? Should the
logic be in my business layer instead? or maybe even the data access
layer?
I also have the problem when performing an insert. If i insert a
record into one table, i also need to insert other values into foreign
key tables. Again, where do i do this?
This is my first 3 tier application and i am not sure which way i
should set this up, so any help would be greatful.
Thanks in advance
