Oracle PL/SQL Trigger-tutorial: in plaats van Compound [Voorbeeld]

Wat is Trigger in PL/SQL?

TRIGGER zijn opgeslagen programma's die worden geactiveerd door Oracle engine automatisch wanneer DML-instructies zoals invoegen, bijwerken, verwijderen op de tabel worden uitgevoerd of als er gebeurtenissen plaatsvinden. De code die moet worden uitgevoerd in geval van een trigger kan naar wens worden gedefinieerd. U kunt de gebeurtenis kiezen waarop de trigger moet worden afgevuurd en de timing van de uitvoering. Het doel van trigger is om de integriteit van de informatie in de database te behouden.

Voordelen van triggers

Hieronder staan ​​de voordelen van triggers.

• Automatisch genereren van enkele afgeleide kolomwaarden
• Het afdwingen van referentiële integriteit
• Gebeurtenisregistratie en opslag van informatie over tafeltoegang
• Auditing
• Syncroneuze replicatie van tabellen
• Het opleggen van veiligheidsmachtigingen
• Ongeldige transacties voorkomen

Soorten triggers in Oracle

Triggers kunnen worden geclassificeerd op basis van de volgende parameters.

• Classificatie op basis van de timing
• BEFORE Trigger: De trigger wordt geactiveerd voordat de opgegeven gebeurtenis heeft plaatsgevonden.
• NA Trigger: Deze wordt geactiveerd nadat de opgegeven gebeurtenis heeft plaatsgevonden.
• IN PLAATS VAN Trigger: een speciaal type. U leert meer over de overige onderwerpen. (alleen voor DML)
• Classificatie op basis van de niveau
• STATEMENT-niveau Trigger: Deze wordt één keer geactiveerd voor de opgegeven gebeurtenisinstructie.
• Trigger op RIJ-niveau: Deze wordt geactiveerd voor elke record die door de opgegeven gebeurtenis is getroffen. (alleen voor DML)
• Classificatie op basis van de Gebeurtenis
• DML-trigger: Deze wordt geactiveerd wanneer de DML-gebeurtenis is opgegeven (INSERT/UPDATE/DELETE)
• DDL-trigger: wordt geactiveerd wanneer de DDL-gebeurtenis is opgegeven (CREATE/ALTER)
• DATABASE-trigger: Deze wordt geactiveerd wanneer de databasegebeurtenis is opgegeven (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Elke trigger is dus de combinatie van bovenstaande parameters.

Trigger creëren

Hieronder vindt u de syntaxis voor het maken van een trigger.

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;

Syntaxis uitleg:

• De bovenstaande syntaxis toont de verschillende optionele instructies die aanwezig zijn bij het maken van triggers.
• BEFORE/AFTER specificeert de timing van de gebeurtenissen.
• INSERT/UPDATE/LOGON/CREATE/etc. specificeert de gebeurtenis waarvoor de trigger moet worden geactiveerd.
• ON-clausule specificeert op welk object de bovengenoemde gebeurtenis geldig is. Dit is bijvoorbeeld de tabelnaam waarop de DML-gebeurtenis kan plaatsvinden in het geval van DML Trigger.
• Het commando “FOR EACH ROW” specificeert de trigger voor het ROW-niveau.
• De WHEN-clausule specificeert de aanvullende voorwaarde waarin de trigger moet worden geactiveerd.
• Het aangiftegedeelte, het uitvoeringsgedeelte en het afhandelingsgedeelte van uitzonderingen zijn hetzelfde als dat van de andere PL/SQL-blokken. Declaratiegedeelte en uitzonderingsafhandelingsgedeelte zijn optioneel.

:NIEUW en :OUD-clausule

Bij een trigger op rijniveau wordt de trigger geactiveerd voor elke gerelateerde rij. En soms is het nodig om de waarde voor en na de DML-instructie te kennen.

Oracle heeft twee clausules opgegeven in de trigger op RECORD-niveau om deze waarden vast te houden. We kunnen deze clausules gebruiken om te verwijzen naar de oude en nieuwe waarden in de triggerbody.

• :NIEUW – Het bevat een nieuwe waarde voor de kolommen van de basistabel/weergave tijdens de uitvoering van de trigger
• :OLD – Het bevat de oude waarde van de kolommen van de basistabel/weergave tijdens de uitvoering van de trigger

Deze clausule moet worden gebruikt op basis van de DML-gebeurtenis. Onderstaande tabel specificeert welke clausule geldig is voor welke DML-instructie (INSERT/UPDATE/DELETE).

	INSERT	UPDATE	VERWIJDEREN
:NIEUW	GELDIG	GELDIG	ONGELDIG. Er is geen nieuwe waarde bij het verwijderen van hoofdletters en kleine letters.
:OUD	ONGELDIG. Er staat geen oude waarde in de insteekhoes	GELDIG	GELDIG

IN PLAATS VAN Trigger

"INSTEAD OF trigger" is het speciale type trigger. Het wordt alleen gebruikt in DML-triggers. Het wordt gebruikt wanneer er een DML-gebeurtenis gaat plaatsvinden in de complexe weergave.

Beschouw een voorbeeld waarin een weergave wordt gemaakt op basis van 3 basistabellen. Wanneer een DML-gebeurtenis via deze weergave wordt uitgegeven, wordt deze ongeldig omdat de gegevens uit drie verschillende tabellen worden gehaald. Dus in deze INSTEAD OF-trigger wordt gebruikt. De INSTEAD OF-trigger wordt gebruikt om de basistabellen rechtstreeks te wijzigen in plaats van de weergave voor de gegeven gebeurtenis te wijzigen.

Voorbeeld 1: In dit voorbeeld gaan we een complexe weergave maken van twee basistabellen.

• Tabel_1 is emp-tabel en

• Tabel_2 is afdelingstabel.

Vervolgens gaan we kijken hoe de INSTEAD OF trigger wordt gebruikt om UPDATE the location detail statement uit te geven op deze complexe view. We gaan ook kijken hoe de :NEW en :OLD nuttig zijn in triggers.

• Stap 1: Tabel 'emp' en 'dept' maken met de juiste kolommen
• Stap 2: De tabel vullen met voorbeeldwaarden
• Stap 3: Weergave maken voor de hierboven gemaakte tabel
• Stap 4: Update van de weergave vóór de in plaats van trigger
• Stap 5: Creatie van de in plaats van trigger
• Stap 6: Update van weergave na in plaats van trigger

Stap 1) Tabel 'emp' en 'dept' maken met de juiste kolommen

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 Uitleg

