Oracle PL / SQL Trigger oktatóanyag: vegyület helyett (példa)

Tartalomjegyzék:

Anonim

Mi az eseményindító a PL / SQL-ben?

A TRIGGERS olyan tárolt programok, amelyeket az Oracle motor automatikusan elindít, amikor a DML utasítások, például a beszúrás, frissítés, törlés az asztalon végrehajtásra kerülnek, vagy bizonyos események bekövetkeznek. A kiváltó kód kiváltása esetén meghatározható a követelmény szerint. Kiválaszthatja azt az eseményt, amelyen ki kell indítani a ravaszt, és a végrehajtás időzítését. A trigger célja az adatbázis integritásának fenntartása.

Ebben az oktatóanyagban megtanulja-

  • A kiváltó okok előnyei
  • Az Oracle kiváltó típusai
  • Trigger létrehozása
  • : ÚJ és: RÉGI záradék
  • A kiváltó ok helyett
  • Összetett kiváltó

A kiváltó okok előnyei

Az alábbiakban bemutatjuk a kiváltó okok előnyeit.

  • Egyes származtatott oszlopértékek automatikus generálása
  • A referenciális integritás érvényesítése
  • Eseménynaplózás és információk tárolása a hozzáféréshez
  • Ellenőrzés
  • A táblák szinkron replikációja
  • Biztonsági engedélyek előírása
  • Az érvénytelen tranzakciók megakadályozása

Az Oracle kiváltó típusai

Az eseményindítók a következő paraméterek alapján osztályozhatók.

  • Osztályozás az időzítés alapján
    • Indítás előtt: A megadott esemény bekövetkezte előtt aktiválódik.
    • Triger: Ugrás a megadott esemény bekövetkezte után.
    • Trigger helyett: Különleges típus. Tudjon meg többet a további témákról. (csak a DML esetében)
  • Besorolás a szint alapján
    • STATEMENT szintű indító: Egyszer indít a megadott esemény-utasításhoz.
    • ROW-szintű trigger: Minden olyan rekordot elindít, amelyet a megadott esemény érintett. (csak a DML esetében)
  • Besorolás az esemény alapján
    • DML-trigger: Akkor aktiválódik, ha a DML-esemény meg van adva (INSERT / UPDATE / DELETE)
    • DDL-trigger: Akkor aktiválódik, ha a DDL esemény meg van adva (CREATE / ALTER)
    • DATABASE trigger: Akkor aktiválódik, ha az adatbázis esemény meg van adva (LOGON / LOGOFF / STARTUP / SHUTDOWN)

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

Trigger létrehozása

Az alábbiakban bemutatjuk a ravaszt létrehozó szintaxist.

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

Szintaxis Magyarázat:

  • A fenti szintaxis bemutatja azokat a különféle opcionális utasításokat, amelyek jelen vannak a trigger létrehozásában.
  • A BEFORE / AFTER megadja az esemény időzítését.
  • INSERT / UPDATE / LOGON / CREATE / stb. meghatározza azt az eseményt, amelyre a ravaszt ki kell indítani.
  • Az ON záradék meghatározza, hogy mely objektumon érvényes a fent említett esemény. Például ez lesz az a táblázat neve, amelyen a DML esemény bekövetkezhet a DML Trigger esetében.
  • Az "EGYES SORRA" paranccsal megadhatja a SOR szintindítót.
  • A WHEN záradék meghatározza azokat a további feltételeket, amelyekben a ravaszt ki kell kapcsolni.
  • A deklarációs rész, a végrehajtási rész, a kivételkezelő rész megegyezik a többi PL / SQL blokkéval. A nyilatkozat és a kivételkezelés része választható.

: ÚJ és: RÉGI záradék

Egy sorszintű triggerben az trigger minden kapcsolódó sorra aktiválódik. Néha meg kell tudni az értéket a DML utasítás előtt és után.

