Oracle PL/SQL-Trigger-Tutorial: Statt Compound [Beispiel]

Was ist Trigger in PL/SQL?

LÖST AUS sind gespeicherte Programme, die von abgefeuert werden Oracle Engine automatisch, wenn DML-Anweisungen wie Einfügen, Aktualisieren, Löschen für die Tabelle ausgeführt werden oder bestimmte Ereignisse auftreten. Der Code, der im Falle eines Triggers ausgeführt werden soll, kann je nach Anforderung definiert werden. Sie können das Ereignis auswählen, bei dem der Auslöser ausgelöst werden soll, sowie den Zeitpunkt der Ausführung. Der Zweck des Triggers besteht darin, die Integrität der Informationen in der Datenbank aufrechtzuerhalten.

Vorteile von Triggern

Im Folgenden sind die Vorteile von Triggern aufgeführt.

• Einige abgeleitete Spaltenwerte werden automatisch generiert
• Durchsetzung der referenziellen Integrität
• Ereignisprotokollierung und Speicherung von Informationen zum Tabellenzugriff
• Auditing
• SyncChronische Replikation von Tabellen
• Auferlegen von Sicherheitsberechtigungen
• Ungültige Transaktionen verhindern

Arten von Auslösern in Oracle

Auslöser können anhand der folgenden Parameter klassifiziert werden.

• Klassifizierung basierend auf der zeitliche Koordinierung
• VOR dem Auslöser: Wird ausgelöst, bevor das angegebene Ereignis eingetreten ist.
• NACH Trigger: Wird ausgelöst, nachdem das angegebene Ereignis aufgetreten ist.
• STATT Trigger: Ein besonderer Typ. Zu den weiteren Themen erfahren Sie mehr. (nur für DML)
• Klassifizierung basierend auf der Grad des
• Auslöser auf STATEMENT-Ebene: Er wird einmal für die angegebene Ereignisanweisung ausgelöst.
• Auslöser auf Zeilenebene: Er wird für jeden Datensatz ausgelöst, der von dem angegebenen Ereignis betroffen war. (nur für DML)
• Klassifizierung basierend auf der Event
• 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 Auslöser ist also die Kombination der oben genannten Parameter.

So erstellen Sie einen Trigger

Nachfolgend finden Sie die Syntax zum Erstellen eines Triggers.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

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 legt den Zeitpunkt des Ereignisses fest.
• EINFÜGEN/AKTUALISIEREN/ANMELDUNG/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, in dem das DML-Ereignis im Fall von DML-Trigger auftreten kann.
• Der Befehl „FOR EACH ROW“ legt den Auslöser für die Zeilenebene fest.
• 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 mit denen der anderen identisch PL/SQL-Blöcke. Der Deklarationsteil und der Ausnahmebehandlungsteil sind optional.

:NEW- und :OLD-Klausel

Bei 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. Mit diesen Klauseln können wir auf die alten und neuen Werte im Triggerkörper verweisen.

• :NEW – Es enthält einen neuen Wert für die Spalten der Basistabelle/-ansicht während der Triggerausführung
• :OLD – Es 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. Die folgende Tabelle gibt an, welche Klausel für welche DML-Anweisung gültig ist (INSERT/UPDATE/DELETE).

	INSERT	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 Einfügefall	GÜLTIG	GÜLTIG

STATT Auslöser

„INSTEAD OF Trigger“ ist der spezielle Triggertyp. Er wird nur in DML-Triggern verwendet. Er wird verwendet, wenn ein DML-Ereignis in der komplexen Ansicht auftreten soll.

Betrachten Sie ein Beispiel, in dem eine Ansicht aus drei Basistabellen erstellt wird. Wenn über diese Ansicht ein DML-Ereignis ausgegeben wird, wird dieses ungültig, da die Daten aus drei verschiedenen Tabellen stammen. In diesem Fall wird also INSTEAD OF-Trigger verwendet. Der INSTEAD OF-Trigger wird verwendet, um die Basistabellen direkt zu ändern, anstatt die Ansicht für das gegebene Ereignis zu ändern.

Beispiel 1: In diesem Beispiel erstellen wir eine komplexe Ansicht aus zwei Basistabellen.

• Tabelle_1 ist emp-Tabelle und

• Table_2 ist eine Abteilungstabelle.

Dann werden wir sehen, wie der INSTEAD OF-Trigger verwendet wird, um die UPDATE-Anweisung für die Standortdetails in dieser komplexen Ansicht auszugeben. Wir werden auch sehen, wie :NEW und :OLD in Triggern nützlich sind.

• Schritt 1: Erstellen Sie die Tabellen „emp“ und „dept“ mit den entsprechenden Spalten
• Schritt 2: Füllen der Tabelle mit Beispielwerten
• Schritt 3: Ansicht für die oben erstellte Tabelle erstellen
• Schritt 4: Aktualisierung der Ansicht vor dem Statt-Trigger
• Schritt 5: Erstellung des Statt-Triggers
• Schritt 6: Aktualisierung der Ansicht nach dem Auslösen

Schritt 1) Erstellen der Tabellen „emp“ und „dept“ 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));
/

Code Erläuterung

• Code Zeile 1-7: Erstellung der Tabelle „emp“.
• Code Zeile 8-12: Erstellung der Tabelle „Abteilung“.

Ausgang

