Oracle PL/SQL-Trigger: Anstelle von & zusammengesetzten Typen

Was ist Trigger in PL/SQL?

Trigger werden gespeichert PL / SQL Programme, die von der Oracle Motor automatisch wenn DML-Anweisungen Trigger werden, wie beispielsweise Einfüge-, Aktualisierungs- und Löschvorgänge, auf einer Tabelle ausgeführt oder wenn bestimmte Ereignisse eintreten. Der im Falle eines Triggers auszuführende Code kann bedarfsgerecht definiert werden. Sie können das Ereignis, bei dem der Trigger ausgelöst werden soll, und den Ausführungszeitpunkt festlegen. Der Zweck eines Triggers besteht darin, die Datenintegrität in der Datenbank zu gewährleisten.

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 dem Zeitpunkt

• VOR dem Auslöser: Es wird ausgelöst, bevor das angegebene Ereignis eingetreten ist.
• NACH Auslöser: Es wird ausgelöst, nachdem das angegebene Ereignis eingetreten ist.
• STATT Trigger: Ein spezieller Typ. Mehr dazu erfahren Sie in den folgenden Abschnitten. (Nur für DML)

Klassifizierung basierend auf dem Niveau

• Trigger auf Anweisungsebene: Es wird einmalig für die angegebene Ereignisanweisung ausgelöst.
• Auslöser auf Zeilenebene: Es wird für jeden Datensatz ausgelöst, der von dem angegebenen Ereignis betroffen ist. (Nur für DML)

Klassifizierung basierend auf dem Ereignis

• DML-Trigger: Es wird ausgelöst, wenn das DML-Ereignis (INSERT/UPDATE/DELETE) angegeben wird.
• DDL-Trigger: Es wird ausgelöst, wenn das DDL-Ereignis (CREATE/ALTER) angegeben wird.
• Datenbank-Trigger: Es wird ausgelöst, wenn das Datenbankereignis angegeben wird (LOGON/LOGOFF/STARTUP/SHUTDOWN).

Jeder Trigger ist also eine Kombination der oben genannten Parameter.

So erstellen Sie einen Trigger

Nachfolgend finden Sie die Syntax zum Erstellen eines Triggers. Der Screenshot unten zeigt diese Trigger-Erstellungssyntax. Oracle.

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.
• VORHER/NACHHER legt die Zeitpunkte der Ereignisse fest.
• EINFÜGEN/AKTUALISIEREN/ANMELDUNG/ERSTELLEN/etc. gibt das Ereignis an, für das der Trigger ausgelöst werden muss.
• Die ON-Klausel legt das Objekt fest, für das das oben genannte Ereignis gilt. Dies ist beispielsweise der Tabellenname, für den das DML-Ereignis im Falle eines DML-Triggers auftreten kann.
• Der Befehl „FOR EACH ROW“ legt den Trigger auf Zeilenebene fest.
• Die WHEN-Klausel legt die zusätzliche Bedingung fest, unter der der Trigger ausgelöst werden muss.
• Der Deklarationsteil, der Ausführungsteil und der Ausnahmebehandlungsteil sind die gleichen wie bei den anderen PL/SQL-BlöckeDer Deklarationsteil und der Ausnahmebehandlung Teile davon 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 Der Zeilenebenentrigger enthält zwei Klauseln, die diese Werte speichern. Mithilfe dieser Klauseln können wir innerhalb des Trigger-Bodys auf die alten und neuen Werte zugreifen.

• :NEU – Es enthält während der Triggerausführung einen neuen Wert für die Spalten der Basistabelle/-ansicht.
• :ALT – Es speichert den alten Wert der Spalten der Basistabelle/-ansicht während der Triggerausführung.

Diese Klausel sollte abhängig vom DML-Ereignis verwendet werden. Die folgende Tabelle gibt an, welche Klausel für welche DML-Anweisung (INSERT/UPDATE/DELETE) gültig ist.

STATT Auslöser

Ein „INSTEAD OF“-Trigger ist ein spezieller Triggertyp. Er wird ausschließlich in DML-Triggern verwendet. Er kommt zum Einsatz, wenn ein DML-Ereignis in einer komplexen Ansicht ausgelöst werden soll.

Betrachten wir ein Beispiel, in dem eine Ansicht aus drei Basistabellen erstellt wird. Jede DML-Operation, die auf dieser Ansicht ausgeführt wird, ist ungültig, da die Daten aus drei verschiedenen Tabellen stammen. In diesem Fall wird ein INSTEAD OF-Trigger verwendet. Dieser Trigger ändert die Basistabellen direkt, anstatt die Ansicht für das jeweilige Ereignis zu modifizieren.

Beispiel 1: In diesem Beispiel erstellen wir eine komplexe Ansicht aus zwei Basistabellen, wobei Tabelle_1 die Mitarbeitertabelle und Tabelle_2 die Abteilungstabelle ist.

Anschließend sehen wir uns an, wie der INSTEAD OF-Trigger verwendet wird, um die Standortdetails in dieser komplexen Ansicht zu aktualisieren. Wir werden auch sehen, wie :NEW und :OLD in Triggern nützlich sind. Das Beispiel wird in den folgenden Schritten durchgeführt:

