Oracle PL/SQL – Trigger

Fach: Informationstechnologie – Datenbanksysteme
Schulstufe: 12. Schulstufe – HTL Informatik
Voraussetzungen: Prozeduren, Funktionen, Packages, Exceptions
Autor: HTL Pinkafeld – IF/IT

1. Was sind Trigger?

1.1 Definition

Ein Trigger ist ein gespeicherter PL/SQL-Block, der automatisch ausgeführt wird, wenn ein bestimmtes Ereignis in der Datenbank eintritt. Im Gegensatz zu Prozeduren wird ein Trigger nicht explizit aufgerufen.

Ereignis (INSERT / UPDATE / DELETE)
    ↓
Oracle erkennt: Trigger ist definiert
    ↓
Trigger-Code wird automatisch ausgeführt
    ↓
Normale Verarbeitung setzt fort (oder wird abgebrochen)

1.2 Trigger-Typen

1.3 Anwendungsfälle

• Auditing: Jede Änderung an kritischen Tabellen protokollieren
• Validierung: Geschäftsregeln durchsetzen (die nicht per Constraint abbildbar sind)
• Ableitung: Berechnete Felder automatisch befüllen (z.B. Jahresgehalt)
• Replikation: Änderungen in andere Tabellen übertragen
• Sicherheit: Bestimmte Operationen zu bestimmten Zeiten verhindern

2. DML-Trigger erstellen

2.1 Syntax

CREATE [OR REPLACE] TRIGGER triggername
    {BEFORE | AFTER | INSTEAD OF}
    {INSERT | UPDATE [OF spalte] | DELETE} [OR {INSERT | UPDATE | DELETE}] ...
    ON tabellenname
    [REFERENCING {NEW AS neu | OLD AS alt}]
    [FOR EACH ROW]
    [WHEN (bedingung)]
DECLARE
    -- Variablendeklarationen (optional)
BEGIN
    -- Trigger-Code
EXCEPTION
    -- Fehlerbehandlung (optional)
END [triggername];
/

2.2 Einfacher AFTER INSERT Trigger

-- Jedes neue Mitglied in einer Audit-Tabelle protokollieren:
CREATE OR REPLACE TRIGGER trg_emp_insert
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
    INSERT INTO emp_audit (aktion, empno, ename, sal, zeitpunkt, benutzer)
    VALUES ('INSERT', :NEW.empno, :NEW.ename, :NEW.sal, SYSTIMESTAMP, USER);
END trg_emp_insert;
/

-- Test:
INSERT INTO emp (empno, ename, job, deptno, sal, hiredate)
VALUES (9001, 'NEWUSER', 'CLERK', 10, 2000, SYSDATE);
COMMIT;

-- Audit-Eintrag prüfen:
SELECT * FROM emp_audit WHERE empno = 9001;

2.3 BEFORE INSERT – Wert setzen

-- Primärschlüssel automatisch aus Sequence befüllen:
CREATE OR REPLACE TRIGGER trg_emp_pk
BEFORE INSERT ON emp
FOR EACH ROW
WHEN (NEW.empno IS NULL)    -- Nur wenn kein Wert angegeben
BEGIN
    :NEW.empno := emp_seq.NEXTVAL;
END;
/

-- Test (empno wird automatisch gesetzt):
INSERT INTO emp (ename, job, deptno, sal, hiredate)
VALUES ('AUTOUSER', 'ANALYST', 20, 3500, SYSDATE);
COMMIT;

3. Zeilen- vs. Statement-Trigger

3.1 Statement-Trigger (Standard)

Ein Statement-Trigger wird einmal pro DML-Anweisung ausgeführt – unabhängig davon, wie viele Zeilen betroffen sind. Kein FOR EACH ROW:

