Trigger: for each row

[harunbicakli]

Hi, was ist der Unterschied zwischen einem Trigger, bei dem "for each row" dabei steht und einem, bei dems nicht dabei steht.

thx^^

[Martin R.]

Hallo,

ohne sind Statement Trigger einmal pro DML( auch wenn keine Zeilen veraendert werden)
Row Trigger wird einmal je geaenderter Zeile ausgelöst(werden nicht ausgeführt, wenn keine Zeile betroffen ist)

[Falk Prüfer]

... und vlt. sollte man auch den umgekehrten Fall erwähnen. Statement Trigger nur einmal, auch wenn 100 Zeilen betroffen sind, während ein for each row-Trigger eben 100mal bei 100 betroffenen Zeilen aufgerufen wird.

Außerdem sind in for each row-Triggern die Pseudospalten :new und :old, zum Zugriff auf die Neuen / Alten Werte der gerade geänderten Zeile verfügbar.

Gruß Falk

[harunbicakli]

Danke vielmals... sehr gut und deutlich erklärt ^^

[MilloZ]

Hallöle ich möchte das Thema Trigger nochmal aufgreifen sofern es noch jemanden gibt der das hier liest ^^

Und zwar wollte ich den Aufrufezeitpunkt eines Triggers abklären (before or after)

ist es richtig, dass der zeitpunkt des aufrufs after immer eine konsequenz nach sich zieht? zum beispiel, wenn ein prüfling ein fach drei mal nicht bestanden, dann wird der prüfling exmatrikuliert.
und ein beispiel für before: eine*Prüfung*darf*höchstens*3*mal*abgelegt*werden

quasi hätte zweiterer satz keine konsequenz, sodass hier nur ein before in frage kommen könnte, stimmt das so bzw. hab ich das so richtig begriffen?

[Falk Prüfer]

Hallo,

eine "Konsequenz" (man könnte auch sagen Exception ) kann ein Trigger immer auslösen (und damit das Statement als solches scheitern lassen). Before und After haben lediglich Einfluss auf den Zeitpunkt des Triggeraufrufs und damit auf die zur Verfügung stehenden Möglichkeiten.

BEFORE: Der Trigger feuert bevor (welch ein Zufall) die Daten tatsächlich geändert werden. Über die Pseudospalte :NEW besteht die Möglichkeit die zu ändernden Werte noch zu manipulieren. Zu diesem Zeitpunkt wurden auch noch keine Constraints geprüft.

AFTER: Der Trigger feuert (wie sollte es anders sein) nachdem die Daten geändert und alle Constraints geprüft wurden. Es besteht keine Möglichkeit mehr, die aktuellen Daten zu manipulieren. Auf :NEW kann nur noch lesend zugegriffen werden.

Ergo wird ein BEFORE-Trigger immer ausgeführt, während ein AFTER-Trigger nur ausgeführt wird, wenn das Statement selber erfolgreich war und nicht bereits wegen z.B. einer Constraintverletzung gescheitert ist.

Daraus ergibt sich als Anwendung für einen BEFORE-Trigger: z.B. setzen von Standardwerten, aktueller Zeitstempel, Logikprüfungen (die nicht durch Constraints abgebildet werden können) und für den AFTER-Trigger: z.B. Protokollierung von Änderungen, Aktualisierung abhängiger Werte, etc.

Gruß Falk

[MilloZ]

Falk du bist in den letzten Tagen mein persönlicher Held geworden Vielen, vielen Dank für diese tolle Erklärung!

[MilloZ]

Eine Frage hätt ich noch zum Trigger und zwar haben ich und mein Kollege sowas gebaut:

BEGIN 
  SELECT count(*) INTO nochnichtbestanden
  
    FROM pruefung WHERE pruefung.matrikelNr = NEW.matrikelNr 
                    AND pruefung.vnr = NEW.vnr
                    AND note > 4; -- Prüfling hat das Fach nicht bestanden 
                    
   SELECT count(*) INTO abgeleget 
    FROM pruefung WHERE pruefung.matrikelNr = NEW.matrikelNr 
                    AND pruefung.vnr = NEW.vnr 
                    AND versuche < 2; -- Anzahl der Versuche 
                    
  IF nochnichtbestanden == false  && abgelegt == false 
    RAISE EXCEPTION 'Prüfung darf nicht eingetragen, da Prüfung schon bestanden oder mehr als 2 Versuche !'; 
  END IF; 
  RETURN NEW; 

create TRIGGER Pruefungsvoraussetzung 
  BEFORE insert or update  
  ON pruefung 
  for each row  
  execute procedure checkPruefung();

Die Frage liegt nun eher darin, ob in der Funktion des Triggers ganz normal "gearbeitet" werden kann ? Also wenn bspw. eine Einschränkung des Datums angegeben ist, kann dann in der Funktion eine where Einschränkung gemacht werden? bspw. where Datum = '1.1.2011'

[dimitri]

Hi,