• Coderegel 1-7: Tabel 'emp' aanmaken.
• Coderegel 8-12: Tabel 'dept' aanmaken.

uitgang

Tabel gemaakt

Stap 2) Nu we de tabel hebben gemaakt, zullen we deze tabel vullen met voorbeeldwaarden en het maken van weergaven voor de bovenstaande tabellen.

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 Uitleg

• Coderegel 13-19: Gegevens invoegen in de 'dept'-tabel.
• Coderegel 20-26: Gegevens invoegen in de 'emp'-tabel.

uitgang

PL/SQL-procedure voltooid

Stap 3) Een weergave maken voor de hierboven gemaakte tabel.

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 Uitleg

• Coderegel 27-32: Creatie van 'guru99_emp_view'-weergave.

• Coderegel 33: Er wordt een query uitgevoerd op guru99_emp_view.

uitgang

Weergave gemaakt

NAAM WERKNEMER	DEPT_NAME	LOCATIE
ZZZ	HR	USA
JJJ	VERKOOP	UK
XXX	FINANCIELE	JAPAN

Stap 4) Update van weergave vóór in plaats van trigger.

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

Code Uitleg

• Coderegel 34-38: Update de locatie van 'XXX' naar 'FRANCE'. Er werd een uitzondering gemaakt omdat de DML-instructies zijn niet toegestaan ​​in de complexe weergave.

uitgang

ORA-01779: kan een kolom die is toegewezen aan een niet-sleutelbewaarde tabel niet wijzigen

ORA-06512: op lijn 2