CREATE OR REPLACE TRIGGER trg_dept_access
BEFORE INSERT OR UPDATE OR DELETE ON dept
BEGIN
    -- Außerhalb der Geschäftszeiten keine Änderungen erlauben:
    IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) NOT BETWEEN 8 AND 17 THEN
        RAISE_APPLICATION_ERROR(
            -20100,
            'Änderungen an DEPT nur zwischen 08:00 und 17:00 erlaubt!'
        );
    END IF;
    IF TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN
        RAISE_APPLICATION_ERROR(-20101, 'Keine Änderungen am Wochenende!');
    END IF;
END trg_dept_access;
/

3.2 Zeilenorientierter Trigger mit FOR EACH ROW

Ein Row-Level Trigger wird für jede betroffene Zeile einmal ausgeführt. Bei UPDATE emp SET sal = sal * 1.1 – 14 Mitarbeiter → 14 Trigger-Aufrufe:

CREATE OR REPLACE TRIGGER trg_sal_check
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
    -- Gehaltskürzung um mehr als 20% verhindern:
    IF :NEW.sal < :OLD.sal * 0.80 THEN
        RAISE_APPLICATION_ERROR(
            -20200,
            'Gehaltskürzung um mehr als 20% nicht erlaubt! (' ||
            :OLD.sal || ' → ' || :NEW.sal || ')'
        );
    END IF;
END trg_sal_check;
/

3.3 Ausführungsreihenfolge

DML-Statement (z.B. UPDATE auf 5 Zeilen)
    ↓
BEFORE Statement-Trigger  (1×)
    ↓
    Für jede betroffene Zeile:
    ├─ BEFORE Row-Trigger  (5×)
    ├─ Zeile wird geändert
    └─ AFTER Row-Trigger   (5×)
    ↓
AFTER Statement-Trigger   (1×)

4. :NEW und :OLD Pseudorecords

4.1 Verfügbarkeit

Wichtig: :NEW kann nur in BEFORE-Triggern geändert werden!

4.2 Beispiel: Vollständiges Audit

-- Audit-Tabelle erstellen:
CREATE TABLE emp_audit (
    audit_id   NUMBER GENERATED ALWAYS AS IDENTITY,
    zeitpunkt  TIMESTAMP DEFAULT SYSTIMESTAMP,
    benutzer   VARCHAR2(30) DEFAULT USER,
    aktion     VARCHAR2(10),
    empno      NUMBER,
    alt_ename  VARCHAR2(10),
    neu_ename  VARCHAR2(10),
    alt_sal    NUMBER,
    neu_sal    NUMBER,
    alt_deptno NUMBER,
    neu_deptno NUMBER
);

-- Audit-Trigger:
CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
    v_aktion VARCHAR2(10);
BEGIN
    IF    INSERTING THEN v_aktion := 'INSERT';
    ELSIF UPDATING  THEN v_aktion := 'UPDATE';
    ELSIF DELETING  THEN v_aktion := 'DELETE';
    END IF;

    INSERT INTO emp_audit
        (aktion, empno, alt_ename, neu_ename, alt_sal, neu_sal, alt_deptno, neu_deptno)
    VALUES
        (v_aktion,
         COALESCE(:OLD.empno, :NEW.empno),
         :OLD.ename,  :NEW.ename,
         :OLD.sal,    :NEW.sal,
         :OLD.deptno, :NEW.deptno);
END trg_emp_audit;
/

4.3 INSERTING, UPDATING, DELETING

Wenn ein Trigger für mehrere Ereignisse definiert ist, kann mit diesen Prädikaten unterschieden werden:

CREATE OR REPLACE TRIGGER trg_multi
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        :NEW.hiredate := NVL(:NEW.hiredate, SYSDATE);  -- Datum automatisch setzen
    ELSIF UPDATING('SAL') THEN
        -- Nur bei SAL-Updates aktiv
        DBMS_OUTPUT.PUT_LINE('Gehalt von ' || :OLD.sal || ' auf ' || :NEW.sal);
    ELSIF DELETING THEN
        IF :OLD.job = 'PRESIDENT' THEN
            RAISE_APPLICATION_ERROR(-20300, 'Präsident kann nicht gelöscht werden!');
        END IF;
    END IF;
