Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Are Triggers Really that Bad or ....???

Are Triggers Really that Bad or ....???

From: Norman Dunbar <>
Date: Thu, 25 Jul 2002 15:14:27 +0100
Message-ID: <>


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 Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
Tel: 0113 289 6265
Fax: 0113 289 3146

-----Original Message-----
From: Ronnie Yours [] 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

"Ronnie Yours" <> wrote in message news:ahmsm4$gd5$
> Hi,
> I am facing a wierd situation here.
> We have a database with around 150-200 Tables and growing . Each
> 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
> care of the values in these fields. For example when a record is
> added/inserted the created_by and created_date fields are populated
> 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
> the senior developers is suggesting that lets create triggers to
> this functionality.
> My suggestion was and is that lets change the code to take care of
> 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
> situation and explain the same to my manager.
> Please Suggest
> Thanks
> Ronnie Yours
Received on Thu Jul 25 2002 - 09:14:27 CDT

Original text of this message