Oracle PL/SQL-liipaisin: Yhdistetyyppien & sijaan

Mikä on Trigger PL/SQL:ssä?

LÄPÄISYT tallennetaan PL / SQL ohjelmat, jotka laukaisevat Oracle moottori automaattisesti, kun DML-lauseet kuten lisäys, päivitys ja poisto suoritetaan taulukossa tai kun joitakin tapahtumia tapahtuu. Liipaisimen tapauksessa suoritettava koodi voidaan määritellä vaatimuksen mukaan. Voit valita tapahtuman, jonka jälkeen liipaisin on laukaistava, ja suorituksen ajoituksen. Liipaisimen tarkoituksena on ylläpitää tietojen eheyttä tietokannassa.

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 ajoituksen perusteella

• ENNEN Liipaisinta: Se käynnistyy ennen määritetyn tapahtuman tapahtumista.
• JÄLKEEN Liipaisin: Se käynnistyy määritetyn tapahtuman jälkeen.
• Liipaisimen sijaan: Erikoistyyppi. Lisätietoja seuraavista aiheista. (vain DML:lle)

Luokittelu tason perusteella

• STATEMENT-tason liipaisin: Se laukeaa kerran määritetyn tapahtumalausekkeen mukaisesti.
• ROW-tason liipaisin: Se käynnistyy jokaisen tietueen kohdalla, johon määritetty tapahtuma vaikuttaa. (vain DML:lle)

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).
• TIETOKANTA-liipaisin: Se käynnistyy, kun tietokantatapahtuma on määritetty (LOGON/LOGOFF/STARTUP/SHUTDOWN).

Joten jokainen liipaisin on yhdistelmä yllä olevista parametreista.

Kuinka luoda triggeri

Alla on syntaksi liipaisimen luomiseksi. Alla oleva kuvakaappaus näyttää tämän liipaisimen luomissyntaksin muodossa Oracle.

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.
• BEFORE/AFTER määrittää tapahtumien ajankohdat.
• LISÄÄ/PÄIVITYS/KIRJAUDU/LUO/jne. määrittää tapahtuman, jota varten laukaisu on käynnistettävä.
• ON-lauseke määrittää objektin, jolle edellä mainittu tapahtuma on voimassa. Tämä on esimerkiksi sen taulukon nimi, jolle DML-tapahtuma voi tapahtua DML-liipaisimen tapauksessa.
• Komento ”FOR EACH ROW” määrittää rivitason liipaisimen.
• WHEN-lauseke määrittää lisäehdon, jossa liipaisimen on laukaistava.
• Määrittelyosa, suoritusosa ja poikkeusten käsittelyosa ovat samat kuin muissakin PL/SQL-lohkotIlmoitusosa ja poikkeusten käsittely osat ovat valinnaisia.

: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 tarjonnut rivitason liipaisimessa kaksi lauseketta näiden arvojen säilyttämistä varten. Voimme käyttää näitä lausekkeita viitataksemme liipaisimen rungon sisällä oleviin vanhoihin ja uusiin arvoihin.

• : UUSI – Se säilyttää uuden arvon perustaulukon/näkymän sarakkeille liipaisimen suorituksen aikana.
• :VANHA – Se säilyttää perustaulukon/näkymän sarakkeiden vanhan arvon liipaisimen suorituksen aikana.

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

Liipaisimen SIJAAN

”INSTEAD OF -liipaisin” on erityinen liipaisintyyppi. Sitä käytetään vain DML-liipaisimissa. Sitä käytetään, kun jokin DML-tapahtuma on tapahtumassa monimutkaisessa näkymässä.

Tarkastellaan esimerkkiä, jossa näkymä luodaan kolmesta perustaulukosta. Kun jokin DML-tapahtuma suoritetaan tämän näkymän kautta, siitä tulee virheellinen, koska tiedot otetaan kolmesta eri taulukosta. Tässä tapauksessa käytetään siis INSTEAD OF -liipaisinta. INSTEAD OF -liipaisinta käytetään suoraan perustaulukoiden muokkaamiseen sen sijaan, että näkymää muokattaisiin tietyn tapahtuman osalta.

Esimerkki 1: Tässä esimerkissä luomme monimutkaisen näkymän kahdesta perustaulukosta, jossa Table_1 on emp-taulukko ja Table_2 on osastotaulukko.

Seuraavaksi katsomme, miten INSTEAD OF -liipaisinta käytetään sijaintitietojen PÄIVITYKSEN (UPDATE) antamiseen tässä monimutkaisessa näkymässä. Katsomme myös, miten :NEW ja :OLD ovat hyödyllisiä liipaisimissa. Esimerkki tehdään seuraavissa vaiheissa:

• Vaihe 1: Taulukoiden 'emp' ja 'dept' luominen asianmukaisilla sarakkeilla
• Vaihe 2: Taulukoiden täyttäminen esimerkkiarvoilla
• Vaihe 3: Näkymän luominen yllä luoduille taulukoille
• Vaihe 4: Näkymän päivittäminen ennen INSTEAD OF -liipaisinta
• Vaihe 5: INSTEAD OF -liipaisimen luominen
• Vaihe 6: Näkymän päivitys INSTEAD OF -liipaisimen jälkeen

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

