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

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 14 Aug 2002 23:19:28 -0700
Message-ID: <92eeeff0.0208142219.193e5d3b@posting.google.com>

> > 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

Business rules *should belong* in the database and I think everyone here has roundly supported that idea. Specially when there could be multiple front ends...Win32 application based, browser based etc. It's much easier to rewrite/port a thin client then trying to rewrite/port business logic.

As far as the Triggers go, IMO... they should not be used to perform validation and referential integrity. Keeping in mind that data is the most important commodity in the database...One biggest caveat with Triggers is that if they quietly get disabled due to unknown reasons, users will not know anything about it because disabled triggers do not raise any errors...and if a trigger is used to do data validation or referential integrity...then the damage could be very painfull. Triggers are well suited for data replication.

IMO, data validation, referential integrity, inserts, updates and deletes should be done via a stored procedure... Here is why.

Stored procedures cannot be disabled. Either there is a valid procedure or invalid procedure will *always* raise an error no matter where it is called from.
Prevent any access to base tables, accept perhaps a select...which can also be resolved by creating a read only view. This will prevent anyone from accessing base tables thru back doors like Access or any other tool. Only access to base tables is via Stored procedure and that can be controlled by procedure execute priviliges.

In the original posters case,
An insert procedure...
CREATE OR REPLACE PROCEDURE insert_ (

   col1_ IN ....,
   col2_ IN ....,
   col3_ IN .... )
IS
   user_      .....;

   sysdate_ .....;
BEGIN
   sysdate_ := sysdate;
   select user into user_ from dual;

Do validation etc here. ... ... Raise exception in case validation fails. Insert into foo (col1, col2, col3, ..., created_by, create_date, updated_by, update_date) values(col1_, col2_, col3_, ...., user_, sysdate_, user_, sysdate_); Exception when dup_val_on_index then ...; END insert_; /

Call this procedure from client and pass appropriate parameters. Update and delete procedures can be setup this way and all this code can be managed in a package.

Since the topic of this thread is "Are Triggers Really that Bad or ....???"... and IMO, triggers are bad when they are *misused*.

If your senior developer wants to use triggers due to project time/money constraints, then maybe you should go with it. However, it is always a good idea to explore all possible solutions. A well planned design up front can save a lot of time and money in the long run.

Regards
//Rauf Sarwar Received on Thu Aug 15 2002 - 01:19:28 CDT