Oracle PL / SQL Trigger -opetusohjelma: Yhdisteen sijaan (esimerkki)

Sisällysluettelo:

Anonim

Mikä on laukaisu PL / SQL: ssä?

TRIGGERS ovat tallennettuja ohjelmia, jotka Oracle-moottori käynnistää automaattisesti, kun taulukossa suoritetaan DML- lauseita, kuten lisäys , päivitys, poisto tai tapahtumia. Liipaisun yhteydessä suoritettava koodi voidaan määrittää vaatimuksen mukaisesti. Voit valita tapahtuman, johon liipaisin on käynnistettävä, ja suorituksen ajoituksen. Laukaisun tarkoituksena on ylläpitää tietokannan tietojen eheyttä.

Tässä opetusohjelmassa opit-

  • Triggerien edut
  • Oracle-laukaisintyypit
  • Kuinka luoda laukaisin
  • : UUSI ja: VANHA lauseke
  • Liipaisun sijasta
  • Yhdisteen liipaisin

Triggerien edut

Seuraavassa on laukaisijoiden edut.

  • Jotkin johdetut sarakearvot luodaan automaattisesti
  • Viitteiden eheyden täytäntöönpano
  • Tapahtumien kirjaaminen ja tietojen tallentaminen pöydän käyttöoikeuksiin
  • Tarkastus
  • Taulukoiden synkroninen replikointi
  • Turvalupien asettaminen
  • Virheellisten tapahtumien estäminen

Oracle-laukaisintyypit

Triggerit voidaan luokitella seuraavien parametrien perusteella.

  • Luokittelu ajoituksen perusteella
    • ENNEN liipaisinta: Se käynnistyy ennen määritetyn tapahtuman tapahtumista.
    • Laukaisun jälkeen: Se käynnistyy määritetyn tapahtuman jälkeen.
    • Liipaisimen sijasta: Erityistyyppi. Opit lisää muista aiheista. (vain DML)
  • Luokittelu tason perusteella
    • STATEMENT-tason liipaisin: Se käynnistää kerran määritettyä tapahtumalausetta varten.
    • RIVitasoinen liipaisin: Se käynnistyy jokaisen tietueen kohdalla, johon määritetty tapahtuma vaikuttaa. (vain DML)
  • Luokittelu tapahtuman perusteella
    • DML-liipaisin: Se käynnistyy, kun DML-tapahtuma on määritetty (INSERT / UPDATE / DELETE)
    • DDL-liipaisin: Se käynnistyy, kun DDL-tapahtuma on määritetty (CREATE / ALTER)
    • DATABASE Trigger: Se käynnistyy, kun tietokantatapahtuma on määritetty (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Joten jokainen laukaisu on yllä olevien parametrien yhdistelmä.

Kuinka luoda laukaisin

Alla on liipaisimen luomisen syntaksi.

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

Syntaksi Selitys:

  • Yllä oleva syntaksi näyttää erilaiset valinnaiset lauseet, joita läsnä on käynnistimen luomisessa.
  • ENNEN / JÄLKEEN määrittää tapahtuman ajoitukset.
  • INSERT / UPDATE / LOGON / CREATE / jne. määrittää tapahtuman, jonka laukaisu on käynnistettävä.
  • ON-lauseke määrittää, mille objektille yllä mainittu tapahtuma on kelvollinen. Esimerkiksi tämä on taulukon nimi, jolla DML-tapahtuma voi esiintyä DML-liipaisimen tapauksessa.
  • Komento "JOKA RIVI" määrittää RIV-tason liipaisimen.
  • WHEN-lausekkeessa määritetään lisäehto, jossa laukaisimen on aktivoitava.
  • Ilmoitusosa, suoritusosa, poikkeuksen käsittelyosa on sama kuin muiden PL / SQL-lohkojen. Ilmoitusosa ja poikkeusten käsittelyosa ovat valinnaisia.

: UUSI ja: VANHA lauseke

Rivitason liipaisimessa liipaisin käynnistyy jokaiselle liittyvälle riville. Ja joskus vaaditaan tieto arvosta ennen DML-käskyä ja sen jälkeen.

Oracle on toimittanut kaksi lauseketta RECORD-tason liipaisimeen näiden arvojen pitämiseksi. Voimme käyttää näitä lausekkeita viittaamaan vanhoihin ja uusiin arvoihin liipaisimen rungon sisällä.

  • : UUSI - Se pitää uuden arvon perustaulukon / näkymän sarakkeissa liipaisun suorituksen aikana
  • : VANHA - Siinä on perustaulukon / näkymän sarakkeiden vanha arvo liipaisun suorituksen aikana

Tätä lauseketta tulisi käyttää DML-tapahtuman perusteella. Alla olevassa taulukossa määritetään, mikä lauseke on voimassa mille DML-käskylle (INSERT / UPDATE / DELETE).

LISÄÄ PÄIVITTÄÄ POISTAA
:UUSI PÄTEVÄ PÄTEVÄ VOIMATON. Poistotapauksessa ei ole uutta arvoa.
:VANHA VIRHEELLINEN. Lisätapauksessa ei ole vanhaa arvoa PÄTEVÄ PÄTEVÄ

Liipaisun sijasta

"INSTEAD OF trigger" on erityinen liipaisintyyppi. Sitä käytetään vain DML-laukaisimissa. Sitä käytetään, kun jokin DML-tapahtuma tapahtuu monimutkaisessa näkymässä.

Tarkastellaan esimerkkiä, jossa näkymä tehdään kolmesta perustaulusta. Kun mitä tahansa DML-tapahtumaa annetaan tämän näkymän yli, se mitätöityy, koska tiedot otetaan kolmesta eri taulukosta. Joten tässä käytetään liipaisimen INSTEAD. INSTEAD OF -liipaisinta käytetään muokkaamaan perustaulukoita suoraan sen sijaan, että muokkaat tietyn tapahtuman näkymää.

Esimerkki 1 : Tässä esimerkissä aiomme luoda monimutkaisen näkymän kahdesta pohjataulukosta.

  • Taulukko_1 on emp taulukko ja
  • Taulukko_2 on osastotaulukko.

Sitten aiomme nähdä, kuinka INSTEAD OF -liipaisinta käytetään päivittämään sijaintitiedot käsky tässä monimutkaisessa näkymässä. Aiomme myös nähdä, kuinka: NEW ja: OLD on hyödyllinen laukaisimissa.

  • Vaihe 1: Luodaan taulukot 'emp' ja 'dept' sopivilla sarakkeilla
  • Vaihe 2: Taulukon täyttäminen näytearvoilla
  • Vaihe 3: Luodaan näkymä yllä luotua taulukkoa varten
  • Vaihe 4: Näkymän päivitys ennen liipaisun sijasta
  • Vaihe 5: Liipaisimen sijasta luominen
  • Vaihe 6: Näkymän päivitys liipaisun sijasta

Vaihe 1) Luodaan taulukot 'emp' ja 'dept' sopivilla sarakkeilla

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

