Anlegen von Tabellen mit Triggern und Logtabelle

Im folgenden Beispiel werden alle Änderungen (Insert, Update und Delete) in einer Logtabelle per Trigger protokolliert.

Die Beispiele zeigen:

• Verwendung von Sequenzen
• Anlegen von Tabellen
• Anlegen von Triggern
• automatisches Setzen von Datumsfeldern
• protokollieren der Userid und des Rechnernamens
• Protokollieren alter und neuer Tabelleninhalte

INHALT

-- drop sequence seq_un_konstante_log;
-- drop sequence seq_un_konstante;
-- drop TABLE un_konstante_LOG;
-- drop TABLE un_konstante;

Verwendung von Sequenzen

create sequence seq_un_konstante_log start with 1;
create sequence seq_un_konstante start with 1;

Anlegen von Tabellen

CREATE TABLE un_konstante_LOG (LOG_ID NUMBER,
       LOG_TYPE VARCHAR2 (255),
       LOG_DATE DATE,
       ID NUMBER (13),
       KONSTANTE NUMBER (13) NOT NULL,
       BEZEICHNUNG VARCHAR2 (100),
       GUELTIG_AB DATE,
       GUELTIG_BIS DATE,
       ANGELEGT_AM DATE,
       ANGELEGT_VON VARCHAR2 (40),
       GEAENDERT_AM DATE,
       GEAENDERT_VON VARCHAR2 (40));

CREATE TABLE un_konstante (ID NUMBER (13) NOT NULL,
       KONSTANTE NUMBER (13) NOT NULL,
       BEZEICHNUNG VARCHAR2 (100),
       GUELTIG_AB DATE,
       GUELTIG_BIS DATE,
       ANGELEGT_AM DATE,
       ANGELEGT_VON VARCHAR2 (40),
       GEAENDERT_AM DATE,
       GEAENDERT_VON VARCHAR2 (40));

Tabellen Kommentar anlegen

COMMENT ON TABLE un_konstante IS 'Allgemeine Unternehmenskonstanten.';

Anlegen von Triggern

CREATE OR REPLACE TRIGGER tbd_un_konstante before delete on un_konstante for each row
declare
       v_terminal varchar2(255);
       v_user varchar2(255);
       v_log_seq number;
begin
       select seq_un_konstante_log.nextval
       into v_log_seq
       from dual;

       select user into v_user from dual;
       select userenv('terminal') into v_terminal from dual;
       v_user:= v_user || '@' || v_terminal;

       INSERT INTO un_konstante_LOG values (v_log_seq,
              'DELETE:'||v_user,
              sysdate,
              :old.ID,
              :old.KONSTANTE,
              :old.BEZEICHNUNG,
              :old.GUELTIG_AB,
              :old.GUELTIG_BIS,
              :old.ANGELEGT_AM,
              :old.ANGELEGT_VON,
              :old.GEAENDERT_AM,
              :old.GEAENDERT_VON);
end;
/
CREATE OR REPLACE TRIGGER tbi_un_konstante before insert on un_konstante for each row
declare
       v_count number(13);
       v_terminal varchar2(255);
       v_log_seq number;
begin
       select seq_un_konstante_log.nextval into v_log_seq from dual;
       select seq_un_konstante.nextval into :new.id from dual;

       select sysdate into :new.angelegt_am from dual;
       select sysdate into :new.gueltig_ab from dual;
       select to_date('31.12.2099') into :new.gueltig_bis from dual;
       select user into :new.angelegt_von from dual;
       select userenv('terminal') into v_terminal from dual;
       :new.angelegt_von := :new.angelegt_von || '@' || v_terminal;

       INSERT INTO un_konstante_LOG values (v_log_seq,
              'INSERT',
              sysdate,
              :new.ID,
              :new.KONSTANTE,
              :new.BEZEICHNUNG,
              :new.GUELTIG_AB,
              :new.GUELTIG_BIS,
              :new.ANGELEGT_AM,
              :new.ANGELEGT_VON,
              :new.GEAENDERT_AM,
              :new.GEAENDERT_VON);

end;
/

CREATE OR REPLACE TRIGGER tbu_un_konstante before update on un_konstante for each row
declare
       v_terminal varchar2(255);
       v_log_seq number;
begin
       select seq_un_konstante_log.nextval into v_log_seq from dual;

       select sysdate into :new.geaendert_am from dual;
       select user into :new.geaendert_von from dual;
       select userenv('terminal') into v_terminal from dual;
       :new.geaendert_von := :new.geaendert_von || '@' || v_terminal;

       INSERT INTO un_konstante_LOG values (v_log_seq,
              'OLD',
              sysdate,
              :old.ID,
              :old.KONSTANTE,
              :old.BEZEICHNUNG,
              :old.GUELTIG_AB,
              :old.GUELTIG_BIS,
              :old.ANGELEGT_AM,
              :old.ANGELEGT_VON,
              :old.GEAENDERT_AM,
              :old.GEAENDERT_VON);

       INSERT INTO un_konstante_LOG values (v_log_seq,
              'NEW',
              sysdate,
              :new.ID,
              :new.KONSTANTE,
              :new.BEZEICHNUNG,
              :new.GUELTIG_AB,
              :new.GUELTIG_BIS,
              :new.ANGELEGT_AM,
              :new.ANGELEGT_VON,
              :new.GEAENDERT_AM,
              :new.GEAENDERT_VON);

end;
/