Výukový program Oracle PL / SQL Trigger: Místo toho, Sloučenina (Příklad)

Obsah:

Anonim

Co je Trigger v PL / SQL?

TRIGGERS jsou uložené programy, které jsou automaticky spouštěny strojem Oracle, když jsou v tabulce provedeny příkazy DML, jako je vložení, aktualizace, odstranění, nebo dojde k nějakým událostem. Kód, který má být vyloučen v případě spouštěče, lze definovat podle požadavku. Můžete si vybrat událost, při které je třeba aktivovat spouštěč, a načasování provedení. Účelem spouštěče je udržovat integritu informací v databázi.

V tomto výukovém programu se naučíte

  • Výhody spouštěčů
  • Typy spouštěčů v systému Oracle
  • Jak vytvořit spouštěč
  • : NOVINKA a: STARÁ doložka
  • MÍSTO Spouštění
  • Složený spouštěč

Výhody spouštěčů

Následují výhody spouštěčů.

  • Automatické generování některých odvozených hodnot sloupců
  • Prosazování referenční integrity
  • Protokolování událostí a ukládání informací o přístupu k tabulce
  • Auditování
  • Synchronní replikace tabulek
  • Ukládání bezpečnostních oprávnění
  • Zabránění neplatným transakcím

Typy spouštěčů v systému Oracle

Spouštěče lze klasifikovat na základě následujících parametrů.

  • Klasifikace na základě načasování
    • BEFORE Trigger: Spustí se dříve, než došlo k zadané události.
    • PO SPUŠTĚNÍ: Spustí se po výskytu zadané události.
    • NAMÍSTO Spouštěče: Speciální typ. Dozvíte se více o dalších tématech. (pouze pro DML)
  • Klasifikace podle úrovně
    • Spuštění úrovně STATEMENT: Spustí se jednou pro zadaný příkaz události.
    • Spouštěč úrovně ROW: Spustí se pro každý záznam, který byl ovlivněn v zadané události. (pouze pro DML)
  • Klasifikace na základě události
    • Spouštěč DML: Spustí se, když je zadána událost DML (INSERT / UPDATE / DELETE)
    • Spouštěč DDL: Spustí se, když je zadána událost DDL (CREATE / ALTER)
    • Spouštěč DATABÁZE: Spustí se, když je zadána událost databáze (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Každý spouštěč je tedy kombinací výše uvedených parametrů.

Jak vytvořit spouštěč

Níže je uvedena syntaxe pro vytvoření spouštěče.

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

Vysvětlení syntaxe:

  • Výše uvedená syntaxe ukazuje různé volitelné příkazy, které jsou přítomny při vytváření spouštěče.
  • PŘED / PO určí časování událostí.
  • INSERT / UPDATE / LOGON / CREATE / atd. určí událost, pro kterou je třeba aktivovat spouštěč.
  • Klauzule ON specifikuje, pro který objekt je výše uvedená událost platná. Například to bude název tabulky, na kterém může dojít k události DML v případě DML Trigger.
  • Příkaz "PRO KAŽDÝ ŘÁD" určí spouštěč úrovně ŘÁDEK.
  • KDY klauzule bude specifikovat další podmínku, ve které se musí aktivovat spouštěč.
  • Část deklarace, část provádění, část zpracování výjimek je stejná jako část ostatních bloků PL / SQL. Část prohlášení a část pro zpracování výjimek jsou volitelné.

: NOVINKA a: STARÁ doložka

Ve spouštěči na úrovni řádků se spouštěč spustí pro každý související řádek. Někdy je nutné znát hodnotu před a po prohlášení DML.

Společnost Oracle poskytla ve spouštěči na úrovni RECORD dvě klauzule, které tyto hodnoty uchovávají. Těmito klauzulemi můžeme odkázat na staré a nové hodnoty uvnitř těla spouštěče.

  • : NOVINKA - Obsahuje novou hodnotu pro sloupce základní tabulky / pohledu během spouštění
  • : STARÉ - Obsahuje starou hodnotu sloupců základní tabulky / pohledu během spouštění

Tato klauzule by měla být použita na základě události DML. Níže uvedená tabulka určí, která klauzule je platná pro který příkaz DML (INSERT / UPDATE / DELETE).

VLOŽIT AKTUALIZACE VYMAZAT
:NOVÝ PLATNÝ PLATNÝ NEPLATNÝ. V případě odstranění není žádná nová hodnota.
:STARÝ NEPLATNÝ. Ve vloženém případě není žádná stará hodnota PLATNÝ PLATNÝ

MÍSTO Spouštění

„INSTEAD OF trigger“ je speciální typ spouštěče. Používá se pouze ve spouštěčích DML. Používá se, když v komplexním zobrazení dojde k jakékoli události DML.

Zvažte příklad, ve kterém je pohled vytvořen ze 3 základních tabulek. Když je v tomto zobrazení vydána nějaká událost DML, stane se neplatná, protože data jsou převzata ze 3 různých tabulek. Takže v tomto INSTEAD OF se používá spoušť. Spouštěč INSTEAD OF se používá k přímé úpravě základních tabulek namísto úpravy pohledu pro danou událost.

Příklad 1 : V tomto příkladu vytvoříme komplexní pohled ze dvou základních tabulek.

  • Table_1 je emp tabulka a
  • Tabulka_2 je tabulka oddělení.

Pak se podíváme, jak se spoušť INSTEAD OF používá k vydání UPDATE příkazu podrobného umístění v tomto komplexním zobrazení. Také se podíváme, jak: NEW a: OLD jsou užitečné při spouštění.

  • Krok 1: Vytvoření tabulky „emp“ a „odd“ s příslušnými sloupci
  • Krok 2: Naplnění tabulky vzorovými hodnotami
  • Krok 3: Vytvoření zobrazení pro výše vytvořenou tabulku
  • Krok 4: Aktualizace pohledu před spouštěčem místo spouštění
  • Krok 5: Vytvoření spouštěče místo
  • Krok 6: Aktualizace pohledu po místo spouštěče

Krok 1) Vytvoření tabulky „emp“ a „odd“ s příslušnými sloupci

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

