„Oracle PL / SQL Trigger“ mokymo programa: vietoj junginio (pavyzdys)

Turinys:

Anonim

Kas yra suaktyvinimas PL / SQL?

TRIGGERS yra saugomos programos, kurias „Oracle“ variklis paleidžia automatiškai, kai ant stalo vykdomi DML teiginiai, pvz., Įterpimas, atnaujinimas, ištrynimas arba įvyksta tam tikrų įvykių. Kodas, kurį reikia įvykdyti suveikus, gali būti apibrėžtas pagal reikalavimą. Galite pasirinkti įvykį, per kurį reikia paleisti gaiduką, ir vykdymo laiką. Trigerio tikslas yra išlaikyti duomenų vientisumą duomenų bazėje.

Šioje pamokoje sužinosite

  • Trigerių nauda
  • Trigerių tipai „Oracle“
  • Kaip sukurti aktyviklį
  • : NAUJA ir: SENA sąlyga
  • Trigerio vietoj
  • Sudėtinis paleidiklis

Trigerių nauda

Toliau pateikiami veiksnių pranašumai.

  • Automatiškai generuojamos kai kurios išvestinės stulpelių vertės
  • Referencinio vientisumo vykdymas
  • Įvykių registravimas ir informacijos, susijusios su prieiga prie lentelės, saugojimas
  • Auditas
  • Sinchroninis lentelių replikavimas
  • Saugos įgaliojimų nustatymas
  • Neleistinų operacijų prevencija

Trigerių tipai „Oracle“

Trigerius galima klasifikuoti pagal šiuos parametrus.

  • Klasifikacija pagal laiką
    • PRIEŠ trigerį: jis suveikia dar neįvykus nurodytam įvykiui.
    • PO Paleidiklis: jis suveikia įvykus nurodytam įvykiui.
    • Trigerio vietoj: specialus tipas. Sužinosite daugiau apie kitas temas. (tik DML)
  • Klasifikacija pagal lygį
    • STATEMENT lygio aktyviklis: jis suaktyvinamas vieną kartą nurodytam įvykio sakiniui.
    • Eilutės aktyviklis: jis suaktyvinamas kiekvienam įrašui, kuris paveiktas nurodytame įvykyje. (tik DML)
  • Klasifikacija pagal įvykį
    • DML aktyviklis: jis suveikia, kai nurodomas DML įvykis (INSERT / UPDATE / DELETE)
    • DDL aktyviklis: jis suveikia, kai nurodomas DDL įvykis (CREATE / ALTER)
    • DUOMENŲ BAZĖS aktyviklis: jis suaktyvinamas, kai nurodomas duomenų bazės įvykis (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Taigi kiekvienas aktyviklis yra aukščiau nurodytų parametrų derinys.

Kaip sukurti aktyviklį

Žemiau pateikiama aktyviklio sukūrimo sintaksė.

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

Sintaksės paaiškinimas:

  • Pirmiau pateiktoje sintaksėje rodomi skirtingi pasirenkami teiginiai, esantys kuriant aktyviklį.
  • BEFORE / AFTER nurodys įvykio laiką.
  • INSERT / UPDATE / LOGON / CREATE / tt. nurodys įvykį, kuriam reikia paleisti gaiduką.
  • Sąlyga ON nurodys, kuriame objekte galioja minėtas įvykis. Pvz., Tai bus lentelės pavadinimas, kuriame gali įvykti DML įvykis DML suaktyvinimo atveju.
  • Komanda „KIEKVIENAI EILE“ nurodys eilutės lygio aktyviklį.
  • KADA sąlyga nurodys papildomą sąlygą, kai reikia suveikti gaiduką.
  • Deklaracijos dalis, vykdymo dalis, išimčių tvarkymo dalis yra tokia pati kaip kitų PL / SQL blokų. Deklaracijos dalis ir išimčių tvarkymo dalys yra neprivalomos.

: NAUJA ir: SENA sąlyga

Eilutės lygio aktyviklyje suveikia kiekvienos susijusios eilutės aktyviklis. Kartais reikalaujama žinoti vertę prieš ir po DML sakinio.

„Oracle“ įrašė laukelį RECORD lygio, kad būtų išlaikytos šios vertės. Šias frazes galime naudoti norėdami nurodyti senąsias ir naujas reikšmes trigerio korpuse.

  • : NAUJAS - trigerio vykdymo metu ji turi naują pagrindinės lentelės / rodinio stulpelių vertę
  • : SENAS - trigerio vykdymo metu ji turi seną pagrindinės lentelės / rodinio stulpelių vertę

Ši sąlyga turėtų būti naudojama remiantis DML įvykiu. Žemiau esančioje lentelėje bus nurodyta, kuris sakinys galioja kuriam DML sakiniui (INSERT / UPDATE / DELETE).

ĮDĖTI ATNAUJINTI IŠTRINTI
: NAUJIENA GALIOJA GALIOJA NETEISINGA. Ištrynimo byloje nėra naujos vertės.
: SENAS NETEISINGA. Įterptinėje raidėje nėra senos vertės GALIOJA GALIOJA

Trigerio vietoj

„INSTEAD OF trigger“ yra specialus aktyviklio tipas. Jis naudojamas tik sukeliant DML. Jis naudojamas, kai bet koks DML įvykis įvyks sudėtingame vaizde.

Apsvarstykite pavyzdį, kuriame vaizdas yra sudarytas iš 3 pagrindinių lentelių. Kai per šį rodinį bus išleistas bet koks DML įvykis, jis negalios, nes duomenys paimti iš 3 skirtingų lentelių. Taigi šiame INSTEAD OF trigeris yra naudojamas. Trigeris INSTEAD OF naudojamas tiesiogiai modifikuoti pagrindines lenteles, o ne modifikuoti duoto įvykio rodinį.

1 pavyzdys : Šiame pavyzdyje mes sukursime sudėtingą vaizdą iš dviejų pagrindinių lentelių.

  • Lentelė_1 yra emp lentelė ir
  • Lentelė_2 yra skyriaus lentelė.

Tada mes pamatysime, kaip aktyviklis INSTEAD OF naudojamas atnaujinant išsamią buvimo vietos ataskaitą šiame sudėtingame rodinyje. Taip pat norėsime sužinoti, kaip: NAUJAS ir: SENAS yra naudingas trigeriuose.

  • 1 veiksmas: sukurkite lenteles „emp“ ir „dept“ su atitinkamais stulpeliais
  • 2 veiksmas: užpildykite lentelę su pavyzdžių reikšmėmis
  • 3 žingsnis: aukščiau sukurtos lentelės rodinio kūrimas
  • 4 žingsnis: atnaujinkite rodinį prieš aktyviklį
  • 5 veiksmas: vietoj aktyviklio sukūrimas
  • 6 veiksmas: atnaujinkite rodinį po aktyviklio

1 žingsnis) Sukurkite lenteles „emp“ ir „dept“ su atitinkamais stulpeliais

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