END;
/

5. WHEN-Klausel in Triggern

5.1 Bedingter Trigger

Die WHEN-Klausel filtert Zeilen, für die der Trigger-Body ausgeführt wird. Innerhalb von WHEN werden :NEW und :OLD ohne Doppelpunkt geschrieben:

CREATE OR REPLACE TRIGGER trg_hochgehalt
AFTER INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
WHEN (NEW.sal > 5000)   -- Kein : vor NEW/OLD in WHEN!
BEGIN
    DBMS_OUTPUT.PUT_LINE(
        'Achtung: ' || :NEW.ename || ' hat ein Gehalt von ' || :NEW.sal
    );
END;
/

6. Compound Triggers

6.1 Motivation

Das Mutating Table Problem tritt auf, wenn ein Row-Trigger auf dieselbe Tabelle zugreift, die gerade geändert wird. Compound Triggers lösen dieses Problem:

CREATE OR REPLACE TRIGGER trg_emp_compound
FOR INSERT OR UPDATE ON emp
COMPOUND TRIGGER

    -- Package-ähnliche Sektion für Variablen:
    TYPE t_sal_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_salaries t_sal_tab;
    v_idx      PLS_INTEGER := 0;

    -- Zeitpunkt 1: Vor dem Statement (einmalig)
    BEFORE STATEMENT IS
    BEGIN
        v_idx := 0;
        v_salaries.DELETE;
    END BEFORE STATEMENT;

    -- Zeitpunkt 2: Vor jeder Zeile
    BEFORE EACH ROW IS
    BEGIN
        NULL;
    END BEFORE EACH ROW;

    -- Zeitpunkt 3: Nach jeder Zeile – Daten sammeln
    AFTER EACH ROW IS
    BEGIN
        v_idx := v_idx + 1;
        v_salaries(v_idx) := :NEW.sal;
    END AFTER EACH ROW;

    -- Zeitpunkt 4: Nach dem Statement – Auswertung
    AFTER STATEMENT IS
        v_gesamt NUMBER := 0;
    BEGIN
        FOR i IN 1..v_idx LOOP
            v_gesamt := v_gesamt + v_salaries(i);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('Gesamtgehalt geändert/eingefügt: ' || v_gesamt);
    END AFTER STATEMENT;

END trg_emp_compound;
/

7. INSTEAD OF Trigger auf Views

7.1 Motivation

Komplexe Views (mit JOINs, GROUP BY, DISTINCT) sind normalerweise nicht direkt änderbar. Ein INSTEAD OF-Trigger interceptiert DML-Operationen auf der View:

-- Komplexe View:
CREATE OR REPLACE VIEW vw_emp_dept AS
    SELECT e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
    FROM   emp e JOIN dept d ON e.deptno = d.deptno;

-- INSTEAD OF Trigger:
CREATE OR REPLACE TRIGGER trg_empdet_insert
INSTEAD OF INSERT ON vw_emp_dept
FOR EACH ROW
DECLARE
    v_cnt NUMBER;
BEGIN
    -- Prüfen ob Abteilung existiert:
    SELECT COUNT(*) INTO v_cnt FROM dept WHERE deptno = :NEW.deptno;
    IF v_cnt = 0 THEN
        RAISE_APPLICATION_ERROR(-20400, 'Abteilung ' || :NEW.deptno || ' existiert nicht.');
    END IF;

    -- Tatsächliche Einfügung in Basistabelle:
    INSERT INTO emp (empno, ename, sal, deptno, job, hiredate)
    VALUES (:NEW.empno, :NEW.ename, :NEW.sal, :NEW.deptno, 'CLERK', SYSDATE);
