Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: saving username and sysdate on insert/update
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