I do not how I missed this yesterday, but you should not be inserting
into the same table that caused the trigger to fire. You can just
assign the values as in :new.col = user;
In article <8bgn6f$lbk$1_at_nnrp1.deja.com>,
dhruba <tapashc_at_my-deja.com> wrote:
> Hi mark ,
> Thanks in advance.
>
> LINE/COL ERROR
> --------
> --
> 10/9 PLS-00103: Encountered the symbol "END" when expecting one of
> the
> following:
> , ; return RETURNING_
> The symbol ";" was substituted for "END" to continue.
>
> here is the code
>
> create or replace trigger ins_tm_uid
> AFTER INSERT OR UPDATE ON testtable
> FOR EACH ROW
> declare
> time_now date;
> curr_uid user_users.username%type;
> begin
> time_now := SYSDATE;
> select username into curr_uid from user_users;
> if inserting then
> insert into testtable (usr_id,u_tm) values (curr_uid,time_now)
> ---- where name=:new.name;
> end if;
> ----
> IF UPDATING ('name') THEN
> INSERT INTO testtable (usr_id,u_tm) values(curr_uid,time_now);
> ---- where name = :new.name;
> end if;
> end;
> /
>
> dhruba
>
> In article <8bgkol$ia6$1_at_nnrp1.deja.com>,
> markp7832_at_my-deja.com wrote:
> > In article <8bgh4h$e6m$1_at_nnrp1.deja.com>,
> > dhruba <tapashc_at_my-deja.com> wrote:
> > > Hi
> > >
> > > I have a table testtable
> > >
> > > name vc2(10)
> > > age number
> > > usr_id number
> > > u_tm date
> > >
> > > i want a trigger so that for any insert or update
> > >
> > > usr_id will get userid from user_users
> > > and u_tm will get current timestamp;
> > >
> > > the trigger is giving an error.
> > >
> > > create or replace trigger ins_tm_uid
> > > AFTER INSERT OR UPDATE ON testtable
> > > FOR EACH ROW
> > > ----
> > > declare
> > > time_now date;
> > > curr_uid user_users.user_id%type;
> > > ----
> > > begin
> > > time_now := SYSDATE;
> > > ----
> > > select username into curr_uid from user_users;
> > > if inserting then
> > > insert into testtable (usr_id,u_tm) values
> > > (curr_uid,time_now)
> > > where name=:new.name;
> > > end if;
> > > ----
> > > end;
> > >
> > > /
> > >
> > You did not list the error message so I will take a chance at being
> > wrong. I believe that whatever error you are getting may be related
to
> > the trigger code effectively running as the table owner so try using
> > the psuedo column 'user' instead of selecting from user_users and
see
> > if this eliminates the error. Otherwise please post the actual error
> > messages along with the trigger code.
> > --
> > Mark D. Powell -- The only advice that counts is the advice that
> > you follow so follow your own advice --
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Mar 25 2000 - 00:00:00 CST