END;
/

-- INSERT auf die View funktioniert jetzt:
INSERT INTO vw_emp_dept (empno, ename, sal, deptno)
VALUES (9100, 'VIEWUSER', 2500, 20);
COMMIT;

8. DDL- und System-Trigger

8.1 DDL-Trigger

Reagiert auf DDL-Operationen (CREATE, ALTER, DROP, RENAME):

-- Tabellen-Drops protokollieren:
CREATE OR REPLACE TRIGGER trg_schema_protect
BEFORE DROP ON SCHEMA
BEGIN
    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
        INSERT INTO ddl_log (aktion, objekt, benutzer, zeitpunkt)
        VALUES (ORA_SYSEVENT, ORA_DICT_OBJ_NAME, ORA_LOGIN_USER, SYSTIMESTAMP);
        COMMIT;

        -- Produktionstabellen schützen:
        IF ORA_DICT_OBJ_NAME IN ('EMP', 'DEPT', 'SALGRADE') THEN
            RAISE_APPLICATION_ERROR(-20500,
                'Schutz: Tabelle ' || ORA_DICT_OBJ_NAME || ' darf nicht gelöscht werden!');
        END IF;
    END IF;
END;
/

8.2 System-Trigger (LOGON/LOGOFF)

-- Login-Protokoll:
CREATE OR REPLACE TRIGGER trg_logon_audit
AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO session_log (benutzer, zeitpunkt, ip_adresse, event)
    VALUES (
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        SYSTIMESTAMP,
        SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
        'LOGIN'
    );
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN NULL;  -- Fehler im Login-Trigger darf Session nicht blockieren!
END;
/

8.3 Ereignis-Funktionen in DDL/System-Triggern

9. Trigger verwalten

9.1 Data Dictionary

-- Alle eigenen Trigger anzeigen:
SELECT trigger_name, trigger_type, triggering_event,
       table_name, status
FROM   user_triggers
ORDER  BY table_name, trigger_name;

-- Trigger-Code anzeigen:
SELECT trigger_body
FROM   user_triggers
WHERE  trigger_name = 'TRG_EMP_AUDIT';

9.2 Trigger aktivieren/deaktivieren

-- Einzelnen Trigger deaktivieren (z.B. für Bulk-Load):
ALTER TRIGGER trg_emp_audit DISABLE;

-- Einzelnen Trigger aktivieren:
ALTER TRIGGER trg_emp_audit ENABLE;

-- Alle Trigger einer Tabelle deaktivieren:
ALTER TABLE emp DISABLE ALL TRIGGERS;

-- Alle Trigger einer Tabelle aktivieren:
ALTER TABLE emp ENABLE ALL TRIGGERS;

9.3 Trigger löschen

DROP TRIGGER trg_emp_audit;
DROP TRIGGER trg_sal_check;

9.4 Trigger kompilieren

ALTER TRIGGER trg_emp_audit COMPILE;

10. Zusammenfassung und Ausblick

10.1 Trigger-Typen und Zeitpunkte

10.2 Best Practices

• ✅ Trigger so klein wie möglich halten – Logik in Packages auslagern
• ✅ Keine langen Berechnungen oder Schleifen in Triggern
• ✅ Fehler in LOGON-Triggern nie nach oben propagieren (blockiert Login)
• ✅ WHEN-Klausel nutzen, um unnötige Trigger-Ausführungen zu vermeiden
• ✅ Für Bulk-Operationen Trigger temporär deaktivieren
• ✅ INSTEAD OF statt komplexer Logik in Basistabellen-Triggern

10.3 Kurs-Abschluss

Damit ist der Oracle PL/SQL Kurs abgeschlossen. Die behandelten Themen:

Weiterführend: Oracle Advanced PL/SQL – Collections, Object Types, Native Dynamic SQL (EXECUTE IMMEDIATE), Fine-Grained Auditing, Virtual Private Database