Tabelle erstellt

Schritt 2) Da wir nun die Tabelle erstellt haben, werden wir diese Tabelle mit Beispielwerten und der Erstellung von Ansichten für die oben genannten Tabellen füllen.

BEGIN
INSERT INTO DEPT VALUES(10,‘HR’,‘USA’);
INSERT INTO DEPT VALUES(20,'SALES','UK’);
INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); 
COMMIT;
END;
/

BEGIN
INSERT 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 Erläuterung

• Code Zeile 13-19: Daten werden in die Tabelle „dept“ eingefügt.
• Code Zeile 20-26: Einfügen von Daten in die Tabelle „emp“.

Ausgang

PL/SQL-Prozedur fertiggestellt

Schritt 3) Erstellen einer Ansicht für die oben erstellte Tabelle.

CREATE VIEW guru99_emp_view(
Employee_name:dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Code Erläuterung

• Code Zeile 27-32: Erstellung der Ansicht „guru99_emp_view“.

• Code Zeile 33: Abfrage von guru99_emp_view.

Ausgang

Ansicht erstellt

MITARBEITERNAME	DEPT_NAME	STANDORT
ZZZ	HR	USA
Yyy	Angebote	UK
XXX	FINANCIAL	JAPAN

Schritt 4) Aktualisierung der Ansicht vor statt Auslöser.

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

Code Erläuterung

• Code Zeile 34-38: Aktualisieren Sie den Standort von „XXX“ auf „FRANKREICH“. Die Ausnahme wurde ausgelöst, weil die DML-Anweisungen sind in der komplexen Ansicht nicht zulässig.

Ausgang

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 zu vermeiden, der beim Aktualisieren der Ansicht im vorherigen Schritt auftritt, verwenden wir in diesem Schritt „anstelle von Trigger“.

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Code Erläuterung

• Code Zeile 39: Erstellung eines 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“.
• Code Zeile 44: Die Update-Anweisung verwendet „:NEW“ und „:OLD“, um den Wert der Spalten vor und nach der Aktualisierung zu ermitteln.

Ausgang

Auslöser erstellt

Schritt 6) Aktualisierung der Ansicht nach dem Statt-Auslöser. Jetzt tritt der Fehler nicht mehr auf, da der „Statt-Auslöser“ den Aktualisierungsvorgang dieser komplexen Ansicht übernimmt. Und wenn der Code ausgeführt wurde, wird der Standort des Mitarbeiters XXX von „Japan“ auf „Frankreich“ aktualisiert.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; 
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Code Erläuterung:

• Code Zeile 49-53: Aktualisierung des Standorts von „XXX“ auf „FRANKREICH“. Dies ist erfolgreich, da der „INSTEAD OF“-Trigger die eigentliche Aktualisierungsanweisung in der Ansicht gestoppt und die Aktualisierung der Basistabelle durchgeführt hat.
• Code Zeile 55: Überprüfen des aktualisierten Datensatzes.

Ausgang:

PL/SQL-Prozedur erfolgreich abgeschlossen

MITARBEITERNAME	DEPT_NAME	STANDORT
ZZZ	HR	USA
Yyy	Angebote	UK
XXX	FINANCIAL	FRANKREICH

Zusammengesetzter Trigger

Der zusammengesetzte Trigger ist ein Trigger, der es Ihnen ermöglicht, Aktionen für jeden der vier Zeitpunkte im einzelnen Triggerkörper festzulegen. Die vier verschiedenen Timing-Punkte, die es unterstützt, sind wie folgt.

• VORHERIGE ERKLÄRUNG – Ebene
• VOR DER REIHE – Ebene
• NACH REIHE – Ebene
• NACH ERKLÄRUNG – Ebene

Es bietet die Möglichkeit, die Aktionen für unterschiedliche Zeitpunkte in demselben Auslöser zu kombinieren.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
ON <name of underlying object>
<Declarative part>‭	‬
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

Syntaxerklärung:

• Die obige Syntax zeigt die Erstellung des „COMPOUND“-Triggers.
• Der deklarative Abschnitt ist für alle Ausführungsblöcke im Triggerkörper gleich.
• Diese 4 Timing-Blö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_trig 
FOR INSERT 
ON emp
COMPOUND TRIGGER 
BEFORE EACH ROW IS 
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); 
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Code Erläuterung:

• Code Zeile 2-10: Erstellung eines zusammengesetzten Triggers. Es wird für die Zeitmessung BEFORE ROW-Ebene 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.
• Code Zeile 11-14: Den Datensatz in die Tabelle „emp“ einfügen.
• Code Linie 16: Überprüfung des eingefügten Datensatzes.

Ausgang:

Trigger erstellt

PL/SQL-Prozedur erfolgreich abgeschlossen.

EMP_NAME	EMP_NO	GEHALT	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

Trigger aktivieren und deaktivieren

Trigger können aktiviert oder deaktiviert werden. Um den Trigger zu aktivieren oder zu deaktivieren, muss eine ALTER-Anweisung (DDL) für den Trigger angegeben werden, die ihn deaktiviert oder aktiviert.

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

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Syntaxerklä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 etwas über PL/SQL-Trigger und ihre Vorteile gelernt. Wir haben auch die verschiedenen Klassifizierungen kennengelernt und INSTEAD OF-Trigger und COMPOUND-Trigger besprochen.