| Subject | Re: triggers |
|---|---|
| Author | Adam |
| Post date | 2005-08-01T03:54:43Z |
--- In firebird-support@yahoogroups.com, "women_lover_best"
<talbronstien@g...> wrote:
I am by no means the authority on this, but be aware there is no one
size fits all approach to the age-old problem. Sometimes it makes
sense to do it in the database, sometimes not. If your application
has to run on multiple databases, then it makes sense to take care of
it where you do. If you are a Firebird shop, then a lot of validation
is dependent on other data, so it can be less expensive to do
it "inside" the database in the form of triggers and stored
procedures. But you do not really want to make a database connection
just to be told a given field can not be null.
I generally do the following:
At the UI level, I do basic validation (check certain edits are not
null etc). Irrelevant edits, checkboxes etc should be hidden. I also
set the maxlength on any edit, and a min and max if applicable on a
number edit.
We only support Firebird and there is no long term plan to introduce
any other DBMS, so our data access layer is doesn't really need to
perform any validation.
We use triggers to raise exceptions if the change will leave the
database in a bad state. If an edit box needs to be unique, we would
police that at the database level using a unique constraint.
So as far as duplication goes, it may cause a performance bottleneck.
To be honest, I see data consistency as a responsibility of the DBMS,
so if I design a table which has particular rules about whether
certain data is valid or not, then I enforce it from within the
database.
In theory, you can run whatever query you like against my database,
and if you manage to get it into an inconsistent state, then it is my
fault. (I said in theory, you need to go for the 80 20 rule
sometimes).
The database will raise an exception to prevent the inconsistent
state, and you do not handle that, that is your fault. Yes it may
cause a problem in a particular input screen, however letting the
inconsistent data into the table may lead to 50 reports not working.
Adam
<talbronstien@g...> wrote:
> Hi Adam,is..
>
> thanks a lot for such clear explanation..good learning for me..
> Now,our application is being developed in windows forms,.net
> based..our clients will always be desktop-based..Our architecture
> UI layer,Business Layer(all the business objects and validation,andclients..and
> rules),Data Access Layer and database..2-tier..multiple
> one server.on
> Why rules and validation in business layer..simple i dont want to
> make a network trip to database and do validation,when I can do it
> client,a smart client on which I will replicate some portion ofHi Vishy,
> database,...so what yu r suggesting is to duplicate all the
> validation and rules in database using triggers and stored
> procedures..rite??..
>
I am by no means the authority on this, but be aware there is no one
size fits all approach to the age-old problem. Sometimes it makes
sense to do it in the database, sometimes not. If your application
has to run on multiple databases, then it makes sense to take care of
it where you do. If you are a Firebird shop, then a lot of validation
is dependent on other data, so it can be less expensive to do
it "inside" the database in the form of triggers and stored
procedures. But you do not really want to make a database connection
just to be told a given field can not be null.
I generally do the following:
At the UI level, I do basic validation (check certain edits are not
null etc). Irrelevant edits, checkboxes etc should be hidden. I also
set the maxlength on any edit, and a min and max if applicable on a
number edit.
We only support Firebird and there is no long term plan to introduce
any other DBMS, so our data access layer is doesn't really need to
perform any validation.
We use triggers to raise exceptions if the change will leave the
database in a bad state. If an edit box needs to be unique, we would
police that at the database level using a unique constraint.
So as far as duplication goes, it may cause a performance bottleneck.
To be honest, I see data consistency as a responsibility of the DBMS,
so if I design a table which has particular rules about whether
certain data is valid or not, then I enforce it from within the
database.
In theory, you can run whatever query you like against my database,
and if you manage to get it into an inconsistent state, then it is my
fault. (I said in theory, you need to go for the 80 20 rule
sometimes).
The database will raise an exception to prevent the inconsistent
state, and you do not handle that, that is your fault. Yes it may
cause a problem in a particular input screen, however letting the
inconsistent data into the table may lead to 50 reports not working.
Adam