| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Are Triggers Really that Bad or ....???
Ronnie,
here's why I always use triggers and RI and constraints on the database and not in code :
I have an app and my business rules state that each customer has to have
a sequential customer number (for example).
So I create a sequence in the database and whenever a new customer is
created, my app gets the sequence value, assigns it to the customer
number field and stores the data in the database. What could be wrong
with that ?
Well, what happens if I use SQLPLus to create a new customer - the
sequence number isn't populated and assuming my database designer failed
to make the field NOT NULL, I have crap data in the database.
Now, using a trigger, my app simply stores the data and the trigger fires (before insert for all rows) and if the customer number field is NULL, get a value from the sequence and store it. At the end of the trigger code, the new customer record fulfills my business rule.
If I come along with SQLPlus again, and do as I did above, I *will* get a customer number assigned.
If someone comes along with an Access front end and creates a customer, I still get a customer number assigned.
Business rules belong in the database and not in the app. The same applies to validation, referential Integrity etc - all of it has to be done as close to the data as possible - and that means, in the database.
For user friendliness in the application, you can still perform validation etc and give the user meaningful error messages when they get it wrong.
The fact that you mention inserting and updating in the same transaction is not a problem. Or are you creating a blank record and then updating various fields ?
HTH
Regards,
Norman.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com -------------------------------------
-----Original Message-----
From: Ronnie Yours [mailto:ronnie_yours_at_yahoo.com]
Posted At: Thursday, July 25, 2002 2:41 PM
Posted To: server
Conversation: Are Triggers Really that Bad or ....???
Subject: Re: Are Triggers Really that Bad or ....???
Thanks a Lot guys,
What are the reasons why each one of you and my Senior Developer
supports
this kind of functionality in Triggers and not the Code itself.
I understand from your replies that nowadays triggers are not that Bad
an
dont affect the performace as I was thinking. But such a thing should
still
be in the Code and not the Database.
Please note that I have to insert/update the columns in the same
transaction. I am not trying to preserve the old rows or maintaining a
audit
in a different table.
Please suggest
Thanks
Ronnie
"Ronnie Yours" <ronnie_yours_at_yahoo.com> wrote in message
news:ahmsm4$gd5$1_at_nntp-m01.news.aol.com...
> Hi,
>
> I am facing a wierd situation here.
>
> We have a database with around 150-200 Tables and growing . Each
Table
has
> 4 columns in additional to the ones for the application, namely
> created_by, created_date,updated_by and updated_date.
> Now when writing the application code the developers were supposed to
take
> care of the values in these fields. For example when a record is
> added/inserted the created_by and created_date fields are populated
and
when
> the records are updated the updated_by and updated_dates are
> populated/updated.
> But the issue is the developeres did not take care of this issue and
one
of
> the senior developers is suggesting that lets create triggers to
implement
> this functionality.
>
> My suggestion was and is that lets change the code to take care of
this
but
> he doesnt agree. He says it will take more time to do that.
>
> Now I am in a dilemma.
>
> If we implement triggers how will they affect the prformance.
> If I should not implement triggers then how should I go about handling
this
> situation and explain the same to my manager.
>
> Please Suggest
>
> Thanks
> Ronnie Yours
>
>
Received on Thu Jul 25 2002 - 09:14:27 CDT
 |
 |