Oracle PL/SQL-triggerin opetusohjelma: Yhdistelmän sijaan [esimerkki]

by: Fiona Brown Fiona Brown
Hours Päivitetty

Mikä on Trigger PL/SQL:ssä?

LIIPAISIMET ovat tallennettuja ohjelmia, jotka käynnistävät Oracle moottori automaattisesti, kun DML-lauseita, kuten lisääminen, päivitys, poistaminen, suoritetaan taulukossa tai tapahtumia tapahtuu. Liipaisutilanteessa suoritettava koodi voidaan määrittää vaatimuksen mukaan. Voit valita tapahtuman, jossa laukaisin käynnistetään, ja suoritusajankohdan. Liipaisimen tarkoitus on säilyttää tietokannan tietojen eheys.

Triggerien edut

Seuraavassa on triggerien edut.

  • Joitakin johdettuja sarakearvoja luodaan automaattisesti
  • Viittauksen eheyden varmistaminen
  • Tapahtumaloki ja tietojen tallentaminen pöytäkäyttöön
  • Tilintarkastus
  • Synctaulukoiden kroninen kopiointi
  • Turvallisuuslupien asettaminen
  • Virheellisten tapahtumien estäminen

Triggerien tyypit Oracle

Triggerit voidaan luokitella seuraavien parametrien perusteella.

  • Luokittelu perustuu ajoitus
  • BEFORE Trigger: Se käynnistyy ennen kuin määritetty tapahtuma on tapahtunut.
  • AFTER Trigger: Se käynnistyy määritetyn tapahtuman jälkeen.
  • Liipaisimen asemesta: Erikoistyyppi. Saat lisätietoja muista aiheista. (vain DML )
  • Luokittelu perustuu taso
  • STATEMENT-tason liipaisin: Se käynnistyy kerran määritetylle tapahtumakäskylle.
  • ROW-tason liipaisin: Se käynnistyy jokaisesta tietueesta, johon määritetty tapahtuma vaikuttaa. (vain DML)
  • Luokittelu perustuu tapahtuma
  • DML-triggeri: Se käynnistyy, kun DML-tapahtuma on määritetty (INSERT/UPDATE/DELETE)
  • DDL Trigger: 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)

Jokainen laukaisin on siis yllä olevien parametrien yhdistelmä.

Kuinka luoda triggeri

Alla on syntaksi liipaisimen luomiseen.

Luo triggeri

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

Syntaksiselitys:

  • Yllä oleva syntaksi näyttää erilaiset valinnaiset lauseet, jotka ovat läsnä liipaisimen luomisessa.
  • ENNEN/JÄLKEEN määrittää tapahtumien ajoitukset.
  • LISÄÄ/PÄIVITYS/KIRJAUDU/LUO/jne. määrittää tapahtuman, jota varten laukaisu on käynnistettävä.
  • ON-lause määrittää, missä objektissa yllä mainittu tapahtuma on voimassa. Tämä on esimerkiksi taulukon nimi, jossa DML-tapahtuma voi tapahtua DML-triggerin tapauksessa.
  • Komento "FOR EACH ROW" määrittää ROW-tason liipaisimen.
  • WHEN-lause määrittää lisäehdon, jossa liipaisimen on käynnistyttävä.
  • Ilmoitusosa, suoritusosa, poikkeuksenkäsittelyosa on sama kuin toisessa PL/SQL-lohkot. Ilmoitusosa ja poikkeuskäsittelyosa ovat valinnaisia.

AIHEESEEN LIITTYVÄT ARTIKKELIT

:NEW ja :OLD lauseke

Rivitason triggerissä triggeri käynnistyy kullekin liittyvälle riville. Ja joskus on tarpeen tietää arvo ennen ja jälkeen DML-käskyn.

Oracle on antanut kaksi lauseketta RECORD-tason triggerissä näiden arvojen säilyttämiseksi. Voimme käyttää näitä lauseita viittaamaan vanhoihin ja uusiin arvoihin liipaisukappaleen sisällä.

  • :NEW – Se sisältää uuden arvon perustaulukon/näkymän sarakkeille liipaisimen suorittamisen aikana
  • :OLD – Se säilyttää perustaulukon/näkymän sarakkeiden vanhan arvon liipaisun suorittamisen aikana

