Re: pl/sql - field level timestamp trigger ?

Thanks a lot for your help. I find it surprising that advanced pl/sql functionality such as Dynamic SQL which I have heard brief mention of do not handle exactly what I require - it would be nice to not have to ammend the trigger/s whenever a new field is added or removed from a table.

Thanks!

P

On Wed, 29 Mar 2000, Ole Bredesen-Vestby wrote:

>
> Create a BEFORE UPDATE OR INSERT trigger with something like this
>
> if nvl(:new.FIELD1,' ') <> nvl(:old.FIELD1,' ') then
> :new.mod_FIELD1 := sysdate;
> end if;
> if nvl(:new.FIELD2,' ') <> nvl(:old.FIELD2,' ') then
> :new.mod_FIELD2 := sysdate;
> end if;
> ...
> ...
> You have to do this for every field. This will only set the mod-field
> if the value in the field actually has changed.
>
> - Ole Bredesen-Vestby
>
> On Wed, 29 Mar 2000 13:17:15 +1000, Peter Skipworth
> <skip_at_netwide.com.au> wrote:
>
> >I hope someone can help with this - I am still an Oracle newbie, but am
> >getting to grips with it reasonably quickly.
> >
> >For several fields in my table, I have matching mod_FIELDNAME fields,
> >which contain the date and time at which the corresponding field was
> >last UPDATEd or INSERTed.
> >
> >For example
> >
> >MOD_DESCRIPTION stores the date at which the DESCRIPTION field was last
> >altered.
> >
> >I need a trigger to do something like
> >
> >for every FIELD in the update/insert command {
> > if a mod_FIELD field exists {
> > new.mod_FIELD = :sysdate;
> > }
> >}
> >
> >Can someone please offer some advice! Please cross-post the rreply to my
> >email address (skip_at_realestate.com.au) as I do not have time to check
> >this newsgroup very often.
> >
> >Thanks,
> >
> >Peter Skipworth./
>

-- 
.-----------------------------------------------------.
|       Peter Skipworth        Ph: 03 9897 1121       |
|      Senior Programmer      Mob: 0417 013 292       |
|      realestate.com.au   skip_at_realestate.com.au     |
`-----------------------------------------------------'