Re: saving username and sysdate on insert/update

Johnny and Chris,

One problem with using the default columns option. They can be over written by SQL*Plus. If you use the trigger route, then there is no way that the modification can be falsified.

i.e. INSERT INTO tab ( ..., addusr ) values (..., 'FRED');

Also, with the Update, you may want to set ":new.addusr := :old.addusr", so that the field cannot be changed. Same with adddte.

Depends on the level of security you want in the solution.

Just a comment,
Andrew

BTW - Totally agree with making the columns more meaningful, but try and keep under 12 characters (some stupid performance issue with Oracle7 means that column names less that 12 characters are processed faster than those longer). I was told this by Oracle Consulting.

Chris Hamilton wrote:

> On Sat, 17 Apr 1999 21:39:08 +0200, "Sybrand Bakker"
> <postbus_at_sybrandb.demon.nl> wrote:
>
> >Hi Johnny,
> >before insert and before update triggers should do the trick
> >create or replace trigger xyz on table_name before insert or update for each
> >row
> >begin
> >if inserting then
> >new.addte := sysdate;
> >new.addusr := user;
> >else
> >new.upddte := sysdate;
> >new.updusr := user;
> >end if;
> >end;
>
> On insert, you don't need the trigger at all. Just set the DEFAULT
> value of the column to the value:
>
> alter table X modify (adddte default sysdate, addusr default
> rtrim(user));
>
> Note you should use rtrim(user) instead of simply the pseudo-column
> "user", since this column is blank-padded out to 30 characters.
>
> Another suggestion -- pick better names for your columns. You've got
> 30 characters to use, might as well make them something that will be
> clearer to future developers.
>
> For instance, adddte could be create_date, addusr could create_user,
> upddte could be update_date, updusr could be update_user, or something
> along those lines. No need to be so cryptic, this isn't dBASE III+
> with the limited field names.
>
> Chris
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Christopher Hamilton
> Oracle DBA -- Wall Street Sports
> chris_at_wallstreetsports.com
> http://www.wallstreetsports.com/
Received on Mon Apr 19 1999 - 08:14:00 CDT