Az Oracle két záradékot adott meg a RECORD-szintű triggerben, hogy ezeket az értékeket megtartsa. Ezekkel a záradékokkal hivatkozhatunk a trigger és a régi és új értékekre.

  • : ÚJ - Új értéket tartalmaz az alaptábla / nézet oszlopai számára az indító végrehajtása során
  • : RÉGI - Megtartja az alaptábla / nézet oszlopainak régi értékét az indító 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 záradék mely DML utasításra érvényes (INSERT / UPDATE / DELETE).

INSERT FRISSÍTÉS TÖRÖL
:ÚJ ÉRVÉNYES ÉRVÉNYES ÉRVÉNYTELEN. Nincs új érték a törlési ügyben.
:RÉGI ÉRVÉNYTELEN. A betétes esetben nincs régi érték ÉRVÉNYES ÉRVÉNYES

A kiváltó ok helyett

Az "INSTEAD OF trigger" a trigger típusa. Csak DML kiváltókban alkalmazzák. Akkor alkalmazzák, amikor bármilyen DML esemény bekövetkezik az összetett nézetben.

Vegyünk egy példát, amelyben a nézet 3 alaptáblából készül. Ha bármilyen DML esemény kerül kiadásra ezen a nézeten, az érvénytelenné válik, mert az adatokat 3 különböző táblából veszik fel. Tehát ebben az INSTEAD OF ravaszt használják. Az INSTEAD OF trigger az alaptáblák közvetlen módosítására szolgál, az adott esemény nézetének módosítása helyett.

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

  • A tábla_1 az emp tábla és a
  • A tábla_2 az osztálytábla.

Aztán megnézzük, hogy az INSTEAD OF trigger használatával hogyan frissítik a helyadatok kimutatását ebben az összetett nézetben. Azt is meg fogjuk nézni, hogy a: NEW és: OLD hogyan hasznos az indítókban.

  • 1. lépés: Az 'emp' és a '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ához
  • 4. lépés: Frissítse a nézetet az indító helyett
  • 5. lépés: A kiváltó trigger létrehozása
  • 6. lépés: A nézet frissítése az aktiválás után

1. lépés: Az 'emp' és a '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

  • 1-7 kódsor: Az 'emp' táblázat létrehozása.
  • 8-12 kódsor : A tábla "osztály" létrehozása.

Kimenet

Tábla létrehozva

2. lépés) Most, hogy létrehoztuk a táblázatot, ezt a táblázatot feltöltjük mintaértékekkel és a fenti táblák nézeteinek létrehozásával. 

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT 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 "osztály" táblába.
  • 20-26 kódsor: Adatok beszúrása az 'emp' táblába.

Kimenet

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

3. lépés: Nézet létrehozása a fenti táblázathoz. 

CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE emp.dept_no=dept.dept_no;/
SELECT * FROM guru99_emp_view;

Kód Magyarázat

  • Kódsor 27-32: A 'guru99_emp_view' nézet létrehozása.
  • 33. kódsor : guru99_emp_view lekérdezése.

Kimenet

Nézet létrehozva

ALKALMAZOTT NEVE DEPT_NAME ELHELYEZKEDÉS
ZZZ HR USA
YYY ELADÁS Egyesült Királyság
XXX PÉNZÜGYI JAPÁN

4. lépés: Frissítse a nézetet a trigger helyett.

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

Kód Magyarázat

  • 34-38 kódsor: Frissítse az "XXX" helyét "FRANCE" -re. Azért vetett fel kivételt, mert a DML utasítások nem engedélyezettek az összetett nézetben.

Kimenet

ORA-01779: nem módosíthatja azt az oszlopot, amely egy nem kulcsfontosságú táblához társul

ORA-06512: a 2. vonalon

5. lépés : Az előző lépésben a nézet frissítése során fellépő hibák elkerülése érdekében ebben a lépésben a "trigger helyett" fogunk használni.

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/