Vysvětlení kódu

  • Řádek kódu 1-7 : Vytvoření tabulky „emp“.
  • Řádek kódu 8-12 : Vytvoření „odd“ tabulky.

Výstup

Tabulka vytvořena

Krok 2) Nyní, protože jsme vytvořili tabulku, naplníme tuto tabulku ukázkovými hodnotami a vytvořením zobrazení pro výše uvedené tabulky. 

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

Vysvětlení kódu

  • Řádek kódu 13-19 : Vkládání dat do tabulky „odd“.
  • Řádek kódu 20-26: Vkládání dat do tabulky „emp“.

Výstup

Procedura PL / SQL dokončena

Krok 3) Vytvoření pohledu pro výše vytvořenou tabulku. 

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;

Vysvětlení kódu

  • Řádek kódu 27-32: Vytvoření pohledu 'guru99_emp_view'.
  • Řádek kódu 33: Dotazování guru99_emp_view.

Výstup

Pohled byl vytvořen

JMÉNO ZAMĚSTNANCE DEPT_NAME UMÍSTĚNÍ
ZZZ HR USA
YYY ODBYT Spojené království
XXX FINANČNÍ JAPONSKO

Krok 4) Aktualizace pohledu před namísto spouštěče.

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

Vysvětlení kódu

  • Řádek kódu 34-38: Aktualizujte umístění „XXX“ na „FRANCE“. Vyvolalo to výjimku, protože příkazy DML nejsou v komplexním zobrazení povoleny.

Výstup

ORA-01779: nelze upravit sloupec, který se mapuje na tabulku, která není chráněna klíčem

ORA-06512: na řádku 2

Krok 5) Abychom se vyhnuli chybovým hlášením během aktualizace zobrazení v předchozím kroku, použijeme v tomto kroku „místo spouštěče“.

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

