Re: Table Trigger to set last_update_date field.

You're right, you cannot change new values in an after trigger.

I made a cut&paste too fast.
Thanks for the correction.

--
Regards
Michel

Kurt Krieger <kkrieger_at_erols.com> a écrit dans le message : 38C86035.365B3F6D_at_erols.com...
> You're assigning a value, so you have to use ':='...
> :new.last_update_date := sysdate;
>
> Also, shouldn't it be 'before insert or update'? Does setting :new.% do
anything
> after the physical record has been written? If the field last_update_date is
not
> null, and the record you are inserting/updating does not have a value in the
> field, then I believe you'll raise an error if you wait until 'after' to set
the
> value. I'd write it this way...
>
> create or replace trigger trg_storm_personnel_biur
> before insert or update on storm_personnel
> for each row
> begin
> :new.last_update_date := sysdate;
> end;
> /
> show error
>
> Kurt
>
> Michel Cadot wrote:
>
> > This will not work and you'll have a "mutating table" error ;
> > you have to use :new.last_update_date:
> >
> > create trigger set_update_date
> > AFTER INSERT or UPDATE on STORM_PERSONNEL for each row
> > begin
> > :new.last_update_date = sysdate;
> > end;
> > /
> >
> > --
> > Have a nice day
> > Michel
> >
> > Richard Billingsley <rbillingsley_at_deadend.com> a écrit dans le message :
> > ruPx4.9317$yV1.2222176_at_tw11.nn.bcandid.com...
> > > I have been asked to create a table trigger that will update the
> > > last_update_date field for each record that is inserted or updated.
> > >
> > > Not being a trigger expert I have created the following code:
> > >
> > > create trigger set_update_date
> > > AFTER INSERT or UPDATE on STORM_PERSONNEL for each row
> > > begin
> > >
> > > update storm_personnel
> > > set last_update_date = sysdate
> > > where storm_pers_id = :new.storm_pers_id;
> > >
> > > end;
> > > /
> > >
> > > Personally, I'm not sure the :new.storm_pers_id would work on an UPDATE,
> > > since it may not be part of the update statement. And perhaps there's
even
> > > an easier way of doing this that I'm not aware of.
> > >
> > > I don't know if this will work or not. Could some one experienced help me
> > > out with what's right or wrong with this.
> > >
> > > I really appreciate it.
> > >
> > > Thanks,
> > >
> > > Richard..
> > >
> > >
> > > --
> > > Richard Billingsley
> > >
> > > Note: To reply via email replace "deadend.com" with "vbig.com" before
> > > sending.
> > >
> > >
>
Received on Mon Mar 13 2000 - 01:34:42 CST