Tätä lauseketta tulee käyttää DML-tapahtuman perusteella. Alla oleva taulukko määrittää, mikä lauseke on kelvollinen millekin DML-käskylle (INSERT/UPDATE/DELETE).

INSERT PÄIVITYS POISTA
: UUSI PÄTEVÄ PÄTEVÄ VIRHEELLINEN. Poista-tapauksessa ei ole uutta arvoa.
:VANHA VIRHEELLINEN. Lisäkotelossa ei ole vanhaa arvoa PÄTEVÄ PÄTEVÄ

Liipaisimen SIJAAN

"SINTEAD OF Trigger" on liipaisimen erityinen tyyppi. Sitä käytetään vain DML-triggereissä. Sitä käytetään, kun mikä tahansa DML-tapahtuma tapahtuu monimutkaisessa näkymässä.

Harkitse esimerkkiä, jossa näkymä tehdään 3 perustaulukosta. Kun DML-tapahtuma julkaistaan ​​tämän näkymän aikana, se ei kelpaa, koska tiedot on otettu kolmesta eri taulukosta. Joten tässä INSTEEAD OF:n laukaisinta käytetään. INSTEAD OF -triggeriä käytetään perustaulukoiden muokkaamiseen suoraan sen sijaan, että muuttaisivat tietyn tapahtuman näkymää.

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

  • Taulukko_1 on tyhjä taulukko ja
  • Taulukko_2 on osastotaulukko.

Sitten aiomme nähdä, kuinka INSTEAD OF -triggeriä käytetään antamaan UPDATE sijainnin yksityiskohtainen lausunto tässä monimutkaisessa näkymässä. Aiomme myös nähdä, kuinka :NEW ja :OLD ovat hyödyllisiä triggereissä.

  • Vaihe 1: Taulukon 'emp' ja 'dept' luominen sopivilla sarakkeilla
  • Vaihe 2: Täytä taulukko näytearvoilla
  • Vaihe 3: Näkymän luominen yllä luodulle taulukolle
  • Vaihe 4: Päivitä näkymä ennen triggeriä
  • Vaihe 5: Liipaisimen sijasta luominen
  • Vaihe 6: Päivitä näkymä liipaisimen sijaan

Vaihe 1) Taulukon 'emp' ja 'dept' luominen sopivilla sarakkeilla

Liipaisimen SIJAAN

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.

ulostulo

Taulukko luotu

Vaihe 2) Nyt kun olemme luoneet taulukon, täytämme tämän taulukon esimerkkiarvoilla ja Näkymien luominen yllä oleville taulukoille.

Liipaisimen SIJAAN

BEGIN
INSERT INTO DEPT VALUES(10,‘HR’,‘USA’);
INSERT INTO DEPT VALUES(20,'SALES','UK’);
INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); 
COMMIT;
END;
/

BEGIN
INSERT 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: Tietojen lisääminen osastotaulukkoon.
  • Koodirivit 20-26: Tietojen lisääminen 'emp'-taulukkoon.

ulostulo

PL/SQL-menettely valmistunut

Vaihe 3) Näkymän luominen yllä luodulle taulukolle.

Liipaisimen SIJAAN

CREATE VIEW guru99_emp_view(
Employee_name:dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Koodin selitys

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

ulostulo

Näkymä luotu

TYÖNTEKIJÄN NIMI DEPT_NAME SIJAINTI
ZZZ HR Soumi
YYY MYYNTI UK
XXX TALOUDELLINEN JAPANI

Vaihe 4) Näkymän päivitys ennen laukaisinta.

Liipaisimen SIJAAN

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

Koodin selitys

  • Koodirivit 34-38: Päivitä kohteen "XXX" sijainniksi "RANSKA". Se esitti poikkeuksen, koska DML-lauseet eivät ole sallittuja monimutkaisessa näkymässä.

ulostulo

ORA-01779: ei voi muokata saraketta, joka vastaa taulukkoon, jota ei ole säilytetty avaimella

ORA-06512: rivillä 2

Vaihe 5)Välttääksemme virheen edellisen vaiheen päivitysnäkymän aikana, käytämme tässä vaiheessa "triggerin sijasta".

