Re: Table Trigger to set last_update_date field.

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 Thu Mar 09 2000 - 20:38:45 CST