Kategorie: Allgemein

Wenn ich die Wahl habe, erstelle ich eine Tabelle in Oracle-Datenbanken mit folgender Grundstruktur:

create table NEW_TABLE (
    id number not null 
  , my_column varchar2(200)
  , created_on date 
  , created_by varchar2(200)
  , updated_on date 
  , updated_by varchar2(200) 
);

create unique index NEW_TABLE_pk on NEW_TABLE (id asc);

alter table NEW_TABLE
add constraint NEW_TABLE_pk primary key (id)
using index NEW_TABLE_pk enable;


create sequence NEW_TABLE_seq increment by 1;


create or replace trigger NEW_TABLE_trg 
    before insert or update on NEW_TABLE
    for each row
declare
    v_user varchar2(200);
begin
    v_user := coalesce(
                sys_context('apex$session', 'app_user'), 
                sys_context('userenv', 'os_user'), 
                sys_context('userenv', 'session_user') );
    if inserting then
        if :new.id is null then
            :new.id := NEW_TABLE_seq.nextval; 
        end if;
       :new.created_on := sysdate;
       :new.created_by := v_user;
       :new.updated_on := sysdate;
       :new.updated_by := v_user;
    end if;
    if updating then
        :new.updated_on := sysdate;
        :new.updated_by := v_user;
    end if;
end; 
/

alter trigger NEW_TABLE_trg enable;