Kaj je sprožilec v PL / SQL?
TRIGGERS so shranjeni programi, ki jih Oracle engine samodejno sproži, ko se v tabeli izvedejo izjave DML, kot so vstavljanje, posodobitev, brisanje ali nekateri dogodki. Kodo, ki jo je treba pretvoriti v primeru sprožilca, lahko določimo v skladu z zahtevo. Izberete lahko dogodek, ob katerem naj se sproži sprožilec, in čas izvedbe. Namen sprožilca je ohraniti celovitost informacij v bazi podatkov.
V tej vadnici boste izvedeli-
- Prednosti sprožilcev
- Vrste sprožilcev v programu Oracle
- Kako ustvariti sprožilec
- : NOVO in: STARO Klavzulo
- Namesto sprožilca
- Sestavljeni sprožilec
Prednosti sprožilcev
Sledijo prednosti sprožilcev.
- Samodejno generiranje nekaterih izpeljanih vrednosti stolpcev
- Uveljavljanje referenčne integritete
- Beleženje dogodkov in shranjevanje informacij o dostopu do tabele
- Revizija
- Sinhrono kopiranje tabel
- Nalaganje varnostnih pooblastil
- Preprečevanje neveljavnih transakcij
Vrste sprožilcev v programu Oracle
Sprožilce lahko razvrstimo na podlagi naslednjih parametrov.
- Razvrstitev glede na čas
- PRED sprožilcem: sproži se, preden se zgodi določen dogodek.
- AFTER Trigger: Sproži se, ko se zgodi določen dogodek.
- Namesto sprožilca: posebna vrsta. Izvedeli boste več o nadaljnjih temah. (samo za DML)
- Razvrstitev glede na raven
- Sprožilec STATEMENT: sproži se enkrat za navedeni stavek dogodka.
- Sprožilec na ravni ROW: sproži se za vsak zapis, na katerega je vplival določeni dogodek. (samo za DML)
- Razvrstitev glede na dogodek
- Sprožilec DML: sproži se, ko je podan dogodek DML (INSERT / UPDATE / DELETE)
- Sprožilec DDL: sproži se, ko je podan dogodek DDL (CREATE / ALTER)
- Sprožilec DATABASE: Sproži se, ko je podan dogodek baze podatkov (LOGON / LOGOFF / STARTUP / SHUTDOWN)
Torej je vsak sprožilec kombinacija zgornjih parametrov.
Kako ustvariti sprožilec
Spodaj je sintaksa za ustvarjanje sprožilca.
CREATE [ OR REPLACE ] TRIGGER[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON [FOR EACH ROW][WHEN ]DECLARE BEGIN EXCEPTION END;
Pojasnilo sintakse:
- Zgornja sintaksa prikazuje različne neobvezne stavke, ki so prisotni pri ustvarjanju sprožilca.
- BEFORE / AFTER določi čas dogodka.
- INSERT / UPDATE / LOGON / CREATE / itd. bo določil dogodek, za katerega je treba sprožiti sprožilec.
- Klavzula ON bo določala, za kateri objekt je veljaven zgoraj omenjeni dogodek. Na primer, to bo ime tabele, na kateri se lahko zgodi dogodek DML v primeru sprožilca DML.
- Ukaz "FOR EACH ROW" bo določil sprožilec ravni ROW.
- Klavzula WHEN bo določila dodatni pogoj, v katerem mora sprožilec sprožiti.
- Del izjave, del izvedbe in del obdelave izjem so enaki kot pri drugih blokih PL / SQL. Del izjave in del, ki obravnava izjeme, sta neobvezna.
: NOVO in: STARO Klavzulo
V sprožilcu na ravni vrstice se sprožilec sproži za vsako povezano vrstico. Včasih je treba poznati vrednost pred stavkom DML in po njem.
Oracle je v sprožilcu na ravni RECORD zagotovil dva stavka, ki zadržujeta te vrednosti. Te določbe lahko uporabimo za sklicevanje na stare in nove vrednosti znotraj telesa sprožilca.
- : NOVO - Med izvajanjem sprožilca vsebuje novo vrednost za stolpce osnovne tabele / pogleda
- : OLD - Med izvajanjem sprožilca vsebuje staro vrednost stolpcev osnovne tabele / pogleda
Ta klavzula naj se uporablja na podlagi dogodka DML. Spodnja tabela bo navedla, kateri stavek velja za kateri stavek DML (INSERT / UPDATE / DELETE).
VSTAVI | NADGRADNJA | IZBRIŠI | |
: NOVO | VELJAVNO | VELJAVNO | NEMOČNO. V primeru brisanja ni nove vrednosti. |
:STAR | NEMOČNO. V vstavku ni stare vrednosti | VELJAVNO | VELJAVNO |
Namesto sprožilca
"NAMESTO sprožilca" je posebna vrsta sprožilca. Uporablja se samo v sprožilcih DML. Uporablja se, kadar se v kompleksnem pogledu zgodi kateri koli dogodek DML.
Razmislite o primeru, v katerem je pogled narejen iz 3 osnovnih tabel. Ko se v tem pogledu izda kateri koli dogodek DML, ta postane neveljaven, ker so podatki zajeti iz treh različnih tabel. Torej je v tem namesto uporabljen sprožilec. Sprožilec INSTEAD OF se uporablja za neposredno spreminjanje osnovnih tabel, namesto da bi spreminjal pogled za dani dogodek.
Primer 1 : V tem primeru bomo iz dveh osnovnih tabel ustvarili kompleksen pogled.
- Tabela_1 je prazna tabela in
- Tabela_2 je tabela oddelkov.
Nato bomo videli, kako se sprožilec INSTEAD OF uporablja za izdajo POSODOBITEV izjave o podrobnosti lokacije v tem zapletenem pogledu. Prav tako bomo videli, kako je: NEW in: OLD uporaben pri sprožilcih.
- 1. korak: Ustvarjanje tabele 'emp' in 'dept' z ustreznimi stolpci
- 2. korak: Popolnite tabelo z vzorčnimi vrednostmi
- 3. korak: Ustvarjanje pogleda za zgoraj ustvarjeno tabelo
- 4. korak: Posodobite pogled pred sprožilcem namesto sprožilca
- 5. korak: Ustvari sprožilec namesto sprožilca
- 6. korak: Posodobitev pogleda po sprožilcu namesto sprožilca
Korak 1) Ustvarjanje tabele 'emp' in 'dept' z ustreznimi stolpci
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));/
Razlaga kode
- Vrstica kode 1-7 : Ustvarjanje tabele 'emp'.
- Vrstica kode 8-12 : Ustvarjanje tabele 'dept'.
Izhod
Tabela ustvarjena
Korak 2) Zdaj, ko smo ustvarili tabelo, bomo to tabelo zapolnili z vzorčnimi vrednostmi in ustvarjanjem pogledov za zgornje tabele.
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;/
Razlaga kode
- Vrstica kode 13-19 : vstavljanje podatkov v tabelo 'dept'.
- Vrstica kode 20-26: vstavljanje podatkov v tabelo 'emp'.
Izhod
PL / SQL postopek zaključen
Korak 3) Ustvarjanje pogleda za zgoraj ustvarjeno tabelo.
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;
Razlaga kode
- Vrstica kode 27-32: Ustvarjanje pogleda 'guru99_emp_view'.
- Vrstica kode 33: Poizvedovanje guru99_emp_view.
Izhod
Pogled ustvarjen
IME ZAPOSLENEGA | DEPT_NAME | LOKACIJA |
ZZZ | HR | ZDA |
YYY | PRODAJA | Združeno kraljestvo |
XXX | FINANČNI | JAPONSKA |
Korak 4) Posodobitev pogleda pred sprožilcem namesto sprožilca.
BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;COMMIT;END;/
Razlaga kode
- Koda 34-38: Posodobite lokacijo "XXX" na "FRANCE". Vzpostavila je izjemo, ker stavki DML v kompleksnem pogledu niso dovoljeni.
Izhod
ORA-01779: ne more spremeniti stolpca, ki se preslika v tabelo, ki ni ohranjena s ključem
ORA-06512: v vrstici 2
5. korak) Da bi se izognili napaki med posodabljanjem pogleda v prejšnjem koraku, bomo v tem koraku uporabili "namesto sprožilca".
CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/
Razlaga kode
- Kodijska vrstica 39: Ustvari sprožilec INSTEAD OF za dogodek 'UPDATE' v pogledu 'guru99_emp_view' na ravni ROW. Vsebuje stavek posodobitve za posodobitev lokacije v osnovni tabeli 'dept'.
- Vrstica kode 44: Stavek posodobitve uporablja ': NEW' in ': OLD' za iskanje vrednosti stolpcev pred in po posodobitvi.
Izhod
Sprožilec ustvarjen
6. korak) Posodobitev pogleda po sprožilcu namesto sprožilca. Zdaj napaka ne bo prišla, saj bo "namesto sprožilca" obravnaval operacijo posodabljanja tega zapletenega pogleda. Ko bo koda izvedena, bo lokacija zaposlenega XXX posodobljena na "Francija" iz "Japonska".
BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;
Razlaga kode:
- Vrstica kode 49-53: Posodobitev lokacije "XXX" na "FRANCIJA". Uspešno je, ker je sprožilec 'INSTEAD OF' ustavil dejanski stavek posodobitve na ogled in izvedel posodobitev osnovne tabele.
- Vrstica kode 55: Preverjanje posodobljenega zapisa.
Izhod:
Postopek PL / SQL je uspešno zaključen
IME ZAPOSLENEGA | DEPT_NAME | LOKACIJA |
ZZZ | HR | ZDA |
YYY | PRODAJA | Združeno kraljestvo |
XXX | FINANČNI | FRANCIJA |
Sestavljeni sprožilec
Sprožilec Compound je sprožilec, ki vam omogoča, da določite dejanja za vsako od štirih časovnih točk v telesu posameznega sprožilca. Štiri različne časovne točke, ki jih podpira, so spodaj.
- PRED IZJAVO - raven
- PRED REDOM - nivo
- PO VRSTI - nivo
- PO IZJAVI - raven
Omogoča združitev dejanj za različne časovne intervale v isti sprožilec.
CREATE [ OR REPLACE ] TRIGGERFOR[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;
Pojasnilo sintakse:
- Zgornja sintaksa prikazuje ustvarjanje sprožilca 'COMPOUND'.
- Izjavni odsek je skupen za vse izvedbene bloke v telesu sprožilca.
- Ti 4 časovni bloki so lahko v katerem koli zaporedju. Ni obvezno imeti vseh teh 4 časovnih blokov. Sprožilec COMPOUND lahko ustvarimo samo za časovne okvire, ki so potrebni.
Primer 1 : V tem primeru bomo ustvarili sprožilec za samodejno izpolnjevanje stolpca plače s privzeto vrednostjo 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;
Razlaga kode:
- Koda vrstica 2-10 : Ustvarjanje sestavljenega sprožilca. Ustvarjen je za merjenje časa PRED RED-nivojem, da se plača zapolni s privzeto vrednostjo 5000. To bo spremenilo plačo na privzeto vrednost "5000", preden vstavite zapis v tabelo.
- Vrstica kode 11-14 : Vstavite zapis v tabelo 'emp'.
- Vrstica kode 16 : Preverjanje vstavljenega zapisa.
Izhod:
Sprožilec ustvarjen
PL / SQL postopek je uspešno zaključen.
EMP_NAME | EMP_NO | PLAČA | VODITELJ | DEPT_NO |
CCC | 1004 | 5000 | AAA | 30. |
Omogočanje in onemogočanje sprožilcev
Sprožilce lahko omogočite ali onemogočite. Če želite omogočiti ali onemogočiti sprožilec, morate za sprožilec podati stavek ALTER (DDL), ki ga onemogoči ali omogoči.
Spodaj je sintaksa omogočanja / onemogočanja sprožilcev.
ALTER TRIGGER[ENABLE|DISABLE];ALTER TABLE [ENABLE|DISABLE] ALL TRIGGERS;
Pojasnilo sintakse:
- Prva sintaksa prikazuje, kako omogočiti / onemogočiti posamezen sprožilec.
- Drugi stavek prikazuje, kako omogočiti / onemogočiti vse sprožilce v določeni tabeli.
Povzetek
V tem poglavju smo spoznali sprožilce PL / SQL in njihove prednosti. Spoznali smo tudi različne klasifikacije in razpravljali NAMESTO sprožilca in SPOJINSKEGA sprožilca.