• Schritt 1: Erstellen der Tabellen 'emp' und 'dept' mit den entsprechenden Spalten
• Schritt 2: Die Tabellen mit Beispielwerten füllen
• Schritt 3: Erstellen einer Ansicht für die oben erstellten Tabellen
• Schritt 4: Aktualisierung der Ansicht vor dem INSTEAD OF-Trigger
• Schritt 5: Erstellung des INSTEAD OF-Triggers
• Schritt 6: Aktualisierung der Ansicht nach dem INSTEAD OF-Trigger

Schritt 1) ​​Erstellen der Tabellen 'emp' und 'dept' mit den entsprechenden Spalten.

Der folgende Screenshot zeigt die Erstellung der Basistabellen „emp“ und „dept“ in Oracle.

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:

Table Created

Schritt 2) Nachdem wir nun die Tabellen erstellt haben, werden wir sie mit Beispielwerten füllen.

Der untenstehende Screenshot zeigt, wie die Beispielzeilen in die Tabellen 'dept' und 'emp' eingefügt werden.

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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Erläuterung

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

Ausgang:

PL/SQL procedure completed

Schritt 3) Es wird eine Ansicht für die oben erstellten Tabellen erstellt.

Der folgende Screenshot zeigt, wie die komplexe Ansicht erstellt und anschließend abgefragt wird.

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:

View created

Schritt 4) Aktualisierung der Ansicht vor dem INSTEAD OF-Trigger.

Der untenstehende Screenshot zeigt den Aktualisierungsversuch der komplexen Ansicht und den daraus resultierenden Fehler.

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“. Es wurde eine Ausnahme ausgelöst, da DML-Anweisungen nicht direkt auf der komplexen Sicht zulässig sind.

Ausgang:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

Schritt 5) Um den Fehler zu vermeiden, der beim Aktualisieren der Ansicht im vorherigen Schritt aufgetreten ist, verwenden wir in diesem Schritt einen „INSTEAD OF“-Trigger.

Der folgende Screenshot zeigt die Erstellung des INSTEAD OF-Triggers.

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 des INSTEAD OF-Triggers für das Ereignis „UPDATE“ in der Ansicht „guru99_emp_view“ auf Zeilenebene. Er enthält die Aktualisierungsanweisung zum Aktualisieren des Standorts in der Basistabelle „dept“.
• Code Zeile 44: Die Aktualisierungsanweisung verwendet ':NEW' und ':OLD', um den Wert der Spalten vor und nach der Aktualisierung zu ermitteln.

Ausgang:

Trigger Created

Schritt 6) Die Ansicht wird nach dem INSTEAD OF-Trigger aktualisiert. Der Fehler tritt nun nicht mehr auf, da der INSTEAD OF-Trigger die Aktualisierung dieser komplexen Ansicht übernimmt. Bei der Codeausführung wird der Standort von Mitarbeiter XXX von „Japan“ auf „Frankreich“ aktualisiert.

Der untenstehende Screenshot zeigt das erfolgreiche Update über den INSTEAD OF-Trigger und die aktualisierte Ansicht.

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: Die Position von „XXX“ wurde auf „FRANKREICH“ aktualisiert. Dies war erfolgreich, da der Trigger „INSTEAD OF“ die eigentliche Aktualisierungsanweisung für die Ansicht gestoppt und stattdessen die Aktualisierung der Basistabelle durchgeführt hat.
• Code Zeile 55: Überprüfen des aktualisierten Datensatzes.

Ausgang:

PL/SQL procedure successfully completed

Zusammengesetzter Trigger

Der zusammengesetzte Trigger ermöglicht es Ihnen, Aktionen für jeden der vier Zeitpunkte in einem einzigen Trigger-Body festzulegen. Die vier unterstützten Zeitpunkte sind unten aufgeführt.

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

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

Der folgende Screenshot zeigt die Syntax des zusammengesetzten Triggers mit seinen vier Zeitabschnitten.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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 eines 'COMPOUND'-Triggers.
• Der Deklarationsteil ist für alle Ausführungsblöcke im Trigger-Rumpf gemeinsam.
• Diese vier Timing-Blöcke können in beliebiger Reihenfolge angeordnet sein. Es ist nicht zwingend erforderlich, alle vier Timing-Blöcke zu verwenden. Wir können einen zusammengesetzten Trigger nur für die benötigten Timings erstellen.

Beispiel 1: In diesem Beispiel erstellen wir einen Trigger, der die Spalte „Gehalt“ automatisch mit dem Standardwert 5000 befüllt.

Der folgende Screenshot zeigt das Beispiel des zusammengesetzten Triggers und dessen Ausgabe.

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 des zusammengesetzten Triggers. Dieser Trigger wird auf der Ebene „Vor Zeile“ ausgeführt, um das Gehalt mit dem Standardwert 5000 zu befüllen. Dadurch wird das Gehalt vor dem Einfügen des Datensatzes in die Tabelle auf den Standardwert „5000“ geändert.
• Code Zeile 11-14: Füge den Datensatz in die Tabelle 'emp' ein.
• Code Zeile 16: Überprüfung des eingefügten Datensatzes.

Ausgang:

Trigger created

PL/SQL procedure successfully completed.

Trigger aktivieren und deaktivieren

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

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 ein einzelner Trigger aktiviert/deaktiviert wird.
• Die zweite Anweisung zeigt, wie alle Trigger für eine bestimmte Tabelle aktiviert/deaktiviert werden.

Häufig gestellte Fragen