Koodin selitys

  • Koodirivi 1-7 : Taulukon 'emp' luominen.
  • Koodirivi 8-12 : Taulukon osaston luominen.

Tuotos

Taulukko luotu

Vaihe 2) Nyt kun olemme luoneet taulukon, täytetään tämä taulukko näyte-arvoilla ja Luoma näkymiä yllä oleville taulukoille. 

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

Koodin selitys

  • Koodirivi 13-19 : Lisätään tietoja osastotaulukkoon.
  • Koodirivi 20-26: Lisätään tietoja emp-taulukkoon.

Tuotos

PL / SQL-menettely on suoritettu

Vaihe 3) Näkymän luominen yllä luotua taulukkoa varten. 

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;

Koodin selitys

  • Koodirivi 27-32: Guru99_emp_view-näkymän luominen.
  • Koodirivi 33: Kysely guru99_emp_view.

Tuotos

Näkymä luotu

TYÖNTEKIJÄN NIMI DEPT_NAME SIJAINTI
ZZZ HR USA
YYY MYYNTI Iso-Britannia
XXX RAHOITUS JAPANI

Vaihe 4) Näkymän päivitys ennen liipaisun sijasta.

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

Koodin selitys

  • Koodirivi 34-38: Päivitä "XXX" -sijainniksi "FRANCE". Se nosti poikkeuksen, koska DML-lausunnot eivät ole sallittuja monimutkaisessa näkymässä.

Tuotos

ORA-01779: ei voi muokata saraketta, joka kartoitetaan avainta säilyttämättömään taulukkoon

ORA-06512: rivillä 2

Vaihe 5) Välttääksemme virheitä edellisen vaiheen näkymän päivityksen aikana, tässä vaiheessa aiomme käyttää "laukaisimen sijaan".

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

