Re: 'INSERT' in Trigger

Hi Jones,

wenn du bei einem BEFORE ... FOR EACH ROW-Trigger die Tabelle,
für die der Trigger definiert ist, manipulieren willst, dann
geht das natürlich nicht: Stell dir vor, du machst ein UPDATE
mit einer WHERE-clause, die das Attribut beinhaltet, das du im
Trigger manipulierst. Das Ergebnis wäre dann gfs. davon
abhängig, in welcher Reihenfolge Oracle die Tupel beim Update
behandelt. So eine Situation nennt man "mutating table".

Zweitens: Wenn du z.B. in einem INSERT-Trigger einen INSERT
machen willst, geht das auch nicht, da dann der Trigger rekursiv
feuert. Typische Fehlermeldung: "Maximale Anzahl offener Cursor
überschritten"

Wenn, wie in deinem Fall, ein UPDATE ein INSERT nach sich ziehen
soll, dann geht das mit folgendem Trick:
Der BEFORE UPDATE ... FOR EACH ROW-Trigger sammelt in einer
PL/SQL-Table alle Daten auf, die anschließend mit einem
AFTER UPDATE ... -Trigger in die Relation eingetragen werden.
Um eine PL/SQL-Tabelle zu haben, muss man ein PACKAGE anlegen.

Beispiel: Bei jedem UPDATE: Wenn subid = 0, dann wird ein
zusätzlicher Datensatz angelegt, jedoch mit subid + 1.


DROP TABLE test;
CREATE TABLE test (
  id  INTEGER,
  subid INTEGER,
  data INTEGER
);


CREATE OR REPLACE PACKAGE test_data AS
  TYPE type_test IS TABLE OF test%ROWTYPE INDEX BY BINARY_INTEGER;

  table_test type_test;
  v_NumEntries BINARY_INTEGER := 0;
END test_data;
/


CREATE OR REPLACE TRIGGER test_bur
  BEFORE UPDATE ON test FOR EACH ROW
BEGIN
  IF :new.subid = 0 THEN
    -- wir merken uns im package: :new.id, :new.subid + 1, :new.data
    test_data.v_NumEntries := test_data.v_NumEntries + 1;
    test_data.table_test(test_data.v_NumEntries).id := :new.id;
    test_data.table_test(test_data.v_NumEntries).subid := :new.subid + 1;
    test_data.table_test(test_data.v_NumEntries).data := :new.data;
  END IF;
END;
/


CREATE OR REPLACE TRIGGER test_au
  AFTER UPDATE ON test
BEGIN
  -- jetzt iterieren wir über die tables und machen INSERT INTO test...
  FOR v_loop IN 1 .. test_data.v_NumEntries LOOP
    INSERT INTO test VALUES (
      test_data.table_test(v_loop).id,
      test_data.table_test(v_loop).subid,
      test_data.table_test(v_loop).data);
  END LOOP;
  -- loop counter wieder zurücksetzen
  test_data.v_NumEntries := 0;
END;
/


INSERT INTO test VALUES (1,2,3);
UPDATE test SET subid = 0;
SELECT * FROM test;


       ID     SUBID      DATA
--------- --------- ---------
        1         0         3
        1         1         3

ciao, Bernhard