Liipaisimen SIJAAN

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Koodin selitys

  • Koodirivi 39: INSTEAD OF -triggerin luominen UPDATE-tapahtumalle guru99_emp_view-näkymässä ROW-tasolla. Se sisältää päivityskäskyn perustaulukon 'osaston' sijainnin päivittämiseksi.
  • Koodirivi 44: Päivityskäsky käyttää ':NEW' ja ': OLD' sarakkeiden arvon etsimiseen ennen päivitystä ja sen jälkeen.

ulostulo

Triggeri luotu

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

Liipaisimen SIJAAN

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; 
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Koodin selitys:

  • Koodirivit 49-53: "XXX":n sijainnin päivitys RANSKAksi. Se onnistuu, koska 'INSTEAD OF' -laukaisin on pysäyttänyt varsinaisen päivityskäskyn näkyvissä ja suorittanut perustaulukon päivityksen.
  • Koodirivi 55: Päivitetyn tietueen tarkistaminen.

lähtö:

PL/SQL-menettely suoritettu onnistuneesti

TYÖNTEKIJÄN NIMI DEPT_NAME SIJAINTI
ZZZ HR Soumi
YYY MYYNTI UK
XXX TALOUDELLINEN RANSKA

Yhdistelmälaukaisu

Yhdistelmälaukaisin on liipaisin, jonka avulla voit määrittää toiminnot kullekin neljästä ajastuspisteestä yhden liipaisimen rungossa. Sen tukemat neljä erilaista ajastuspistettä ovat alla.

  • ENNEN LAUSUNTOA – taso
  • ENNEN RIVAA – taso
  • RIVIN JÄLKEEN – taso
  • LAUSUNNON JÄLKEEN – taso

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

Yhdistelmälaukaisu

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
ON <name of underlying object>
<Declarative part>‭	‬
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

Syntaksiselitys:

  • Yllä oleva syntaksi näyttää 'COMPOUND'-laukaisimen luomisen.
  • Ilmoitusosio on yhteinen kaikille liipaisimen rungon suorituslohkoille.
  • Nämä 4 ajoituslohkoa voivat olla missä tahansa järjestyksessä. Kaikkien näiden neljän ajoituslohkon käyttäminen ei ole pakollista. Voimme luoda COMPOUND-laukaisimen vain vaadituille ajoituksille.

Esimerkki 1: Tässä esimerkissä aiomme luoda liipaisimen, joka täyttää palkkasarakkeen automaattisesti oletusarvolla 5000.

Yhdistelmälaukaisu

CREATE TRIGGER emp_trig 
FOR INSERT 
ON emp
COMPOUND TRIGGER 
BEFORE EACH ROW IS 
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); 
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Koodin selitys:

  • Koodirivi 2-10: Yhdistelmälaukaisimen luominen. Se luodaan ajoitusta ENNEN RIVI-tasoa varten, jotta palkka täytetään oletusarvolla 5000. Tämä muuttaa palkan oletusarvoon '5000' ennen tietueen lisäämistä taulukkoon.
  • Koodirivi 11-14: Lisää tietue 'emp'-taulukkoon.
  • Koodirivi 16: Lisätyn tietueen tarkistaminen.

lähtö:

Triggeri luotu

PL/SQL-menettely suoritettu onnistuneesti.

EMP_NAME EMP_NO PALKKA MANAGER 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 ottamiseksi käyttöön tai poistamiseksi käytöstä on annettava ALTER (DDL) -käsky liipaisimelle, joka poistaa sen käytöstä tai ottaa sen käyttöön.

Alla on syntaksi liipaisujen käyttöön ottamiseksi/poistamiseksi käytöstä.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Syntaksiselitys:

  • Ensimmäinen syntaksi näyttää, kuinka yksittäinen liipaisin otetaan käyttöön tai poistetaan käytöstä.
  • Toinen lause näyttää, kuinka kaikki tietyn taulukon triggerit otetaan käyttöön tai poistetaan käytöstä.

Yhteenveto

Tässä luvussa olemme oppineet PL/SQL-triggereistä ja niiden eduista. Olemme myös oppineet eri luokitukset ja keskustelleet liipaisimen ja YHDISTETYT-liipaisimen SIJAAN.

Saatat tykätä: