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

Another view why you should go for triggers: Coding this in the database will be much faster then in any programming language.
Your developper will be ablee to write a procedure to _generate_ the necessary code for all tables.

create or replace procedure create_triggers declare
  stmt varchar2(4000); -- the generated SQL-code   nl varchar2(2);
  cursot tabls is
    select table_name from all_tables
    where _put_a_desent_clause_here_
begin
  nl:=chr(10);
  for tab in tabls loop
    stmt:='CREATE OR REPLACE TRIGGER'|| tab.table_name ||'trc BEFORE INSERT OR UPDATE ON'||nl; -- trigger is called TABLEtrc (trc for trace)

    stmt:=stmt|| tab.table_name ||' for each row'||nl||'begin'||nl||'if insserting then'||nl;

    stmt:=stmt||continue adding the code as below (no variable part any more)     

• execute the generated statement or save it in a temporary table insert into foo values (stmt); execute_immediate(stmt); end loop; end;

This way you can create triggers for your hundreds of tables in a minute...
Generating SQL with SQL is fun once you get used to it :-)

"Stjepan Brbot" <stjepan.brbot_at_zg.hinet.hr> wrote in message news:<ahsa3k$8j3f$1_at_as201.hinet.hr>...
> I think that your senior developer has right (expirience). It's better
> to maintain these 4 columns by one simple trigger because:
>
> 1) you do not have to care about these 4 columns in your application
> development
> 2) column will be maintained even rows in tables are updated/inserted by
> any external app (not only your application)
>
> here is the example of trigger often used for such a issue:
>
> --
>
> CREATE OR REPLACE TRIGGER triggername BEFORE INSERT OR UPDATE ON
> tablename FOR EACH ROW
> BEGIN
> IF inserting THEN
> :NEW.created_date:=TRUNC(sysdate);
> :NEW.created_by:=user;
> END IF;
> :NEW.modified_date:=TRUNC(sysdate);
> :NEW.modified_by:=user;
> END;
>
> --
>
> Stjepan Brbot
>
>
> "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 Mon Aug 26 2002 - 10:54:17 CDT