Vysvětlení kódu

  • Řádek kódu 39: Vytvoření OKAMŽITÉHO spouštěče pro událost „UPDATE“ v pohledu „guru99_emp_view“ na úrovni ROW. Obsahuje prohlášení o aktualizaci k aktualizaci umístění v základní tabulce 'odd'.
  • Řádek kódu 44: Příkaz Update používá k vyhledání hodnoty sloupců před a po aktualizaci znaky „: NEW“ a „: OLD“.

Výstup

Spouštěč vytvořen

Krok 6) Aktualizace pohledu po místo spouštěče. Nyní chyba nepřijde, protože operaci aktualizace tohoto komplexního pohledu zpracuje „místo spouštěče“. A až bude kód spuštěn, místo zaměstnance XXX bude aktualizováno na „Francie“ z „Japonska“.

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

Vysvětlení kódu:

  • Řádek kódu 49-53: Aktualizace umístění „XXX“ na „FRANCIE“. Je úspěšný, protože aktivační událost 'INSTEAD OF' zastavila aktuální příkaz aktualizace v zobrazení a provedla aktualizaci základní tabulky.
  • Řádek kódu 55: Ověření aktualizovaného záznamu.

Výstup:

Procedura PL / SQL byla úspěšně dokončena

JMÉNO ZAMĚSTNANCE DEPT_NAME UMÍSTĚNÍ
ZZZ HR USA
YYY ODBYT Spojené království
XXX FINANČNÍ FRANCIE

Složený spouštěč

Složená aktivační událost je aktivační událost, která umožňuje určit akce pro každý ze čtyř časových bodů v těle jediné aktivační události. Čtyři různé časové body, které podporuje, jsou uvedeny níže.

  • PŘED PROHLÁŠENÍM - úroveň
  • PŘED ŘADOU - úroveň
  • PO ŘADĚ - úroveň
  • PO PROHLÁŠENÍ - úroveň

Poskytuje možnost kombinovat akce pro různé načasování do stejného spouštěče.

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;

Vysvětlení syntaxe:

  • Výše uvedená syntaxe ukazuje vytvoření spouštěče „COMPOUND“.
  • Deklarativní část je společná pro všechny spouštěcí bloky v těle spouště.
  • Tyto 4 časovací bloky mohou být v libovolném pořadí. Není povinné mít všechny tyto 4 časové bloky. Můžeme vytvořit spouštěč SLOŽENÍ pouze pro požadovaná časování.

Příklad 1 : V tomto příkladu vytvoříme spouštěč pro automatické vyplnění sloupce plat s výchozí hodnotou 5000.

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;

Vysvětlení kódu:

  • Řádek kódu 2-10 : Vytvoření složeného spouštěče. Je vytvořen pro načasování PŘED ŘADOU na úrovni k naplnění platu výchozí hodnotou 5000. Tím se plat před vložením záznamu do tabulky změní na výchozí hodnotu „5000“.
  • Řádek kódu 11-14 : Vložte záznam do tabulky „emp“.
  • Řádek kódu 16 : Ověření vloženého záznamu.

Výstup:

Spouštěč vytvořen

Procedura PL / SQL byla úspěšně dokončena.

EMP_NAME EMP_NO PLATBA MANAŽER DEPT_NO
CCC 1004 5000 AAA 30

Povolení a zakázání spouštěčů

Spouštěče lze povolit nebo zakázat. Chcete-li aktivovat nebo deaktivovat aktivační událost, je třeba zadat příkaz ALTER (DDL) pro aktivační událost, která ji deaktivuje nebo povolí.

Níže je uvedena syntaxe pro povolení / zakázání spouštěčů.

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

Vysvětlení syntaxe:

  • První syntaxe ukazuje, jak povolit / zakázat jeden spouštěč.
  • Druhý příkaz ukazuje, jak povolit / zakázat všechny spouštěče v konkrétní tabulce.

souhrn

V této kapitole jsme se dozvěděli o spouštěčích PL / SQL a jejich výhodách. Také jsme se naučili různé klasifikace a diskutovali INSTEAD OF trigger a COMPOUND trigger.