PL/SQL – Triggers

In diesem Kapitel werden wir Triggers in PL/SQL behandeln. Trigger sind gespeicherte Programme, die automatisch ausgeführt oder abgefeuert werden, wenn bestimmte Ereignisse eintreten. Trigger werden so geschrieben, dass sie als Reaktion auf eines der folgenden Ereignisse ausgeführt werden

• Eine Datenbankmanipulationsanweisung (DML) (DELETE, INSERT oder UPDATE)

• Eine Datenbankdefinitionsanweisung (DDL) (CREATE, ALTER oder DROP).

• Eine Datenbankoperation (SERVERERROR, LOGON, LOGOFF, STARTUP oder SHUTDOWN).

Trigger können für die Tabelle, den View, das Schema oder die Datenbank definiert werden, mit der das Ereignis verbunden ist.

Nutzen von Triggern

Trigger können für folgende Zwecke geschrieben werden –

• Einige abgeleitete Spaltenwerte automatisch generieren
• Referentielle Integrität erzwingen
• Ereignisprotokollierung und Speichern von Informationen über Tabellenzugriffe
• Auditing
• Synchrone Replikation von Tabellen
• Sicherheitsberechtigungen einführen
• Ungültige Transaktionen verhindern

Erstellen von Triggern

Die Syntax zum Erstellen eines Triggers lautet –

CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT | UPDATE | DELETE} ON table_name WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; 

Wo,

• CREATE TRIGGER trigger_name – Erzeugt oder ersetzt einen vorhandenen Trigger mit dem Trigger_namen.

• {BEFORE | AFTER | INSTEAD OF} – Dies gibt an, wann der Trigger ausgeführt wird. Die INSTEAD OF-Klausel wird zum Erstellen von Triggern auf einer Ansicht verwendet.

• {INSERT | UPDATE | DELETE} – Dies gibt die DML-Operation an.

• – Dies gibt den Spaltennamen an, der aktualisiert wird.

• – Dies gibt den Namen der Tabelle an, die mit dem Trigger verknüpft ist.

• – Damit können Sie neue und alte Werte für verschiedene DML-Anweisungen wie INSERT, UPDATE und DELETE referenzieren.

• – Dies spezifiziert einen Trigger auf Zeilenebene, d.h., der Trigger wird für jede betroffene Zeile ausgeführt. Andernfalls wird der Trigger nur einmal ausgeführt, wenn die SQL-Anweisung ausgeführt wird, was als Trigger auf Tabellenebene bezeichnet wird.

• WHEN (Bedingung) – Dies gibt eine Bedingung für Zeilen an, für die der Trigger ausgelöst würde. Diese Klausel ist nur bei Triggern auf Zeilenebene gültig.

Beispiel

Zu Beginn verwenden wir die Tabelle KUNDEN, die wir in den vorherigen Kapiteln erstellt und verwendet haben –

Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+ 

Das folgende Programm erstellt einen Trigger auf Zeilenebene für die Tabelle KUNDEN, der bei INSERT- oder UPDATE- oder DELETE-Operationen in der Tabelle KUNDEN ausgelöst würde. Dieser Trigger zeigt die Gehaltsdifferenz zwischen den alten Werten und den neuen Werten an –

CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW WHEN (NEW.ID > 0) DECLARE sal_diff number; BEGIN sal_diff := :NEW.salary - :OLD.salary; dbms_output.put_line('Old salary: ' || :OLD.salary); dbms_output.put_line('New salary: ' || :NEW.salary); dbms_output.put_line('Salary difference: ' || sal_diff); END; / 

Wenn der obige Code an der SQL-Eingabeaufforderung ausgeführt wird, wird, ergibt sich folgendes Ergebnis –

Trigger created.

Dabei sind folgende Punkte zu beachten –

• OLD- und NEW-Referenzen sind nicht für Trigger auf Tabellenebene verfügbar, sondern können für Trigger auf Datensatzebene verwendet werden.

• Wenn Sie die Tabelle im selben Trigger abfragen wollen, dann sollten Sie das Schlüsselwort AFTER verwenden, denn Trigger können die Tabelle erst dann abfragen oder erneut ändern, wenn die ursprünglichen Änderungen angewendet wurden und die Tabelle wieder in einem konsistenten Zustand ist.

• Der obige Trigger wurde so geschrieben, dass er vor jeder DELETE- oder INSERT- oder UPDATE-Operation auf der Tabelle feuert, aber Sie können Ihren Trigger auf eine einzelne oder mehrere Operationen schreiben, zum Beispiel BEFORE DELETE, der immer dann feuert, wenn ein Datensatz mit der DELETE-Operation auf der Tabelle gelöscht wird.

Triggern eines Triggers

Lassen Sie uns einige DML-Operationen auf der Tabelle CUSTOMERS durchführen. Hier ist eine INSERT-Anweisung, die einen neuen Datensatz in der Tabelle erstellt –

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Kriti', 22, 'HP', 7500.00 ); 

Wenn ein Datensatz in der Tabelle CUSTOMERS erstellt wird, wird der obige Trigger create, display_salary_changes ausgelöst und zeigt das folgende Ergebnis an –

Old salary: New salary: 7500 Salary difference:

Da es sich um einen neuen Datensatz handelt, ist das alte Gehalt nicht verfügbar und das obige Ergebnis wird als Null ausgegeben. Lassen Sie uns nun eine weitere DML-Operation auf der Tabelle CUSTOMERS durchführen. Die UPDATE-Anweisung wird einen vorhandenen Datensatz in der Tabelle aktualisieren –

UPDATE customers SET salary = salary + 500 WHERE id = 2; 

Wenn ein Datensatz in der Tabelle CUSTOMERS aktualisiert wird, wird der obige Trigger create, display_salary_changes abgefeuert und zeigt das folgende Ergebnis an –

Old salary: 1500 New salary: 2000 Salary difference: 500