Oracle PL/SQL trigger oktatóanyag: Összetett helyett [Példa]

Mi az a trigger a PL/SQL-ben?

TRIGGEREK tárolt programok, amelyeket a Oracle motor automatikusan, amikor DML utasítások, például beszúrás, frissítés, törlés végrehajtásra kerülnek a táblán, vagy bizonyos események bekövetkeznek. A trigger esetén végrehajtandó kód az igény szerint definiálható. Kiválaszthatja az eseményt, amelynél a triggert el kell indítani, és a végrehajtás időpontját. A trigger célja az adatbázisban lévő információk integritásának megőrzése.

A triggerek előnyei

Az alábbiakban bemutatjuk a triggerek előnyeit.

• Néhány származtatott oszlopérték automatikus generálása
• A hivatkozási integritás érvényesítése
• Eseménynaplózás és információk tárolása az asztali hozzáféréssel kapcsolatban
• Könyvvizsgálat
• Synctáblázatok hronikus replikációja
• Biztonsági felhatalmazások előírása
• Érvénytelen tranzakciók megelőzése

A triggerek típusai Oracle

A triggerek a következő paraméterek alapján osztályozhatók.

• Osztályozás a időzítés
• BEFORE Trigger: A megadott esemény bekövetkezte előtt aktiválódik.
• AFTER Trigger: A megadott esemény bekövetkezte után aktiválódik.
• Trigger HELYETT: Egy speciális típus. A további témákról többet megtudhat. (csak DML esetén)
• Osztályozás a szint
• STATEMENT szintű trigger: Egyszer aktiválódik a megadott eseményutasításhoz.
• SOR szintű trigger: Minden olyan rekord esetén aktiválódik, amelyet a megadott esemény érintett. (csak DML esetén)
• Osztályozás a esemény
• DML Trigger: Akkor indul el, ha a DML eseményt megadják (INSERT/UPDATE/DELETE)
• DDL trigger: Akkor indul el, ha a DDL eseményt megadják (CREATE/ALTER)
• ADATBÁZIS-indító: Akkor indul el, ha az adatbázis-esemény meg van adva (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Tehát minden trigger a fenti paraméterek kombinációja.

Hogyan hozzunk létre triggert

Az alábbiakban látható a trigger létrehozásának szintaxisa.

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;

Szintaxis magyarázata:

• A fenti szintaxis bemutatja a különböző választható utasításokat, amelyek jelen vannak az eseményindító létrehozásában.
• A BEFORE/AFTER meghatározza az események időzítését.
• INSERT/UPDATE/LOGON/CREATE/stb. megadja azt az eseményt, amelyhez a triggert ki kell indítani.
• Az ON záradék határozza meg, hogy a fent említett esemény melyik objektumon érvényes. Például ez lesz az a táblanév, amelyen a DML esemény előfordulhat DML Trigger esetén.
• A „FOR EACH ROW” parancs határozza meg a SORA szintű triggert.
• A WHEN záradék meghatározza azt a további feltételt, amelyben az eseményindítónak aktiválódnia kell.
• A deklarációs rész, végrehajtási rész, kivételkezelési rész megegyezik a másikéval PL/SQL blokkok. A nyilatkozat rész és a kivételkezelés rész nem kötelező.

:NEW és :OLD záradék

Egy sorszintű aktiválási szabálynál az aktiválási szabály minden kapcsolódó sornál aktiválódik. És néha szükséges tudni a DML utasítás előtti és utáni értéket.

Oracle két záradékot biztosított a RECORD szintű triggerben ezen értékek megtartásához. Ezekkel a tagmondatokkal hivatkozhatunk a trigger törzsön belüli régi és új értékekre.

• :NEW – Új értéket tárol az alaptábla/nézet oszlopaihoz a trigger végrehajtása során
• :OLD – Megőrzi az alaptábla/nézet oszlopainak régi értékét a trigger végrehajtása során

Ezt a záradékot a DML esemény alapján kell használni. Az alábbi táblázat meghatározza, hogy melyik DML utasításhoz melyik záradék érvényes (INSERT/UPDATE/DELETE).

	INSERT	UPDATE	DELETE
:ÚJ	ÉRVÉNYES	ÉRVÉNYES	ÉRVÉNYTELEN. A törlés esetén nincs új érték.
:RÉGI	ÉRVÉNYTELEN. A betétbetétben nincs régi érték	ÉRVÉNYES	ÉRVÉNYES

Trigger HELYETT

„INSTEAD OF trigger” a trigger speciális típusa. Csak DML triggerekben használatos. Akkor használatos, ha bármilyen DML esemény fog bekövetkezni az összetett nézetben.

Vegyünk egy példát, amelyben a nézet 3 alaptáblázatból készül. Ha bármilyen DML-esemény kerül kiadásra ebben a nézetben, az érvénytelen lesz, mivel az adatok 3 különböző táblából származnak. Tehát ebben az INTEAD OF trigger használatos. Az INSTEAD OF trigger az alaptáblázatok közvetlen módosítására szolgál az adott esemény nézetének módosítása helyett.

Példa 1: Ebben a példában egy összetett nézetet fogunk létrehozni két alaptáblázatból.

• A Table_1 az emp táblázat és

• A Table_2 a részlegtábla.

Ezután meglátjuk, hogyan használják az INSTEAD OF triggert a UPDATE hely részletes utasításának kiadására ebben az összetett nézetben. Azt is látni fogjuk, hogy a :NEW és :OLD mennyire hasznosak a triggerekben.

• 1. lépés: 'emp' és 'dept' táblázat létrehozása megfelelő oszlopokkal
• 2. lépés: A táblázat kitöltése mintaértékekkel
• 3. lépés: Nézet létrehozása a fent létrehozott táblázathoz
• 4. lépés: Frissítse a nézetet az aktiváló helyett
• 5. lépés: A trigger helyett a trigger létrehozása
• 6. lépés: A nézet frissítése a trigger helyett

Step 1) 'emp' és 'dept' táblázat létrehozása megfelelő oszlopokkal

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