Stap 5)Om de fout te voorkomen tijdens het updaten van de weergave in de vorige stap, gaan we in deze stap 'in plaats van trigger' gebruiken.

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 Uitleg

• Coderegel 39: Creatie van INSTEAD OF trigger voor 'UPDATE'-gebeurtenis in de 'guru99_emp_view'-weergave op ROW-niveau. Het bevat de update-instructie om de locatie in de basistabel 'dept' bij te werken.
• Coderegel 44: Update-instructie gebruikt ':NEW' en ': OLD' om de waarde van kolommen vóór en na de update te vinden.

uitgang

Trigger gemaakt

Stap 6) Update van view na in plaats van trigger. Nu zal de fout niet meer optreden omdat de "in plaats van trigger" de update-bewerking van deze complexe view zal afhandelen. En wanneer de code is uitgevoerd, zal de locatie van werknemer XXX worden bijgewerkt van "Japan" naar "Frankrijk".

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

SELECT * FROM guru99_emp_view;

Code Verklaring:

• Coderegel 49-53: Update van de locatie van “XXX” naar 'FRANCE'. Dit is succesvol omdat de 'INSTEAD OF'-trigger de daadwerkelijke update-instructie heeft gestopt en de basistabel heeft bijgewerkt.
• Coderegel 55: Het bijgewerkte record verifiëren.

Output:

PL/SQL-procedure met succes voltooid

NAAM WERKNEMER	DEPT_NAME	LOCATIE
ZZZ	HR	USA
JJJ	VERKOOP	UK
XXX	FINANCIELE	FRANKRIJK

Samengestelde trigger

De samengestelde trigger is een trigger waarmee u acties kunt specificeren voor elk van de vier timingpunten in de enkele triggerbody. De vier verschillende timingpunten die het ondersteunt, zijn zoals hieronder.

• VOOR VERKLARING – niveau
• VOOR RIJ – niveau
• NA RIJ – niveau
• NA VERKLARING – niveau

Het biedt de mogelijkheid om de acties voor verschillende timing te combineren in dezelfde trigger.

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;

Syntaxis uitleg:

• De bovenstaande syntaxis toont de creatie van de 'COMPOUND'-trigger.
• Declaratieve sectie is gemeenschappelijk voor alle uitvoeringsblokken in de triggerbody.
• Deze 4 timingblokken kunnen in elke volgorde staan. Het is niet verplicht om al deze 4 timingblokken te hebben. We kunnen alleen een COMPOUND-trigger maken voor de timings die nodig zijn.

Voorbeeld 1: In dit voorbeeld gaan we een trigger maken om de salariskolom automatisch in te vullen met de standaardwaarde 5000.

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 Verklaring:

• Coderegel 2-10: Creatie van samengestelde trigger. Het is gemaakt voor timing BEFORE ROW-niveau om het salaris in te vullen met de standaardwaarde 5000. Hierdoor wordt het salaris gewijzigd naar de standaardwaarde '5000' voordat het record in de tabel wordt ingevoegd.
• Coderegel 11-14: Voeg het record in de 'emp'-tabel in.
• Coderegel 16: De ingevoegde record verifiëren.

Output:

Trigger gemaakt

PL/SQL-procedure met succes voltooid.

EMP_NAME	EMP_NO	SALARIS	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

Triggers in- en uitschakelen

Triggers kunnen worden in- of uitgeschakeld. Om de trigger in of uit te schakelen, moet een ALTER-instructie (DDL) worden gegeven voor de trigger die deze in- of uitschakelt.

Hieronder vindt u de syntaxis voor het in-/uitschakelen van de triggers.

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

Syntaxis uitleg:

• De eerste syntaxis laat zien hoe u de enkele trigger in-/uitschakelt.
• De tweede instructie laat zien hoe u alle triggers voor een bepaalde tabel kunt in-/uitschakelen.

Samenvatting

In dit hoofdstuk hebben we geleerd over PL/SQL-triggers en hun voordelen. We hebben ook de verschillende classificaties geleerd en INSTEAD OF trigger en COMPOUND trigger besproken.