Re: *****time stamp on same table:pl/sql****

From: dhruba <tapashc_at_my-deja.com>
Date: 2000/03/24
Message-ID: <8bgn6f$lbk$1@nnrp1.deja.com>#1/1

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. Received on Fri Mar 24 2000 - 00:00:00 CST