Oracle PL / SQL Trigger Tutorial: Anstelle von Compound [Beispiel]

In diesem Tutorial erfahren Sie-

• Vorteile von Triggern
• Arten von Triggern in Oracle
• So erstellen Sie Trigger
• :NEUE und :ALTE Klausel
• ANSTELLE VON Trigger
• Zusammengesetzter Trigger

Vorteile von Triggern

Im Folgenden finden Sie die Vorteile von Triggern.

• Automatisches Generieren einiger abgeleiteter Spaltenwerte
• Erzwingen der referenziellen Integrität
• Ereignisprotokollierung und Speichern von Informationen zum Tabellenzugriff
• Auditing
• Synchrone Replikation von Tabellen
• Auferlegen von Sicherheitsberechtigungen
• Verhindern ungültiger Transaktionen

Arten von Triggern in Oracle

Trigger können anhand der folgenden Parameter klassifiziert werden.

• Klassifizierung basierend auf dem Timing
  • VOR DEM Trigger: Es wird ausgelöst, bevor das angegebene Ereignis aufgetreten ist.
  • AFTER Trigger: Es 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 der Ebene
  • ANWEISUNG level Trigger: Es wird einmal für die angegebene Ereignisanweisung ausgelöst.
  • Zeilenpegelauslöser: Er wird für jeden Datensatz ausgelöst, der vom angegebenen Ereignis betroffen ist. (nur für DML)
• Klassifizierung basierend auf dem Ereignis
  • DML-Trigger: Wird ausgelöst, wenn das DML-Ereignis angegeben wird (EINFÜGEN/AKTUALISIEREN/LÖSCHEN)
  • DDL-Trigger: Es wird ausgelöst, wenn das DDL-Ereignis angegeben wird (CREATE/ALTER)
  • DATABASE Trigger: Es wird ausgelöst, wenn das Datenbankereignis angegeben wird (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Jeder Trigger ist also die Kombination der obigen Parameter.

So erstellen Sie einen Trigger

Nachfolgend finden Sie die Syntax zum Erstellen eines Triggers.

CREATE TRIGGER <trigger_name> ON<name of underlying object> DECLARE<Declaration part>BEGIN<Execution part> EXCEPTION<Exception handling part> END;

Syntaxerklärung:

• Die obige Syntax zeigt die verschiedenen optionalen Anweisungen, die bei der Triggererstellung vorhanden sind.
• BEFORE/ AFTER gibt die Zeitpunkte der Ereignisse an.
• EINFÜGEN/AKTUALISIEREN/ANMELDEN/ERSTELLEN/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, für den das DML-Ereignis im Fall eines DML-Triggers auftreten kann.
• Befehl „FÜR JEDE Zeile“ wird die Zeile Level-Trigger angeben.
• WHEN-Klausel gibt die zusätzliche Bedingung an, in der der Trigger ausgelöst werden muss.
• Der Deklarationsteil, Ausführungsteil, Ausnahmebehandlungsteil ist derselbe wie der der anderen PL / SQL-Blöcke. Deklarationsteil und Ausnahmebehandlungsteil sind optional.

:NEW und :OLD 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 zwei Klauseln im Trigger auf Datensatzebene bereitgestellt, um diese Werte zu speichern. Wir können diese Klauseln verwenden, um auf die alten und neuen Werte im Triggerkörper 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 für die 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. In diesem Fall gibt es keinen neuen Wert.
:ALT	UNGÜLTIG. In diesem Fall gibt es keinen alten Wert	GÜLTIG	GÜLTIG

STATT Trigger

„STATT Trigger“ ist der spezielle Triggertyp. Es wird nur in DML-Triggern verwendet. Es wird verwendet, wenn ein DML-Ereignis in der komplexen Ansicht auftritt.

Betrachten Sie ein Beispiel, 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. Also wird in diesem ANSTELLE VON Trigger verwendet. Der INSTEAD OF-Trigger 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.

• Table_1 ist die EMP-Tabelle und

• Table_2 ist die Abteilungstabelle.

Dann werden wir sehen, wie der INSTEAD OF-Trigger verwendet wird, um die location Detail-Anweisung für diese komplexe Ansicht zu aktualisieren. Wir werden auch sehen, wie :NEW und :OLD in Triggern nützlich sind.

• Schritt 1: Erstellen der Tabelle ‚emp‘ und ‚dept‘ mit entsprechenden Spalten
• Schritt 2: Auffüllen der Tabelle mit Beispielwerten
• Schritt 3: Erstellen der Ansicht für die oben erstellte Tabelle
• Schritt 4: Aktualisieren der Ansicht vor dem instead-of-Trigger
• Schritt 5: Erstellen des Instead-of-Triggers
• Schritt 6: Aktualisieren der Ansicht nach dem instead-of-Trigger

Schritt 1) Erstellen der Tabelle ‚emp ‚ mit 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));/

Erklärung des Codes