Kód Magyarázat

• Kódsor 1-7: 'emp' tábla létrehozása.
• Kódsor 8-12: Táblázat 'részleg' létrehozása.

teljesítmény

Táblázat létrehozva

Step 2) Most, hogy létrehoztuk a táblát, ezt a táblázatot mintaértékekkel és Nézetek létrehozása elemmel fogjuk feltölteni a fenti táblákhoz.

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

Kód Magyarázat

• Kódsor 13-19: Adatok beszúrása a 'részleg' táblázatba.
• 20-26. kódsor: Adatok beszúrása az 'emp' táblába.

teljesítmény

PL/SQL eljárás befejezték

Step 3) Nézet létrehozása a fent létrehozott táblázathoz.

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;

Kód Magyarázat

• 27-32. kódsor: A 'guru99_emp_view' nézet létrehozása.

• 33. kódsor: A guru99_emp_view lekérdezése.

teljesítmény

Nézet létrehozva

ALKALMAZOTT NEVE	DEPT_NAME	HELYSZÍN
ZZZ	HR	USA
ÉÉÉÉ	ÉRTÉKESÍTÉSI	UK
XXX	PÉNZÜGYI	JAPÁN

Step 4) A trigger helyett a nézet frissítése.

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

Kód Magyarázat

• 34-38. kódsor: Frissítse a „XXX” helyét „FRANCIA”-ra. Felvetette a kivételt, mert a DML utasítások nem engedélyezettek az összetett nézetben.

teljesítmény

ORA-01779: nem módosítható olyan oszlop, amely egy nem kulcs által megőrzött táblára van leképezve

ORA-06512: a 2. sorban

Step 5)Az előző lépésben a nézet frissítése során fellépő hiba elkerülése érdekében ebben a lépésben a „trigger helyett” szót fogjuk használni.

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

Kód Magyarázat