Koodin selitys

  • Koodirivi 39: INSTEAD OF -liipaisimen luominen UPDATE-tapahtumalle guru99_emp_view-näkymässä ROW-tasolla. Se sisältää päivityslausekkeen, jolla päivitetään sijainti perustaulukossa "osasto".
  • Koodirivi 44: Päivityslauseke käyttää ': NEW' ja ': OLD' etsimään sarakkeiden arvon ennen päivitystä ja sen jälkeen.

Tuotos

Liipaisin luotu

Vaihe 6) Näkymän päivitys liipaisun sijasta. Nyt virhe ei tule, koska "liipaisimen sijaan" hoitaa tämän monimutkaisen näkymän päivitystoiminnon. Ja kun koodi on suoritettu, työntekijän XXX sijainti päivitetään "Ranskalle" Japanista.

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

Koodin selitys:

  • Koodirivi 49-53: XXX : n sijainnin päivitys FRANCE: ksi. Se on onnistunut, koska INSTEAD OF -liipaisin on pysäyttänyt varsinaisen päivityslausunnon näkymässä ja suorittanut perustaulukon päivityksen.
  • Koodirivi 55: Päivitetyn tietueen tarkistaminen.

Tuotos:

PL / SQL-menettely on suoritettu onnistuneesti

TYÖNTEKIJÄN NIMI DEPT_NAME SIJAINTI
ZZZ HR USA
YYY MYYNTI Iso-Britannia
XXX RAHOITUS RANSKA

Yhdisteen liipaisin

Yhdistetty liipaisin on liipaisin, jonka avulla voit määrittää toiminnot kullekin yhdelle liipaisimen rungon neljälle ajoituspisteelle. Sen tukemat neljä erilaista ajoituspistettä ovat alla.

  • ENNEN LAUSUMAA - taso
  • ENNEN RIVIä - taso
  • RIVIN JÄLKEEN - taso
  • JULKISUUDEN JÄLKEEN - taso

Se tarjoaa mahdollisuuden yhdistää eri ajoituksen toiminnot samaan liipaisimeen.

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;

Syntaksi Selitys:

  • Yllä oleva syntakse osoittaa 'COMPOUND' -liipaisimen luomisen.
  • Deklaratiivinen osa on yhteinen kaikille laukaisurungon suorituslohkoille.
  • Nämä 4 ajoituslohkoa voivat olla missä tahansa järjestyksessä. Kaikkia näitä 4 ajoituslohkoa ei ole pakollinen. Voimme luoda YHDISTETTY-liipaisimen vain tarvittaville ajoituksille.

Esimerkki 1 : Tässä esimerkissä aiomme luoda laukaisimen, jonka avulla palkkasarake täytetään automaattisesti oletusarvolla 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;

Koodin selitys:

  • Koodirivi 2-10 : Yhdistetyn liipaisimen luominen. Se on luotu ajoitukseen ENNEN RIVI-tasoa täyttämään palkan oletusarvo 5000. Tämä muuttaa palkan oletusarvoksi '5000' ennen tietueen lisäämistä taulukkoon.
  • Koodirivi 11-14 : Lisää tietue emp -taulukkoon.
  • Koodirivi 16 : Lisätyn tietueen tarkistus.

Tuotos:

Trigger luotu

PL / SQL-menettely on suoritettu onnistuneesti.

EMP_NAME EMP_NO PALKKA JOHTAJA DEPT_NO
CCC 1004 5000 AAA 30

Triggerien ottaminen käyttöön ja poistaminen käytöstä

Triggerit voidaan ottaa käyttöön tai poistaa käytöstä. Liipaisimen käyttöön ottamiseksi tai poistamiseksi käytöstä on annettava ALTER (DDL) -lauseke liipaisimelle, joka poistaa sen käytöstä tai ottaa sen käyttöön.

Alla on syntaksi liipaisimien sallimiseksi / käytöstä poistamiseksi.

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

Syntaksi Selitys:

  • Ensimmäisessä syntaksissa kerrotaan, kuinka yksittäinen liipaisin otetaan käyttöön / pois käytöstä.
  • Toinen lausunto näyttää, kuinka kaikki tietyn taulukon laukaisimet otetaan käyttöön / pois käytöstä.

Yhteenveto

Tässä luvussa olemme oppineet PL / SQL-laukaisimista ja niiden eduista. Olemme myös oppineet eri luokitukset ja keskustelleet liipaisimen ja yhdisteen liipaisimen sijasta.