PL/SQL – Triggers

In dit hoofdstuk gaan we het hebben over Triggers in PL/SQL. Triggers zijn opgeslagen programma’s, die automatisch worden uitgevoerd of afgevuurd wanneer bepaalde gebeurtenissen zich voordoen. Triggers worden in feite geschreven om te worden uitgevoerd in reactie op een van de volgende gebeurtenissen –

• Een database manipulatie (DML) statement (DELETE, INSERT, of UPDATE)

• Een database definitie (DDL) statement (CREATE, ALTER, of DROP).

• Een databasebewerking (SERVERERROR, LOGON, LOGOFF, STARTUP of SHUTDOWN).

Triggers kunnen worden gedefinieerd voor de tabel, view, schema of database waaraan de gebeurtenis is gekoppeld.

Voordelen van triggers

Triggers kunnen voor de volgende doeleinden worden geschreven –

• Het automatisch genereren van bepaalde afgeleide kolomwaarden
• Het afdwingen van referentiële integriteit
• Event logging en het opslaan van informatie over de toegang tot tabellen
• Auditing
• Synchrone replicatie van tabellen
• Het instellen van beveiligingsautorisaties
• Het voorkomen van ongeldige transacties

Triggers maken

De syntaxis voor het maken van een trigger is –

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; 

Waar,

• CREATE TRIGGER trigger_name – Creëert of vervangt een bestaande trigger met de trigger_name.

• {BEFORE | AFTER | INSTEAD OF} – Hiermee wordt gespecificeerd wanneer de trigger wordt uitgevoerd. De INSTEAD OF-clausule wordt gebruikt om een trigger op een view te maken.

• {INSERT | UPDATE | DELETE} – Hiermee wordt de DML-bewerking gespecificeerd.

• – Hiermee wordt de kolomnaam gespecificeerd die zal worden bijgewerkt.

• – Hiermee wordt de naam gespecificeerd van de tabel die aan de trigger is gekoppeld.

• – Hiermee kunt u nieuwe en oude waarden voor verschillende DML-statements, zoals INSERT, UPDATE en DELETE.

• – Hiermee specificeert u een trigger op rijniveau, d.w.z, de trigger wordt uitgevoerd voor elke rij die wordt beïnvloed. Anders wordt de trigger slechts eenmaal uitgevoerd wanneer het SQL-instructie wordt uitgevoerd, wat een trigger op tabelniveau wordt genoemd.

• WAN (voorwaarde) – Hiermee wordt een voorwaarde opgegeven voor rijen waarvoor de trigger zou afgaan. Deze clausule is alleen geldig voor triggers op rijniveau.

Voorbeeld

Om te beginnen gebruiken we de tabel KLANTEN die we in de vorige hoofdstukken hebben gemaakt en gebruikt –

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 | +----+----------+-----+-----------+----------+ 

Het volgende programma maakt een trigger op rijniveau voor de tabel klanten die zou afgaan bij INSERT-, UPDATE- of DELETE-bewerkingen die op de tabel KLANTEN worden uitgevoerd. Deze trigger zal het salarisverschil tussen de oude en nieuwe waarden weergeven –

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; / 

Wanneer de bovenstaande code wordt uitgevoerd op de SQL prompt, levert dit het volgende resultaat op –

Trigger created.

De volgende punten moeten hier in overweging worden genomen –

• OLD en NEW referenties zijn niet beschikbaar voor triggers op tabelniveau, maar u kunt ze gebruiken voor triggers op recordniveau.

• Als je de tabel in dezelfde trigger wilt query’en, dan moet je het AFTER keyword gebruiken, omdat triggers de tabel pas opnieuw kunnen query’en of wijzigen nadat de initiële wijzigingen zijn toegepast en de tabel weer in een consistente staat is.

• De bovenstaande trigger is zo geschreven dat deze afgaat voor elke DELETE-, INSERT- of UPDATE-bewerking op de tabel, maar je kunt je trigger op een enkele of meerdere bewerkingen schrijven, bijvoorbeeld BEFORE DELETE, die afgaat wanneer een record wordt verwijderd met de DELETE-bewerking op de tabel.

Triggeren van een Trigger

Laten we enkele DML-bewerkingen op de tabel CUSTOMERS uitvoeren. Hier is een INSERT-instructie, die een nieuw record in de tabel zal aanmaken –

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

Wanneer een record in de tabel KLANTEN wordt aangemaakt, zal de bovenstaande create-trigger, display_salary_changes worden afgevuurd en zal het volgende resultaat worden weergegeven –

Old salary: New salary: 7500 Salary difference:

Omdat dit een nieuw record is, is het oude salaris niet beschikbaar en komt het bovenstaande resultaat als null. Laten we nu nog een DML-bewerking op de tabel CUSTOMERS uitvoeren. Het UPDATE statement zal een bestaand record in de tabel bijwerken –

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

Wanneer een record in de tabel CUSTOMERS wordt bijgewerkt, zal de bovenstaande create trigger, display_salary_changes worden afgevuurd en zal het volgende resultaat worden weergegeven –

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