Was ist Trigger in PL / SQL?
TRIGGERS sind gespeicherte Programme, die von der Oracle-Engine automatisch ausgelöst werden, wenn DML-Anweisungen wie Einfügen, Aktualisieren, Löschen in der Tabelle ausgeführt werden oder einige Ereignisse auftreten. Der im Falle eines Triggers auszuführende Code kann gemäß der Anforderung definiert werden. Sie können das Ereignis auswählen, bei dem der Trigger ausgelöst werden soll, und den Zeitpunkt der Ausführung. Der Zweck des Triggers besteht darin, die Integrität der Informationen in der Datenbank aufrechtzuerhalten.
In diesem Tutorial lernen Sie:
- Vorteile von Triggern
- Arten von Triggern in Oracle
- So erstellen Sie einen Trigger
- : NEU und: ALTE Klausel
- STATT Auslöser
- Zusammengesetzter Auslöser
Vorteile von Triggern
Im Folgenden sind die Vorteile von Triggern aufgeführt.
- Einige abgeleitete Spaltenwerte werden automatisch generiert
- Durchsetzen der referenziellen Integrität
- Ereignisprotokollierung und Speichern von Informationen zum Tabellenzugriff
- Prüfung
- Synchrone Replikation von Tabellen
- Auferlegen von Sicherheitsberechtigungen
- Ungültige Transaktionen verhindern
Arten von Triggern in Oracle
Trigger können anhand der folgenden Parameter klassifiziert werden.
- Klassifizierung basierend auf dem Timing
- VOR dem Auslösen: Wird ausgelöst, bevor das angegebene Ereignis aufgetreten ist.
- AFTER Trigger: Wird ausgelöst, nachdem das angegebene Ereignis aufgetreten ist.
- STATT Trigger: Ein spezieller Typ. Sie erfahren mehr über die weiteren Themen. (nur für DML)
- Klassifizierung basierend auf dem Level
- STATEMENT Level Trigger: Wird für die angegebene Ereignisanweisung einmal ausgelöst.
- ROW Level Trigger: Wird für jeden Datensatz ausgelöst, der im angegebenen Ereignis betroffen war. (nur für DML)
- Klassifizierung basierend auf dem Ereignis
- DML-Trigger: Wird ausgelöst, wenn das DML-Ereignis angegeben wird (INSERT / UPDATE / DELETE).
- DDL-Trigger: Wird ausgelöst, wenn das DDL-Ereignis angegeben wird (CREATE / ALTER).
- DATABASE-Trigger: Wird ausgelöst, wenn das Datenbankereignis angegeben wird (LOGON / LOGOFF / STARTUP / SHUTDOWN).
Jeder Trigger ist also die Kombination der oben genannten Parameter.
So erstellen Sie einen Trigger
Unten finden Sie die Syntax zum Erstellen eines Triggers.
CREATE [ OR REPLACE ] TRIGGER[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON [FOR EACH ROW][WHEN ]DECLARE BEGIN EXCEPTION END;
Syntax Erläuterung:
- Die obige Syntax zeigt die verschiedenen optionalen Anweisungen, die bei der Triggererstellung vorhanden sind.
- BEFORE / AFTER legt die Ereigniszeiten fest.
- INSERT / UPDATE / LOGON / CREATE / etc. gibt das Ereignis an, für das der Trigger ausgelöst werden muss.
- Die ON-Klausel gibt an, für welches Objekt das oben genannte Ereignis gültig ist. Dies ist beispielsweise der Tabellenname, bei dem das DML-Ereignis im Fall von DML-Trigger auftreten kann.
- Der Befehl "FÜR JEDE REIHE" gibt den Trigger für den ROW-Pegel an.
- Die WHEN-Klausel gibt die zusätzliche Bedingung an, unter der der Trigger ausgelöst werden muss.
- Der Deklarationsteil, der Ausführungsteil und der Ausnahmebehandlungsteil sind dieselben wie die anderen PL / SQL-Blöcke. Deklarationsteil und Ausnahmebehandlungsteil sind optional.
: NEU und: ALTE Klausel
In einem Trigger auf Zeilenebene wird der Trigger für jede zugehörige Zeile ausgelöst. Und manchmal ist es erforderlich, den Wert vor und nach der DML-Anweisung zu kennen.
Oracle hat im Trigger auf RECORD-Ebene zwei Klauseln bereitgestellt, um diese Werte zu speichern. Wir können diese Klauseln verwenden, um auf die alten und neuen Werte innerhalb des Triggerkörpers zu verweisen.
- : NEW - Enthält einen neuen Wert für die Spalten der Basistabelle / -ansicht während der Triggerausführung
- : OLD - Enthält den alten Wert der Spalten der Basistabelle / -ansicht während der Triggerausführung
Diese Klausel sollte basierend auf dem DML-Ereignis verwendet werden. In der folgenden Tabelle wird angegeben, welche Klausel für welche DML-Anweisung gültig ist (INSERT / UPDATE / DELETE).
| EINFÜGEN | AKTUALISIEREN | LÖSCHEN | |
| :NEU | GÜLTIG | GÜLTIG | UNGÜLTIG. Im Löschfall gibt es keinen neuen Wert. |
| :ALT | UNGÜLTIG. Es gibt keinen alten Wert im Insert-Fall | GÜLTIG | GÜLTIG |
STATT Auslöser
"STATT Trigger" ist die spezielle Art des Triggers. Es wird nur in DML-Triggern verwendet. Es wird verwendet, wenn ein DML-Ereignis in der komplexen Ansicht auftreten soll.
Stellen Sie sich ein Beispiel vor, in dem eine Ansicht aus 3 Basistabellen erstellt wird. Wenn ein DML-Ereignis über diese Ansicht ausgegeben wird, wird dies ungültig, da die Daten aus 3 verschiedenen Tabellen stammen. In diesem Fall wird also der STATT-Trigger verwendet. Der Trigger INSTEAD OF wird verwendet, um die Basistabellen direkt zu ändern, anstatt die Ansicht für das angegebene Ereignis zu ändern.
Beispiel 1 : In diesem Beispiel erstellen wir eine komplexe Ansicht aus zwei Basistabellen.
- Tabelle_1 ist emp Tabelle und
- Tabelle_2 ist eine Abteilungstabelle.
Dann werden wir sehen, wie der INSTEAD OF-Trigger verwendet wird, um UPDATE die Standortdetailanweisung für diese komplexe Ansicht auszugeben. Wir werden auch sehen, wie nützlich das: NEW und: OLD bei Triggern ist.
- Schritt 1: Erstellen Sie die Tabellen 'emp' und 'dept' mit den entsprechenden Spalten
- Schritt 2: Füllen Sie die Tabelle mit Beispielwerten
- Schritt 3: Erstellen einer Ansicht für die oben erstellte Tabelle
- Schritt 4: Aktualisierung der Ansicht vor dem Auslöser anstelle des Auslösers
- Schritt 5: Erstellung des Auslösers anstelle des Auslösers
- Schritt 6: Aktualisierung der Ansicht nach statt Auslöser
Schritt 1) Erstellen Sie die Tabellen 'emp' und 'dept' mit den entsprechenden Spalten
CREATE TABLE emp(emp_no NUMBER,emp_name VARCHAR2(50),salary NUMBER,manager VARCHAR2(50),dept_no NUMBER);/CREATE TABLE dept(Dept_no NUMBER,Dept_name VARCHAR2(50),LOCATION VARCHAR2(50));/
Code Erklärung
- Codezeile 1-7 : Erstellung der Tabelle 'emp'.
- Codezeile 8-12 : Erstellung der Tabelle 'Abteilung'.
Ausgabe
Tabelle erstellt
Schritt 2) Nachdem wir die Tabelle erstellt haben, füllen wir diese Tabelle mit Beispielwerten und der Erstellung von Ansichten für die obigen Tabellen.
BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30);INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10);COMMIT;END;/
Code Erklärung
- Codezeile 13-19 : Einfügen von Daten in die 'dept'-Tabelle.
- Codezeile 20-26: Einfügen von Daten in die Tabelle 'emp'.
Ausgabe
PL / SQL-Prozedur abgeschlossen
Schritt 3) Erstellen einer Ansicht für die oben erstellte Tabelle.
CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE emp.dept_no=dept.dept_no;/
SELECT * FROM guru99_emp_view;
Code Erklärung
- Codezeile 27-32: Erstellung der Ansicht 'guru99_emp_view'.
- Codezeile 33: Abfrage von guru99_emp_view.
Ausgabe
Ansicht erstellt
| MITARBEITERNAME | DEPT_NAME | STANDORT |
| ZZZ | HR | USA |
| JJJ | DER UMSATZ | Vereinigtes Königreich |
| XXX | FINANZIELLE | JAPAN |
Schritt 4) Aktualisierung der Ansicht vor statt Auslöser.
BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;COMMIT;END;/
Code Erklärung
- Codezeile 34-38: Aktualisieren Sie die Position von "XXX" auf "FRANKREICH". Die Ausnahme wurde ausgelöst, da die DML-Anweisungen in der komplexen Ansicht nicht zulässig sind.
Ausgabe
ORA-01779: Eine Spalte, die einer nicht schlüsselerhaltenen Tabelle zugeordnet ist, kann nicht geändert werden
ORA-06512: in Zeile 2
Schritt 5) Um die Fehlerbehebung beim Aktualisieren der Ansicht im vorherigen Schritt zu vermeiden, verwenden wir in diesem Schritt "anstelle des Auslösers".
CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/
Code Erklärung
- Codezeile 39: Erstellung des INSTEAD OF-Triggers für das Ereignis 'UPDATE' in der Ansicht 'guru99_emp_view' auf ROW-Ebene. Es enthält die Update-Anweisung zum Aktualisieren des Speicherorts in der Basistabelle 'dept'.
- Codezeile 44: Update-Anweisung verwendet ': NEW' und ': OLD', um den Wert von Spalten vor und nach dem Update zu ermitteln.
Ausgabe
Auslöser erstellt
Schritt 6) Aktualisierung der Ansicht nach statt Auslöser. Jetzt tritt der Fehler nicht mehr auf, da "anstelle des Auslösers" den Aktualisierungsvorgang dieser komplexen Ansicht übernimmt. Und wenn der Code ausgeführt wurde, wird der Standort des Mitarbeiters XXX von "Japan" auf "Frankreich" aktualisiert.
BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;
Code Erläuterung:
- Codezeile 49-53: Aktualisierung der Position von "XXX" auf "FRANKREICH". Dies ist erfolgreich, da der Trigger 'INSTEAD OF' die tatsächlich angezeigte Aktualisierungsanweisung gestoppt und die Aktualisierung der Basistabelle durchgeführt hat.
- Codezeile 55: Überprüfen des aktualisierten Datensatzes.
Ausgabe:
PL / SQL-Prozedur erfolgreich abgeschlossen
| MITARBEITERNAME | DEPT_NAME | STANDORT |
| ZZZ | HR | USA |
| JJJ | DER UMSATZ | Vereinigtes Königreich |
| XXX | FINANZIELLE | FRANKREICH |
Zusammengesetzter Auslöser
Der zusammengesetzte Trigger ist ein Trigger, mit dem Sie Aktionen für jeden der vier Zeitpunkte im einzelnen Triggerkörper festlegen können. Die vier verschiedenen Zeitpunkte, die es unterstützt, sind wie folgt.
- VOR DER ERKLÄRUNG - Ebene
- VOR REIHE - Level
- AFTER ROW - Level
- NACH DER ERKLÄRUNG - Ebene
Es bietet die Möglichkeit, die Aktionen für unterschiedliche Zeitpunkte in demselben Trigger zu kombinieren.
CREATE [ OR REPLACE ] TRIGGERFOR[INSERT | UPDATE | DELET… .]ON BEFORE STATEMENT ISBEGIN ;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN ;END EACH ROW;AFTER EACH ROW ISBEGIN ;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN ;END AFTER STATEMENT;END;
Syntax Erläuterung:
- Die obige Syntax zeigt die Erstellung des Triggers 'COMPOUND'.
- Der deklarative Abschnitt ist für alle Ausführungsblöcke im Triggerkörper gleich.
- Diese 4 Zeitsteuerungsblöcke können in beliebiger Reihenfolge vorliegen. Es ist nicht zwingend erforderlich, alle diese 4 Zeitblöcke zu haben. Wir können einen COMPOUND-Trigger nur für die erforderlichen Timings erstellen.
Beispiel 1 : In diesem Beispiel erstellen wir einen Auslöser, um die Gehaltsspalte automatisch mit dem Standardwert 5000 zu füllen.
CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/
BEGININSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30);COMMIT;END;/
SELECT * FROM emp WHERE emp_no=1004;
Code Erläuterung:
- Codezeile 2-10 : Erstellung eines zusammengesetzten Triggers. Es wurde für das Timing VOR ROW-Level erstellt, um das Gehalt mit dem Standardwert 5000 zu füllen. Dadurch wird das Gehalt auf den Standardwert '5000' geändert, bevor der Datensatz in die Tabelle eingefügt wird.
- Codezeile 11-14 : Fügen Sie den Datensatz in die Tabelle 'emp' ein.
- Codezeile 16 : Überprüfen des eingefügten Datensatzes.
Ausgabe:
Trigger erstellt
PL / SQL-Prozedur erfolgreich abgeschlossen.
| EMP_NAME | EMP_NO | GEHALT | MANAGER | DEPT_NO |
| CCC | 1004 | 5000 | AAA | 30 |
Aktivieren und Deaktivieren von Triggern
Trigger können aktiviert oder deaktiviert werden. Um den Trigger zu aktivieren oder zu deaktivieren, muss für den Trigger, der ihn deaktiviert oder aktiviert, eine ALTER (DDL) -Anweisung angegeben werden.
Nachfolgend finden Sie die Syntax zum Aktivieren / Deaktivieren der Trigger.
ALTER TRIGGER[ENABLE|DISABLE];ALTER TABLE [ENABLE|DISABLE] ALL TRIGGERS;
Syntax Erläuterung:
- Die erste Syntax zeigt, wie der einzelne Trigger aktiviert / deaktiviert wird.
- Die zweite Anweisung zeigt, wie alle Trigger für eine bestimmte Tabelle aktiviert / deaktiviert werden.
Zusammenfassung
In diesem Kapitel haben wir uns mit PL / SQL-Triggern und ihren Vorteilen vertraut gemacht. Wir haben auch die verschiedenen Klassifikationen gelernt und STATT Trigger und COMPOUND Trigger besprochen.