Re: Oracle triggers

Just a side note.

Using the USER in a trigger causes a recursive select user from dual; While the statement is not that expensive alone a database with many tables that use this type of trigger will end up with many calls to select user from dual.

The idea our shop came up with is to store the USER in a package and then set :new.dtInsert := PackageName.Username;

That way the select was only done once for each instance.

Scott W
Calgary Canada.

<michael_bialik_at_my-deja.com> wrote in message news:80cp62$hhf$1_at_nnrp1.deja.com...
> Hi.
>
> Try :
> CREATE TRIGGER set_insert_date_and_user BEFORE INSERT ON tab_name
> FOR EACH ROW
> BEGIN
> :new.vchInsertBy := USER; /* Set inserting user id */
> IF :new.dtInsertDate IS NULL THEN
> :new.dtInsertDate := SYSDATE;
> END IF;
> END;
>
> HTH. Michael.
>
> In article <80cibs$bsp$1_at_nnrp1.deja.com>,
> jdefreitas_at_my-deja.com wrote:
> > Hi. I am running Oracle 8.0.5, and have a question
> > about triggers.
> >
> > On our tables there are "dtInsertDate" and
> > "vchInsertBy" fields. I want to alter the
> > "TI" triggers (the triggers that execute when a
> > record is inserted) so that if a user does not
> > supply the dtInsertDate, the field will be set to
> > sysdate.
> >
> > I was wondering if anyone had some PL/SQL
> > examples of this, and also whether the trigger
> > should fire before or after the insert (I assume
> > after).
> >
> > Thanks! - John G. de Freitas
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Nov 10 1999 - 20:16:33 CST