Oracle PL/SQL-i päästik: & liittüüpide asemel

Mis on Trigger PL/SQL-is?

PÄÄSTIKUD on salvestatud PL / SQL programmid, mis käivitatakse Oracle mootor automaatselt, kui DML avaldused Näiteks lisamine, värskendamine ja kustutamine käivitatakse tabelis või teatud sündmuste toimumise korral. Päästiku puhul käivitatava koodi saab määratleda vastavalt vajadusele. Saate valida sündmuse, mille korral päästik tuleb käivitada, ja käivitamise ajastuse. Päästiku eesmärk on säilitada andmebaasi teabe terviklikkus.

Päästikute eelised

Allpool on toodud päästikute eelised.

• Mõne tuletatud veeru väärtuse automaatne genereerimine
• Viite terviklikkuse jõustamine
• Sündmuste logimine ja teabe salvestamine tabelijuurdepääsu kohta
• Auditeerimine
• Synctabelite kroonne replikatsioon
• Turvavolituste kehtestamine
• Kehtetute tehingute vältimine

Käivitajate tüübid Oracle

Päästikuid saab klassifitseerida järgmiste parameetrite alusel.

Klassifikatsioon ajastuse põhjal

• ENNE Päästikut: See käivitub enne määratud sündmuse toimumist.
• PÄRAST Päästikut: See käivitub pärast määratud sündmuse toimumist.
• Päästiku asemel: Eriline tüüp. Lisateavet leiate järgmistest teemadest. (ainult DML-i puhul)

Klassifikatsioon taseme põhjal

• STATEMENT taseme päästik: See käivitub määratud sündmuse avalduse korral üks kord.
• ROW taseme päästik: See käivitub iga kirje puhul, mida määratud sündmus mõjutas. (ainult DML-i puhul)

Sündmuse põhjal klassifitseerimine

• DML-i päästik: See käivitub, kui DML-sündmus on määratud (INSERT/UPDATE/DELETE).
• DDL-päästik: See käivitub, kui DDL-sündmus on määratud (CREATE/ALTER).
• ANDMEBAASI käivitaja: See käivitub andmebaasi sündmuse (LOGON/LOGOFF/STARTUP/SHUTDOWN) määramisel.

Seega on iga päästik ülaltoodud parameetrite kombinatsioon.

Päästiku loomine

Allpool on toodud päästiku loomise süntaks. Allolev ekraanipilt näitab päästiku loomise süntaksit keeles 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;

Süntaksi selgitus:

• Ülaltoodud süntaks näitab erinevaid valikulisi lauseid, mis on päästiku loomisel olemas.
• ENNE/PÄRAST määrab sündmuste ajastuse.
• SISESTAMINE/VÄRSKENDAMINE/LOGUN/LOO/jne. määrab sündmuse, mille puhul päästik tuleb käivitada.
• ON-klausel määrab objekti, millel ülalmainitud sündmus kehtib. Näiteks on see tabeli nimi, millel DML-sündmus võib DML-päästiku korral toimuda.
• Käsk „FOR EACH ROW” määrab ROW taseme päästiku.
• WHEN-klausel määrab lisatingimuse, mille korral päästik peab käivituma.
• Deklaratsiooniosa, teostusosa ja erandite käsitlemise osa on samad, mis teistel. PL/SQL plokidDeklaratsiooniosa ja erandite käsitlemine osad on valikulised.

:NEW ja :OLD klausel

Reataseme päästik käivitub iga seotud rea puhul. Ja mõnikord on vaja teada väärtust enne ja pärast DML-lauset.

Oracle on rea tasemel päästikus lisanud kaks klauslit nende väärtuste hoidmiseks. Neid klausleid saab kasutada päästiku sisus vanadele ja uutele väärtustele viitamiseks.

• :UUS – See hoiab päästiku käivitamise ajal baastabeli/vaate veergude jaoks uut väärtust.
• :VANA – See hoiab päästiku täitmise ajal baastabeli/vaate veergude vana väärtust.

Seda klauslit tuleks kasutada DML-sündmuse põhjal. Allolev tabel määrab, milline klausel kehtib millise DML-lause (INSERT/UPDATE/DELETE) jaoks.

Päästiku ASEMEL

„INSTEAD OF trigger” on eritüüpi triger. Seda kasutatakse ainult DML-i trigerites. Seda kasutatakse siis, kui keerulisel vaatel toimub mõni DML-i sündmus.

Vaatleme näidet, kus vaade luuakse kolmest baastabelist. Kui selle vaate kaudu käivitatakse mis tahes DML-sündmus, muutub see kehtetuks, kuna andmed võetakse kolmest erinevast tabelist. Seega kasutatakse antud juhul päästikut INSTEAD OF. Päästikut INSTEAD OF kasutatakse baastabelite otseseks muutmiseks, mitte vaate muutmiseks antud sündmuse jaoks.

Näide 1: Selles näites loome kahest baastabelist keeruka vaate, kus Table_1 on emp-tabel ja Table_2 on osakonna tabel.

Seejärel vaatame, kuidas päästikut INSTEAD OF kasutatakse asukoha üksikasjade UPDATE väljastamiseks sellel keerulisel vaatel. Samuti vaatame, kuidas :NEW ja :OLD on päästikutes kasulikud. Näide on tehtud järgmiste sammudega:

• 1. samm: Sobivate veergudega tabelite 'emp' ja 'dept' loomine
• 2. samm: tabelite täitmine näidisväärtustega
• 3. samm: ülaltoodud tabelite vaate loomine
• 4. samm: vaate värskendamine enne INSTEAD OF päästikut
• 5. samm: INSTEAD OF päästiku loomine
• 6. samm: Vaate värskendamine pärast INSTEAD OF päästikut