• 39. kódsor: Az 'UPDATE' esemény INSTEAD OF triggerének létrehozása a 'guru99_emp_view' nézetben a ROW szintjén. Tartalmazza a frissítési utasítást a 'dept' alaptábla helyének frissítéséhez.
• 44. kódsor: Az Update utasítás a „:NEW” és „: OLD” karakterláncot használja a frissítés előtti és utáni oszlopok értékének meghatározásához.

teljesítmény

Trigger létrehozva

Step 6) A nézet frissítése az eseményindító helyett. Most a hiba nem fog megjelenni, mivel a „kioldó helyett” kezeli ennek az összetett nézetnek a frissítési műveletét. És amikor a kód végrehajtódott, a XXX alkalmazott tartózkodási helye „Franciaországra” módosul „Japán” helyett.

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

SELECT * FROM guru99_emp_view;

Kód magyarázata:

• 49-53. kódsor: „XXX” helyének frissítése „FRANCIA”-ra. Sikeres, mert az 'INSTEAD OF' trigger leállította a tényleges frissítési utasítást a nézetben, és végrehajtotta az alaptábla frissítését.
• 55. kódsor: A frissített rekord ellenőrzése.

output:

A PL/SQL eljárás sikeresen befejeződött

ALKALMAZOTT NEVE	DEPT_NAME	HELYSZÍN
ZZZ	HR	USA
ÉÉÉÉ	ÉRTÉKESÍTÉSI	UK
XXX	PÉNZÜGYI	FRANCIAORSZÁG

Összetett trigger

Az Összetett trigger egy olyan trigger, amely lehetővé teszi a műveletek megadását az egyetlen trigger törzsben található négy időpont mindegyikéhez. Az általa támogatott négy különböző időzítési pont az alábbi.

• NYILATKOZAT ELŐTT – szint
• SOR ELŐTT – szint
• SOR UTÁN – szint
• NYILATKOZAT UTÁN – szint

Lehetővé teszi a különböző időzítésű műveletek egyazon triggerben történő kombinálását.

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;

Szintaxis magyarázata:

• A fenti szintaxis a „COMPOUND” trigger létrehozását mutatja.
• A deklaratív szakasz közös a trigger törzsben lévő összes végrehajtási blokknál.
• Ez a 4 időzítési blokk bármilyen sorrendben lehet. Nem kötelező mind a 4 időzítési blokk megléte. Csak a szükséges időzítésekhez hozhatunk létre COMPOUND triggert.

Példa 1: Ebben a példában létrehozunk egy triggert, amely automatikusan kitölti a fizetés oszlopot az alapértelmezett 5000 értékkel.

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;

Kód magyarázata:

• Kódsor 2-10: Összetett trigger létrehozása. Létrejön a SOR ELŐTT időzítéshez, hogy a fizetést 5000 alapértelmezett értékkel töltse fel. Ezzel a fizetést az alapértelmezett '5000' értékre változtatja, mielőtt a rekordot beszúrná a táblázatba.
• Kódsor 11-14: A rekord beszúrása az 'emp' táblába.
• 16. kódsor: A beillesztett rekord ellenőrzése.

output:

Trigger létrehozva

A PL/SQL eljárás sikeresen befejeződött.

EMP_NAME	EMP_NO	FIZETÉS	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

Triggerek engedélyezése és letiltása

A triggerek engedélyezhetők vagy letilthatók. A trigger engedélyezéséhez vagy letiltásához egy ALTER (DDL) utasítást kell adni a triggerhez, amely letiltja vagy engedélyezi.

Az alábbiakban látható a triggerek engedélyezésének/letiltásának szintaxisa.

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

Szintaxis magyarázata:

• Az első szintaxis megmutatja, hogyan engedélyezhető/letiltható az egyszeri trigger.
• A második utasítás megmutatja, hogyan engedélyezheti/letilthatja az összes triggert egy adott táblán.

Összegzésként

Ebben a fejezetben megismerkedtünk a PL/SQL triggerekkel és azok előnyeivel. Megtanultuk a különböző besorolásokat is, és megvitattuk a trigger és az összetett trigger HELYETT.