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

I overlooked that.
I overlooked it because you shouldn't use the user_id. It may be reassigned during a full database import. IMO you should store the username and change the column accordingly. If you don't want to do that, you need to retain the select and use :new.usr_id = curr_id;
but I advise strongly against that.
The insert definitely is inappropiate as you are already inserting/updating the table.

Regards,

Sybrand Bakker, Oracle DBA

dhruba <tapashc_at_my-deja.com> wrote in message news:8bgo8h$mdr$1_at_nnrp1.deja.com...
> Hi sybrand
>
> Thanks for your reply,
> one more issue
>
> in my table the "usr_id" column is number
>
> little more please
>
> rcy
>
>
> In article <953931297.22693.3.pluto.d4ee154e_at_news.demon.nl>,
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> > correct code
> >
> > create or replace trigger ins_tm_uid
> > BEFORE INSERT OR UPDATE ON testtable
> > FOR EACH ROW
> > ----
> > declare
> > > ----

 begin
> > :new.u_tm := SYSDATE;
> > :new.usr_id := user;
> > ----
> > end;
> > /
> > And that is ALL
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> > dhruba <tapashc_at_my-deja.com> wrote in message
> > news:8bgh4h$e6m$1_at_nnrp1.deja.com...
> > > 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;
> > >
> > > /
> > >
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Mar 25 2000 - 00:00:00 CST