1. samm) Loo tabelid 'emp' ja 'dept' sobivate veergudega.

Allolev ekraanipilt näitab 'emp' ja 'dept' baastabelite loomist rakenduses 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 Selgitus

• Code rida 1-7: Tabeli 'emp' loomine.
• Code rida 8-12: Tabeli 'osakond' loomine.

Väljund:

Table Created

Step 2) Nüüd, kuna oleme tabelid loonud, asustame need näidisväärtustega.

Allolev ekraanipilt näitab näidisridade lisamist tabelitesse „dept” ja „emp”.

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 Selgitus

• Code rida 13-19: Andmete sisestamine tabelisse 'dept'.
• Code rida 20-26: Andmete sisestamine tabelisse 'emp'.

Väljund:

PL/SQL procedure completed

Step 3) Vaate loomine ülaltoodud tabelite jaoks.

Allolev ekraanipilt näitab keeruka vaate loomist ja seejärel päringu esitamist.

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 Selgitus

• Code rida 27-32: Vaate 'guru99_emp_view' loomine.
• Code rida 33: Päringu guru99_emp_view.

Väljund:

View created

Step 4) Vaate värskendamine enne INSTEAD OF päästikut.

Allolev ekraanipilt näitab keeruka vaate värskendamise katset ja sellest tulenevat viga.

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

Code Selgitus

• Code rida 34-38: Värskenda „XXX” asukohta „FRANCE”. See tekitas erandi, kuna DML-laused ei ole kompleksvaates otse lubatud.

Väljund:

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

ORA-06512: at line 2

Step 5) Eelmises etapis vaate värskendamisel ilmnenud vea vältimiseks kasutame selles etapis päästikut „INSTEAD OF“.

Allolev ekraanipilt näitab INSTEAD OF päästiku loomist.

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 Selgitus

• Code rida 39: INSTEAD OF päästiku loomine sündmusele 'UPDATE' vaates 'guru99_emp_view' rea tasemel. See sisaldab värskenduslauset 'update' asukoha värskendamiseks baastabelis 'dept'.
• Code rida 44: Värskenduslause kasutab ':NEW' ja ':OLD' veergude väärtuste leidmiseks enne ja pärast värskendamist.

Väljund:

Trigger Created

Step 6) Vaate värskendamine pärast INSTEAD OF päästikut. Nüüd viga enam ei ilmu, kuna selle keeruka vaate värskendamise toimingut haldab „INSTEAD OF päästik”. Koodi käivitamisel värskendatakse töötaja XXX asukoht „Jaapanist” „Prantsusmaaks”.

Allolev ekraanipilt näitab edukat värskendamist INSTEAD OF päästiku kaudu ja värskendatud vaadet.

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

SELECT * FROM guru99_emp_view;

Code Selgitus:

• Code rida 49-53: „XXX” asukoha värskendamine väärtuseks „FRANCE”. See õnnestus, kuna päästik „INSTEAD OF” peatas vaate värskenduslause ja teostas baastabeli värskendamise.
• Code rida 55: Värskendatud kirje kontrollimine.

Väljund:

PL/SQL procedure successfully completed

Ühendpäästik

Liitpäästik on päästik, mis võimaldab teil määrata toimingud iga nelja ajastuspunkti jaoks ühes päästiku põhiosas. See toetab nelja erinevat ajastuspunkti, mis on toodud allpool.

• ENNE AVALDUST – tase
• ENNE RIDA – tase
• PÄRAST RIDA – tase
• PÄRAST AVALDUST – tase

See annab võimaluse kombineerida erineva ajastusega toiminguid samasse päästikusse.

Allolev ekraanipilt näitab liitpäästiku süntaksit koos selle nelja ajastusosaga.

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;

Süntaksi selgitus:

• Ülaltoodud süntaks näitab 'COMPOUND' päästiku loomist.
• Deklaratiivne osa on ühine kõigile päästiku kehas olevatele täitmisplokkidele.
• Need neli ajastusplokki võivad olla suvalises järjekorras. Kõikide nelja ajastusploki olemasolu ei ole kohustuslik. Saame luua liitpäästiku ainult vajalike ajastuste jaoks.

Näide 1: Selles näites loome päästiku, mis täidab palgaveeru automaatselt vaikeväärtusega 5000.

Allolev ekraanipilt näitab liitpäästiku näidet ja selle väljundit.

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 Selgitus:

• Code rida 2-10: Liitpäästiku loomine. See luuakse ajastusel ENNE ROW tasemel palga täitmiseks vaikeväärtusega 5000. See muudab palga enne kirje tabelisse lisamist vaikeväärtuseks '5000'.
• Code rida 11-14: Sisesta kirje tabelisse 'emp'.
• Code rida 16: Sisestatud kirje kontrollimine.

Väljund:

Trigger created

PL/SQL procedure successfully completed.

Päästikute lubamine ja keelamine

Päästikuid saab lubada või keelata. Päästiku lubamiseks või keelamiseks tuleb päästiku kohta anda ALTER (DDL) käsk, mis selle keelab või lubab.

Allpool on toodud päästikute lubamise/keelamise süntaks.

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

Süntaksi selgitus:

• Esimene süntaks näitab, kuidas ühte päästikut lubada/keelata.
• Teine avaldus näitab, kuidas lubada/keelata kõiki konkreetse tabeli päästikuid.

KKK