Kodo paaiškinimas

  • 1-7 kodo eilutė : lentelės „emp“ sukūrimas.
  • Kodo eilutė 8–12 : „Dept“ lentelės kūrimas.

Rezultatas

Sukurta lentelė

2 žingsnis) Dabar, kai sukūrėme lentelę, šioje lentelėje užpildysime aukščiau pateiktų lentelių pavyzdžių reikšmes ir rodinių kūrimą. 

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

Kodo paaiškinimas

  • Kodo eilutė 13-19 : Duomenų įterpimas į „dept“ lentelę.
  • Kodo eilutė 20–26: Duomenų įterpimas į lentelę „emp“.

Rezultatas

PL / SQL procedūra baigta

3 žingsnis. Sukurkite aukščiau sukurtos lentelės rodinį. 

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;

Kodo paaiškinimas

  • Kodo eilutė 27-32: „guru99_emp_view“ rodinio sukūrimas.
  • 33 kodo eilutė: užklausa „guru99_emp_view“.

Rezultatas

Rodinys sukurtas

DARBUOTOJO VARDAS DEPT_NAME VIETA
ZZZ HR JAV
YYY PARDAVIMAS JK
XXX FINANSINĖ Japonija

4 žingsnis. Atnaujinkite rodinį prieš paleidimą.

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

Kodo paaiškinimas

  • Kodo eilutė 34-38: atnaujinkite „XXX“ vietą į „FRANCE“. Ji pateikė išimtį, nes DML teiginiai neleidžiami sudėtingu požiūriu.

Rezultatas

ORA-01779: negalima modifikuoti stulpelio, kuris susiejamas su lentele, kurioje nėra rakto

ORA-06512: 2 eilutėje

5 žingsnis. Kad išvengtumėte klaidų atnaujinant ankstesnio veiksmo rodinį, šiame etape naudosime „vietoj aktyviklio“.

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

Kodo paaiškinimas

  • 39 kodo eilutė: „UPDATE“ įvykio INSTEAD OF sukūrimas rodinyje „guru99_emp_view“ ROW lygyje. Jame yra naujinimo sakinys, skirtas atnaujinti vietą pagrindinėje lentelėje „dept“.
  • Kodo 44 eilutė: Atnaujinimo sakinyje naudojami stulpelių prieš ir po atnaujinimo reikšmės surasti „: NEW“ ir „: OLD“.

Rezultatas

Sukurtas sukurtas

6 žingsnis. Vaizdo atnaujinimas po vietoj aktyviklio. Dabar klaida neatsiras, nes „vietoj paleidimo“ valdys šio sudėtingo rodinio atnaujinimo operaciją. Kai kodas bus įvykdytas, darbuotojo XXX vieta bus atnaujinta į „Prancūzija“ iš „Japonija“.

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

Kodo paaiškinimas:

  • Kodo eilutė 49–53: „XXX“ vietos atnaujinimas į „FRANCE“. Tai sėkminga, nes „INSTEAD OF“ aktyviklis sustabdė tikrąjį atnaujinimo sakinį rodinyje ir atliko pagrindinės lentelės atnaujinimą.
  • Kodo 55 eilutė: atnaujinto įrašo tikrinimas.

Išvestis:

PL / SQL procedūra sėkmingai baigta

DARBUOTOJO VARDAS DEPT_NAME VIETA
ZZZ HR JAV
YYY PARDAVIMAS JK
XXX FINANSINĖ PRANCŪZIJA

Sudėtinis paleidiklis

Sudėtinis aktyviklis yra aktyviklis, leidžiantis nurodyti veiksmus kiekvienam iš keturių laiko taškų, esančių viename aktyviklio korpuse. Keturi skirtingi laiko taškai, kuriuos jis palaiko, yra tokie, kaip nurodyta toliau.

  • PRIEŠ PAREIŠKIMĄ - lygis
  • PRIEŠ EILOW - lygis
  • PO EILĖS - lygis
  • PO PAREIŠKIMO - lygis

Tai suteikia galimybę sujungti skirtingo laiko veiksmus į tą patį veiksmą.

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;

Sintaksės paaiškinimas:

  • Aukščiau pateikta sintaksė rodo, kaip sukurtas „COMPOUND“ aktyviklis.
  • Deklaracinis skyrius yra bendras visam paleidimo bloko vykdymo lauke.
  • Šie 4 laiko blokai gali būti bet kokia seka. Nebūtina turėti visų šių 4 laiko blokų. Sudėtinį aktyviklį galime sukurti tik tam laikui, kuris reikalingas.

1 pavyzdys : Šiame pavyzdyje mes sukursime aktyviklį, kuris automatiškai užpildys atlyginimo stulpelį numatytąjąja verte 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;

Kodo paaiškinimas:

  • Kodo eilutė 2-10 : Sudėtinio aktyviklio sukūrimas. Jis sukurtas laikui PRIEŠ EILĖS lygį, kad algą būtų galima įrašyti pagal numatytąją 5000 vertę. Tai pakeis algą į numatytąją vertę „5000“ prieš įterpiant įrašą į lentelę.
  • Kodo eilutė 11–14 : Įterpkite įrašą į lentelę „emp“.
  • Kodo 16 eilutė : patikrinamas įterptas įrašas.

Išvestis:

Sukurta sukurta

PL / SQL procedūra sėkmingai baigta.

EMP_NAME EMP_NO ATLYGINIMAS VADOVAS DEPT_NO
CCC 1004 5000 AAA 30

Aktyviklių įjungimas ir išjungimas

Trigerius galima įjungti arba išjungti. Norint įjungti arba išjungti trigerį, reikia nurodyti ALTER (DDL) sakinį trigeriui, kuris jį išjungia arba įgalina.

Žemiau pateikiama suaktyviklių įjungimo / išjungimo sintaksė.

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

Sintaksės paaiškinimas:

  • Pirmoji sintaksė rodo, kaip įjungti / išjungti vieną trigerį.
  • Antrasis teiginys parodo, kaip įjungti / išjungti visus tam tikros lentelės aktyviklius.

Santrauka

Šiame skyriuje mes sužinojome apie PL / SQL trigerius ir jų pranašumus. Mes taip pat sužinojome skirtingas klasifikacijas ir aptarėme trigerio ir JUNGINIO trigerio vietą.