• Codezeile 1-7: Tabelle ‚emp‘ Erstellung.
• Codezeile 8-12: Erstellung der Tabelle ‚dept‘.

Ausgabe

Tabelle erstellt

Schritt 2) Nachdem wir die Tabelle erstellt haben, füllen wir diese Tabelle mit Beispielwerten und erstellen 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;/

Erklärung des Codes

• Codezeile 13-19: Einfügen von Daten in die Tabelle „dept“.
• 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;

Erklärung des Codes

• Codezeile 27-32: guru99_emp_view‘ Ansicht.

• Codezeile 33: guru99_emp_view abfragen.

Output

View created

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
YYY	SALES	UK
XXX	FINANCIAL	JAPAN

Step 4) Update of view before instead-of trigger.

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;COMMIT;END;/

Code Explanation

• Code line 34-38: Update the location of „XXX“ to ‚FRANCE‘. 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 den Fehler beim Aktualisieren der Ansicht im vorherigen Schritt zu vermeiden, verwenden wir in diesem Schritt „anstelle von“.“

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/

Erklärung des Codes

• Codezeile 39: Erstellung eines ANSTELLE EINES Triggers für das Ereignis ‚UPDATE‘ in der Ansicht ‚guru99_emp_view‘ auf Zeilenebene. Es enthält die update-Anweisung, um den Speicherort in der Basistabelle ‚dept‘ zu aktualisieren.
• Codezeile 44: Update-Anweisung verwendet ‚:NEW‘ und ‚: OLD‘, um den Wert von Spalten vor und nach dem Update zu ermitteln.

Ausgabe

Trigger erstellt

Schritt 6) Aktualisieren der Ansicht nach Anstelle des Triggers. Jetzt wird der Fehler nicht auftreten, da „anstelle des Triggers“ den Aktualisierungsvorgang dieser komplexen Ansicht verarbeitet. 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;

Erklärung des Codes:

• Codezeile 49-53: Aktualisierung der Position von „XXX“ nach ‚FRANKREICH‘. Dies ist erfolgreich, da der Auslöser ‚INSTEAD OF‘ die eigentliche Update-Anweisung in der Ansicht gestoppt und die Aktualisierung der Basistabelle durchgeführt hat.
• Codezeile 55: Überprüfung des aktualisierten Datensatzes.

Ausgabe: PL/SQL-Prozedur erfolgreich abgeschlossen

EMPLOYEE_NAME	DEPT_NAME	STANDORT
ZZZ	HR	USA
YYY	VERTRIEB	UK
XXX	FINANZEN	FRANKREICH

Zusammengesetzter Trigger

Der zusammengesetzte Trigger ist ein Trigger, mit dem Sie Aktionen für jeden der vier Zeitpunkte im einzelnen Triggerkörper angeben können. Die vier verschiedenen timing punkt es unterstützt ist als unten.

• VOR DER Anweisungsebene
• VOR DER Zeilenebene
• NACH DER Zeilenebene
• NACH DER Anweisungsebene

Es bietet die Möglichkeit, die Aktionen für verschiedene Zeitpunkte in demselben Trigger zu kombinieren.

CREATE TRIGGER <trigger_name> FORON <name of underlying object><Declarative part>‭ ‬BEFORE STATEMENT ISBEGIN<Execution part>;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN<Execution part>;END EACH ROW;AFTER EACH ROW ISBEGIN<Execution part>;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN<Execution part>;END AFTER STATEMENT;END;

Syntax-Erklärung:

• Die obige Syntax zeigt die Erstellung von ‚ZUSAMMENGESETZTEN‘ Triggern.
• Deklarative Abschnitt ist für alle Ausführungsblock im Triggerkörper gemeinsam.
• Diese 4 Zeitblöcke können in beliebiger Reihenfolge sein. Es ist nicht zwingend erforderlich, alle diese 4 Zeitblöcke zu haben. Wir können einen ZUSAMMENGESETZTEN Trigger nur für die erforderlichen Timings erstellen.

Beispiel 1: In diesem Beispiel erstellen wir einen Trigger, um die Gehaltsspalte automatisch mit dem Standardwert 5000 zu füllen.

CREATE TRIGGER emp_trig FOR INSERT ON empCOMPOUND TRIGGER BEFORE EACH ROW IS BEGIN: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;

Erklärung des Codes:

• Codezeile 2-10: Erstellung eines zusammengesetzten Triggers. Es wird für das Timing VOR der Zeilenebene 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	ABTEILUNGSNUMMER
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-Anweisung (DDL) angegeben werden.

Nachfolgend finden Sie die Syntax zum Aktivieren /Deaktivieren der Trigger.

ALTER TRIGGER <trigger_name> ;ALTER TABLE <table_name> ALL TRIGGERS;

Syntax-Erklärung:

• 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 PL/SQL-Trigger und ihre Vorteile kennengelernt. Wir haben auch die verschiedenen Klassifikationen gelernt und ANSTELLE VON Trigger und COMPOUND Trigger diskutiert.