Kód Magyarázat

  • 39. kódsor : INSTEAD OF trigger létrehozása az „UPDATE” eseményhez a „guru99_emp_view” nézetben ROW szinten. Ez tartalmazza a frissítési utasítást, hogy frissítse a helyet az alaptáblában.
  • 44. kódsor: Az Update utasítás az ': NEW' és ': OLD' szavakkal keresi meg a frissítés előtti és utáni oszlopok értékét.

Kimenet

Trigger létrehozva

6. lépés: A nézet frissítése az indítás után. Most a hiba nem fog bekövetkezni, mivel a "trigger helyett" kezeli ennek az összetett nézetnek a frissítési műveletét. És amikor a kód végrehajtotta, a XXX alkalmazott helyét a „Japan” -ra frissítjük „France” -ra.

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;

Kód Magyarázat:

  • Kódsor 49-53: Az "XXX" helyének frissítése "FRANCE" -re. Sikeres, mert az 'INSTEAD OF' ravaszt leállította a tényleges frissítési utasítással a megtekintéskor, és végrehajtotta az alaptábla frissítését.
  • 55. kódsor : A frissített rekord ellenőrzése.

Kimenet:

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

ALKALMAZOTT NEVE DEPT_NAME ELHELYEZKEDÉS
ZZZ HR USA
YYY ELADÁS Egyesült Királyság
XXX PÉNZÜGYI FRANCIAORSZÁG

Összetett kiváltó

Az Összetett ravaszt egy olyan trigger, amely lehetővé teszi, hogy műveleteket adjon meg az egyes ravasztörzs négy időzítési pontjára. Az általa támogatott négy különböző időzítési pont az alábbiak szerint alakul.

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

Lehetőséget nyújt arra, hogy a különböző időzítéshez kapcsolódó műveleteket ugyanabba az eseményindítóba egyesítse.

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;END AFTER STATEMENT;END;

Szintaxis Magyarázat:

  • A fenti szintaxis a „COMPOUND” trigger létrehozását mutatja.
  • A deklaratív szakasz a kiváltó törzs összes végrehajtási blokkjára vonatkozik.
  • Ez a 4 időzítő blokk bármilyen sorrendben lehet. Nem kötelező, hogy mind a 4 időzítő blokk legyen. Csak a szükséges időzítésekhez hozhatunk létre COMPOUND ravaszt.

1. példa : Ebben a példában létrehozunk egy triggeret, hogy automatikusan feltöltsük a fizetési oszlopot az alapértelmezett 5000 értékkel.

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/
BEGININSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30);COMMIT;END;/
SELECT * FROM emp WHERE emp_no=1004;

Kód Magyarázat:

  • 2-10. Kódsor : Összetett trigger létrehozása. A SOR ELŐTT időzítésre van létrehozva, hogy a fizetést 5000 alapértelmezett értékkel töltse fel. Ez megváltoztatja a fizetést alapértelmezett „5000” értékre, mielőtt a rekordot beszúrná a táblázatba.
  • Kódsor 11-14 : Helyezze be a rekordot az 'emp' táblába.
  • 16. kódsor: A beillesztett rekord ellenőrzése.

Kimenet:

Aktiváló létrehozva

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

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

Triggerek engedélyezése és letiltása

Az eseményindítók engedélyezhetők vagy letilthatók. A trigger aktiválásához vagy letiltásához ALTER (DDL) utasítást kell megadni ahhoz a triggerhez, amely letiltja vagy engedélyezi.

Az alábbiakban a kiváltók engedélyezésének / letiltásának szintaxisa található.

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

Szintaxis Magyarázat:

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

Összegzés

Ebben a fejezetben megismertük a PL / SQL kiváltó okait és azok előnyeit. Megtanultuk a különböző osztályozásokat is, és megbeszéltük a trigger és az COMPOUND trigger helyett.