in diesem Trigger wurde so ziemlich alles falsch gemacht was man machen kann.
1. Ihr verwendet einen Trigger, um eine fachliche Anforderung zu implementieren. So wie ihr es macht wäre es nicht multiuserfähig.
2. Ihr selektiert innerhalb eines Row Level Triggers auf seine Tabelle. Das erlaubt Oracle nicht (Ausnahme ist ein INSERT INTO ... VALUES bei dem genau eine Zeile eingefügt wird), da ihr damit in die Transaktion hineinschauen würdet während sie noch am laufen ist. Mehr dazu findet ihr hier.
3. Ihr selektiert in eine (nicht deklarierte) nummerische Variable und fragt dann auf TRUE/FALSE ab. Das geht nicht, ihr müsst auf den nummerischen Wert abfragen.
4. Den Operator == gibt es in C/C++ und Java. In PL/SQL gibt es := (Zuweisung) und = (Vergleich)
5. Eine Procedur hat keinen Returnwert
6. Eine Prozedur wird einfach durch Nennung des Namens aufgerufen. Execute Prozedure gibt es nicht
7. In einer Prozedur hat man keinen Zugriff auf die new und old Variablen. Ihr müsst die Parameter direkt übergeben. Also meineProc(:new.matrikelNr);

Die Frage liegt nun eher darin, ob in der Funktion des Triggers ganz normal "gearbeitet" werden kann ? Also wenn bspw. eine Einschränkung des Datums angegeben ist, kann dann in der Funktion eine where Einschränkung gemacht werden? bspw. where Datum = '1.1.2011'

Ja sicher.

Dim

[MilloZ]

ohje da gibt es wohl noch einiges aufzuarbeiten wie ich sehe, aber vielen dank für die antwort, dies hilft schon ein gutes stück weiter!

[MilloZ]

Hallöchen ich bins nomma ^^ da es zu Triggern ja noch nachohlbedarf gab wollte ich nachfragen, ob eine Syntax dieser Form so erlaubt wäre?

 SELECT count(*) INTO schonBestanden 
    FROM pruefung WHERE pruefung.matrikelNr = NEW.matrikelNr 
                    AND pruefung.vnr = NEW.vnr 
                    AND note <= 4;

if schonbestanden >0
Raise excetion 'Prüfung wurde schon abgelegt und darf nicht wiederholt werden'

Es geht nur um den Block innerhalb des Triggers ohne Begin etc. Wenn wir davon ausgehen, dass "schonBestanden" ein Variable vom Typ integer ist und wir prüfen wollen wenn eine vorhandene matrikelnummer = der neu eingetragenen Matrikelnummer ist und die Veranstaltungsnummer = der neuen Veranstaltungsnummer ist, sowie die Note kleiner oder gleich 4 ist, wenn das zutrifft soll eine Execption ausgeworfen werden, die einen Eintrag der Prüfung in die Tabelle Prüfung nicht erlaubt, da ja schon eine Prüfung bereits eine Prüfung vorliegt. Sofern beide Kriterien nicht zutreffen darf die Prüfung eingetragen werden. Der andere Block definiert die Anzahl der Versuche. Ich denke dazu gibt es nichts großartiges zu erklären ^^

Das nur so zur Erklärung von dem was ich jetzt aus dem Trigger herauslese.

Die Frage besteht lediglich darin ob eine Darstellung des angegebenen SQL Blocks so korrekt aussehen würde? Sowie die dazugehörige IF Abfrage? (ausgehend davon, dass "schonbestanden" eine integer variable ist (ohne jetzt auf Sinn oder Verbesserungsvorschläge einzugehen ^^)

PS: Vll. noch eine Frage zur Syntax? Irgendwo habe ich gelesen dass man auch mit der Darstellung :=NEW sowie :=OLD arbeitet? Kann mir vielleicht einer auf "SQL-Deutsch" erklären was es damit auf sich hat? Oder müssten die oben aufgeführten SQL Blöcke dann mit :=NEW.Matrikelnr aussehen ? Wäre super nett wenn mir jemand bei meinem Problemchen helfen könnte !

[dimitri]

Die Frage besteht lediglich darin ob eine Darstellung des angegebenen SQL Blocks so korrekt aussehen würde?

Nicht ganz. Vor NEW und OLD muss jeweils ein : da es sich um Bindvariablen handelt.

Irgendwo habe ich gelesen dass man auch mit der Darstellung :=NEW sowie :=OLD arbeitet?

Wie gesagt nur : ohne =. Ansonsten wie gehabt. Funtkionieren wird der Trigger aber trotzdem nur bei einem INSERT INTO ... VALUES(...)
Bei einem UPDATE oder einem INSERT INTO ... SELECT bekommt ihr einen ORA-04091
Multiuserfähig ist er auch nicht - also im Prinzip zwar syntaktisch korrekt, aber eure Anforderungen können damit nicht erfüllt werden.

Dim

[MilloZ]

Daaaaaaaaaaaaanke