Alla oleva kuvakaappaus näyttää 'emp'- ja 'dept'-perustaulukoiden luomisen ohjelmassa Oracle.

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

Code Selitys

• Code rivi 1–7: Taulukon 'emp' luonti.
• Code rivi 8–12: Taulukon 'osasto' luonti.

lähtö:

Table Created

Vaihe 2) Koska olemme nyt luoneet taulukot, täytämme ne esimerkkiarvoilla.

Alla olevassa kuvakaappauksessa näytetään esimerkkirivien lisääminen 'dept'- ja 'emp'-taulukoihin.

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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Selitys

• Code rivi 13–19: Tietojen lisääminen 'dept'-taulukkoon.
• Code rivi 20–26: Tietojen lisääminen 'emp'-taulukkoon.

lähtö:

PL/SQL procedure completed

Vaihe 3) Näkymän luominen yllä luoduille taulukoille.

Alla olevassa kuvakaappauksessa näkyy, kuinka monimutkainen näkymä luodaan ja sitä sitten kysellään.

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;

Code Selitys

• Code rivi 27–32: 'guru99_emp_view'-näkymän luominen.
• Code rivi 33: Kysely guru99_emp_view.

lähtö:

View created

Vaihe 4) Näkymän päivitys ennen INSTEAD OF -liipaisinta.

Alla oleva kuvakaappaus näyttää päivitysyrityksen kompleksinäkymässä ja siitä johtuvan virheen.

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

Code Selitys

• Code rivi 34–38: Päivitä “XXX”:n sijainniksi 'FRANCE'. Se aiheutti poikkeuksen, koska DML-lausekkeita ei sallita suoraan kompleksinäkymässä.

lähtö:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

Vaihe 5) Välttääksemme edellisessä vaiheessa näkymän päivittämisen aikana ilmenneen virheen, käytämme tässä vaiheessa ”INSTEAD OF” -liipaisinta.

Alla oleva kuvakaappaus näyttää INSTEAD OF -liipaisimen luomisen.

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

Code Selitys

• Code rivi 39: INSTEAD OF -liipaisimen luonti 'UPDATE'-tapahtumalle 'guru99_emp_view'-näkymässä ROW-tasolla. Se sisältää update-lausekkeen, joka päivittää sijainnin perustaulukossa 'dept'.
• Code rivi 44: Päivityslauseke käyttää ':NEW'- ja ':OLD'-muuttujaa sarakkeiden arvon löytämiseen ennen päivitystä ja sen jälkeen.

lähtö:

Trigger Created

Vaihe 6) Näkymän päivitys INSTEAD OF -liipaisimen jälkeen. Virhettä ei enää ilmene, koska ”INSTEAD OF -liipaisin” käsittelee tämän monimutkaisen näkymän päivitystoiminnon. Kun koodi suoritetaan, työntekijän XXX sijainti päivittyy ”Japanista” muotoon ”Ranska”.

Alla oleva kuvakaappaus näyttää onnistuneen päivityksen INSTEAD OF -liipaisimen avulla ja päivitetyn näkymän.

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

SELECT * FROM guru99_emp_view;

Code Selitys:

• Code rivi 49–53: Sijainnin “XXX” päivitys muotoon 'FRANCE'. Päivitys onnistuu, koska liipaisin 'INSTEAD OF' on pysäyttänyt varsinaisen päivityskäskyn näkymälle ja suorittanut perustaulukon päivityksen.
• Code rivi 55: Päivitetyn tietueen tarkistaminen.

lähtö:

PL/SQL procedure successfully completed

Yhdistelmälaukaisu

Yhdistetyn liipaisimen avulla voit määrittää toiminnot jokaiselle neljälle ajoituspisteelle yhdessä liipaisimen rungossa. Sen tukemat neljä erilaista ajoituspistettä ovat seuraavat.

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

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

Alla oleva kuvakaappaus näyttää yhdistetyn liipaisimen syntaksin ja sen neljä ajoitusosiota.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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'-liipaisimen luomisen.
• Deklaratiiviosa on yhteinen kaikille liipaisimen rungon suorituslohkoille.
• Nämä neljä ajoituslohkoa voivat olla missä tahansa järjestyksessä. Kaikkien neljän ajoituslohkon olemassaolo ei ole pakollista. Voimme luoda COMPOUND-liipaisimen vain tarvittaville ajoituksille.

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

Alla oleva kuvakaappaus näyttää yhdistetyn liipaisimen esimerkin ja sen tulosteen.

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;

Code Selitys:

• Code rivi 2–10: Yhdistetyn liipaisimen luonti. Se luodaan BEFORE ROW -tasolle, jotta palkkaan lisätään oletusarvo 5000. Tämä muuttaa palkan oletusarvoon '5000' ennen tietueen lisäämistä tauluun.
• Code rivi 11–14: Lisää tietue 'emp'-taulukkoon.
• Code rivi 16: Lisätyn tietueen varmentaminen.

lähtö:

Trigger created

PL/SQL procedure successfully completed.

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

Liipaisimet voidaan ottaa käyttöön tai poistaa käytöstä. Liipaisimen ottamiseksi käyttöön 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 käyttöönottoa/poistamista varten.

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/pois käytöstä.
• Toinen lause näyttää, kuinka kaikki tietyn taulukon triggerit otetaan käyttöön tai poistetaan käytöstä.

UKK