Oracle PL/SQL Trigger: & Összetett típusok helyett

Mi az a trigger a PL/SQL-ben?

A TRIGGEREIT tárolja PL / SQL által indított programok Oracle motor automatikusan, amikor DML utasítások Az olyan műveletek, mint az insert, update és delete, a táblázaton kerülnek végrehajtásra, vagy bizonyos események bekövetkeztekor. A trigger esetén végrehajtandó kód a követelményeknek megfelelően definiálható. Kiválaszthatja azt az eseményt, amelyre a triggert ki kell váltani, és a végrehajtás időzítését. A trigger célja az adatbázisban található információk integritásának fenntartása.

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.

Az időzítésen alapuló osztályozás

• ELŐTT Trigger: A megadott esemény bekövetkezte előtt aktiválódik.
• UTÁNA Trigger: A megadott esemény bekövetkezte után aktiválódik.
• A TRIGDER HELYETT: Egy különleges típus. A következő témákban többet is megtudhat róla. (csak DML esetén)

Szint szerinti osztályozás

• UTASÍTÁS szintű Trigger: Egyszer aktiválódik a megadott eseményutasításhoz.
• SOR szintű trigger: Minden olyan rekordnál aktiválódik, amelyet a megadott esemény érint. (csak DML esetén)

Eseményen alapuló osztályozás

• DML-indító: Akkor aktiválódik, amikor a DML esemény meg van adva (INSERT/UPDATE/DELETE).
• DDL-indító: Akkor aktiválódik, amikor a DDL esemény meg van adva (CREATE/ALTER).
• ADATBÁZIS Trigger: Akkor aktiválódik, amikor 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ó egy trigger létrehozásának szintaxisa. Az alábbi képernyőkép ezt a trigger létrehozási szintaxist mutatja be a következőképpen: 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;

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.
• Az ELŐTT/UTÁN mezők határozzák meg 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 azt az objektumot, amelyen a fent említett esemény érvényes. Például ez lesz annak a táblának a neve, amelyen a DML esemény bekövetkezhet egy DML trigger esetén.
• A „FOR EACH ROW” parancs határozza meg a sor szintű triggert.
• A WHEN záradék határozza meg azt a további feltételt, amely teljesülése esetén a triggernek működnie kell.
• A deklarációs rész, a végrehajtási rész és a kivételkezelési rész megegyezik a többivel. PL/SQL blokkokA nyilatkozati rész és a kivétel kezelése részek opcionálisak.

: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 sor szintű triggerben ezen értékek tárolására. Ezekkel a záradékokkal hivatkozhatunk a trigger törzsében található régi és új értékekre.

• :ÚJ – Új értéket tárol az alaptábla/nézet oszlopaihoz a trigger végrehajtása során.
• :RÉGI – A trigger végrehajtása során az alaptábla/nézet oszlopainak régi értékét tárolja.

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

Trigger HELYETT

Az „INSTEAD OF trigger” egy speciális triggertípus. Csak DML triggerekben használják. Akkor használják, ha valamilyen DML esemény fog bekövetkezni egy összetett nézeten.

Vegyünk egy példát, amelyben egy nézet három alaptáblából készül. Amikor bármilyen DML esemény történik ezen a nézeten, az érvénytelenné válik, mivel az adatok három különböző táblából származnak. Tehát ebben az esetben egy INSTEAD OF triggert használunk. Az INSTEAD OF triggert az alaptáblák közvetlen módosítására használjuk a nézet módosítása helyett az adott eseményhez.

Példa 1: Ebben a példában egy összetett nézetet fogunk létrehozni két alaptáblából, ahol a Table_1 az emp tábla, a Table_2 pedig a részleg tábla.

Ezután megvizsgáljuk, hogyan használható az INSTEAD OF trigger a helyszín részleteinek UPDATE (FRISSÍTÉS) kiadására ezen az összetett nézeten. Azt is megvizsgáljuk, hogy a :NEW és :OLD triggerekben hogyan hasznosak. A példa a következő lépésekben valósul meg:

• 1. lépés: Az „emp” és a „dept” táblák létrehozása megfelelő oszlopokkal
• 2. lépés: A táblázatok feltöltése mintaértékekkel
• 3. lépés: Nézet létrehozása a fent létrehozott táblázatokhoz
• 4. lépés: A nézet frissítése az INSTEAD OF trigger előtt
• 5. lépés: Az INSTEAD OF trigger létrehozása
• 6. lépés: A nézet frissítése az INSTEAD OF trigger után

1. lépés) Az „emp” és a „dept” táblák létrehozása a megfelelő oszlopokkal.

Az alábbi képernyőkép az „emp” és a „dept” alaptáblák létrehozását mutatja a 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 Magyarázat

• Code 1-7. sor: Az „emp” tábla létrehozása.
• Code 8-12. sor: „Részleg” tábla létrehozása.

output:

Table Created

Step 2) Mivel létrehoztuk a táblázatokat, most mintaértékekkel fogjuk feltölteni őket.

Az alábbi képernyőképen a minta sorok beszúrása látható a „dept” és az „emp” táblákba.

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 Magyarázat

• Code 13-19. sor: Adatok beszúrása a 'dept' táblázatba.
• Code 20-26. sor: Adatok beszúrása az 'emp' táblába.

output:

PL/SQL procedure completed

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

Az alábbi képernyőkép a létrehozott és lekérdezett összetett nézetet mutatja.

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 Magyarázat

• Code 27-32. sor: A 'guru99_emp_view' nézet létrehozása.
• Code 33. sor: A guru99_emp_view lekérdezése.

output:

View created

Step 4) A nézet frissítése az INSTEAD OF trigger előtt.

Az alábbi képernyőkép a komplex nézet frissítési kísérletét és az ebből eredő hibát mutatja.

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

Code Magyarázat

• Code 34-38. sor: Frissítse az „XXX” helyét „FRANCE”-re. Kivételt okozott, mert a DML utasítások nem engedélyezettek közvetlenül az összetett nézetben.

output:

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

ORA-06512: at line 2

Step 5) Az előző lépésben a nézet frissítésekor felmerült hiba elkerülése érdekében ebben a lépésben egy „INSTEAD OF triggert” fogunk használni.

Az alábbi képernyőkép az INSTEAD OF trigger létrehozását mutatja.

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 Magyarázat

• Code 39. sor: Az INSTEAD OF trigger létrehozása az 'UPDATE' eseményhez a 'guru99_emp_view' nézetben, sor szinten. Tartalmazza a frissítési utasítást a 'dept' alaptáblában lévő hely frissítéséhez.
• Code 44. sor: A frissítési utasítás a ':NEW' és ':OLD' változókat használja az oszlopok frissítés előtti és utáni értékének megkereséséhez.

output:

Trigger Created

Step 6) A nézet frissítése az INSTEAD OF trigger után. Most a hiba nem jelenik meg, mivel az „INSTEAD OF trigger” fogja kezelni ennek az összetett nézetnek a frissítési műveletét. A kód végrehajtásakor XXX alkalmazott helye frissül „Japánról” „Franciaországra”.

Az alábbi képernyőkép az INSTEAD OF triggereken keresztüli sikeres frissítést és a frissített nézetet mutatja.

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

SELECT * FROM guru99_emp_view;

Code Magyarázat:

• Code 49-53. sor: Az „XXX” helyének frissítése „FRANCE”-re. Sikeres, mert az „INSTEAD OF” trigger leállította a tényleges frissítési utasítást a nézeten, és elvégezte az alaptábla frissítését.
• Code 55. sor: A frissített rekord ellenőrzése.

output:

PL/SQL procedure successfully completed

Összetett trigger

Az összetett trigger egy olyan trigger, amely lehetővé teszi, hogy egyetlen trigger törzsben négy időzítési pont mindegyikéhez műveleteket adjon meg. A négy különböző időzítési pont, amelyet támogat, az alábbiak.

• 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ésekhez tartozó műveletek egyetlen triggerbe történő kombinálását.

Az alábbi képernyőkép az összetett trigger szintaxist mutatja be a négy időzítési szakaszával.

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;

Szintaxis magyarázata:

• A fenti szintaxis egy „COMPOUND” trigger létrehozását mutatja be.
• A deklaratív szakasz közös az trigger törzsében található összes végrehajtási blokkban.
• Ez a négy időzítő blokk bármilyen sorrendben lehet. Nem kötelező mind a négy időzítő blokk megléte. Létrehozhatunk egy ÖSSZETETT triggert csak a szükséges időzítésekhez.

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

Az alábbi képernyőkép az összetett trigger példáját és annak kimenetét mutatja.

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 Magyarázat:

• Code 2-10. sor: Az összetett trigger létrehozása. Létrejön a BEFORE ROW szintű időzítéshez, hogy a fizetést az alapértelmezett 5000-es értékkel töltse fel. Ez a fizetést az alapértelmezett '5000' értékre módosítja, mielőtt a rekordot beillesztené a táblázatba.
• Code 11-14. sor: Helyezze be a rekordot az 'emp' táblába.
• Code 16. sor: A beillesztett rekord ellenőrzése.

output:

Trigger created

PL/SQL procedure successfully completed.

Triggerek engedélyezése és letiltása

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

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

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

Szintaxis magyarázata:

• Az első szintaxis bemutatja, hogyan lehet engedélyezni/letiltani egyetlen triggert.
• A második utasítás megmutatja, hogyan engedélyezheti/letilthatja az összes triggert egy adott táblán.

GYIK