Oracle PL/SQL okidač: Umjesto složenih tipova &

Što je Trigger u PL/SQL?

OTKAZIVAČI su pohranjeni PL / SQL programe koje pokreće Oracle motor automatski kada DML izjave Naredbe poput umetanja, ažuriranja i brisanja izvršavaju se na tablici ili kada se dogode neki događaji. Kod koji se izvršava u slučaju okidača može se definirati prema zahtjevu. Možete odabrati događaj na koji se okidač treba aktivirati i vrijeme izvršenja. Svrha okidača je održavanje integriteta informacija u bazi podataka.

Prednosti okidača

Slijede prednosti okidača.

• Automatsko generiranje nekih izvedenih vrijednosti stupaca
• Provođenje referencijalnog integriteta
• Zapisivanje događaja i pohranjivanje informacija o pristupu tablici
• Revizija
• Synchronološka replikacija tablica
• Nametanje sigurnosnih ovlaštenja
• Sprječavanje nevažećih transakcija

Vrste okidača u Oracle

Okidači se mogu klasificirati na temelju sljedećih parametara.

Klasifikacija na temelju vremena

• PRIJE okidača: Okida se prije nego što se dogodi određeni događaj.
• NAKON okidača: Okida se nakon što se dogodi određeni događaj.
• UMJESTO Okidača: Posebna vrsta. Više ćete saznati u sljedećim temama. (samo za DML)

Klasifikacija na temelju razine

• Okidač na razini IZJAVE: Okida se jednom za navedenu naredbu događaja.
• Okidač na razini ROW-a: Okida se za svaki zapis koji je pogođen određenim događajem. (samo za DML)

Klasifikacija na temelju događaja

• DML okidač: Okida se kada je naveden DML događaj (INSERT/UPDATE/DELETE).
• DDL okidač: Okida se kada je naveden DDL događaj (CREATE/ALTER).
• Okidač BAZE PODATAKA: Okida se kada je naveden događaj baze podataka (PRIJAVA/ODJAVA/POKRETANJE/ISKLJUČIVANJE).

Dakle, svaki okidač je kombinacija gore navedenih parametara.

Kako stvoriti okidač

U nastavku je sintaksa za stvaranje okidača. Snimka zaslona u nastavku prikazuje ovu sintaksu stvaranja okidača u 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;

Objašnjenje sintakse:

• Gornja sintaksa prikazuje različite neobavezne izjave koje su prisutne u stvaranju okidača.
• PRIJE/POSLJE će odrediti vrijeme događaja.
• INSERT/UPDATE/PRIJAVA/KREIRANJE/itd. odredit će događaj za koji se okidač treba aktivirati.
• Klauzula ON će odrediti objekt na kojem je gore navedeni događaj valjan. Na primjer, to će biti naziv tablice na kojoj se DML događaj može dogoditi u slučaju DML okidača.
• Naredba „ZA SVAKI RED“ odredit će okidač na razini RED.
• Klauzula WHEN će odrediti dodatni uvjet u kojem se okidač mora aktivirati.
• Dio deklaracije, dio izvršenja i dio za obradu iznimki isti su kao i kod ostalih PL/SQL blokoviDeklaracijski dio i rukovanje izuzetkom dio je opcionalan.

:NEW i :STARA klauzula

U okidaču na razini retka, okidač se aktivira za svaki povezani redak. A ponekad je potrebno znati vrijednost prije i poslije DML izjave.

Oracle je u okidaču na razini retka osigurao dvije klauzule za pohranu tih vrijednosti. Ove klauzule možemo koristiti za pozivanje na stare i nove vrijednosti unutar tijela okidača.

• :NOVI – Sadrži novu vrijednost za stupce osnovne tablice/prikaza tijekom izvršavanja okidača.
• :STAR – Zadržava staru vrijednost stupaca osnovne tablice/prikaza tijekom izvršavanja okidača.

Ova klauzula treba se koristiti na temelju DML događaja. Donja tablica navodi koja klauzula vrijedi za koju DML naredbu (INSERT/UPDATE/DELETE).

UMJESTO Okidač

"UMJESTO okidača" je posebna vrsta okidača. Koristi se samo u DML okidačima. Koristi se kada će se bilo koji DML događaj dogoditi na složenom prikazu.

Razmotrimo primjer u kojem je prikaz napravljen od tri osnovne tablice. Kada se bilo koji DML događaj izda nad ovim prikazom, on će postati nevažeći jer su podaci preuzeti iz tri različite tablice. Dakle, u ovom slučaju koristi se okidač UMJESTO OF. Okidač UMJESTO OF koristi se za izravnu izmjenu osnovnih tablica umjesto izmjene prikaza za zadani događaj.

Primjer 1: U ovom primjeru, stvorit ćemo složeni prikaz iz dvije osnovne tablice, gdje je Tablica_1 privremena tablica, a Tablica_2 tablica odjela.

Zatim ćemo vidjeti kako se okidač INSTEAD OF koristi za izdavanje UPDATE detalja lokacije na ovom složenom prikazu. Također ćemo vidjeti kako su :NEW i :OLD korisni u okidačima. Primjer se izvodi u sljedećim koracima:

• Korak 1: Izrada tablica 'emp' i 'dept' s odgovarajućim stupcima
• Korak 2: Popunjavanje tablica s primjerima vrijednosti
• Korak 3: Izrada prikaza za gore kreirane tablice
• Korak 4: Ažuriranje prikaza prije okidača UMJESTO OF
• Korak 5: Izrada okidača UMJESTO OF
• Korak 6: Ažuriranje prikaza nakon okidača UMJESTO OF

Korak 1) Izrada tablica 'emp' i 'dept' s odgovarajućim stupcima.

Snimka zaslona u nastavku prikazuje stvaranje osnovnih tablica 'emp' i 'dept' u 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 Objašnjenje

• Code redak 1-7: Izrada tablice 'emp'.
• Code redak 8-12: Izrada tablice 'odjel'.

Izlaz:

Table Created

Korak 2) Sada, budući da smo kreirali tablice, popunit ćemo ih primjerima vrijednosti.

Snimka zaslona u nastavku prikazuje primjere redaka koji se umeću u tablice 'dept' i '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 Objašnjenje

• Code redak 13-19: Unos podataka u tablicu 'odjel'.
• Code redak 20-26: Umetanje podataka u 'emp' tablicu.

Izlaz:

PL/SQL procedure completed

Korak 3) Izrada prikaza za gore kreirane tablice.

Snimka zaslona u nastavku prikazuje stvaranje, a zatim i slanje upita o složenom prikazu.

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 Objašnjenje

• Code redak 27-32: Izrada prikaza 'guru99_emp_view'.
• Code redak 33: Upit guru99_emp_view.

Izlaz:

View created

Korak 4) Ažuriranje prikaza prije okidača UMJESTO OF.

Snimka zaslona u nastavku prikazuje pokušaj ažuriranja složenog prikaza i rezultirajuću pogrešku.

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

Code Objašnjenje

• Code redak 34-38: Ažurirajte lokaciju "XXX" na 'FRANCUSKA'. To je izazvalo iznimku jer DML naredbe nisu dopuštene izravno na složenom prikazu.

Izlaz:

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

ORA-06512: at line 2

Korak 5) Kako bismo izbjegli grešku koja se pojavila prilikom ažuriranja prikaza u prethodnom koraku, u ovom koraku ćemo koristiti okidač "UMJESTO okidača".

Snimka zaslona u nastavku prikazuje stvaranje okidača UMJESTO OF.

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 Objašnjenje

• Code redak 39: Stvaranje okidača INSTEAD OF za događaj 'UPDATE' na prikazu 'guru99_emp_view' na razini ROW-a. Sadrži naredbu ažuriranja za ažuriranje lokacije u osnovnoj tablici 'dept'.
• Code redak 44: Naredba za ažuriranje koristi ':NEW' i ':OLD' za pronalaženje vrijednosti stupaca prije i poslije ažuriranja.

Izlaz:

Trigger Created

Korak 6) Ažuriranje prikaza nakon okidača INSTEAD OF. Sada se greška neće pojavljivati ​​jer će okidač "INSTEAD OF" obraditi operaciju ažuriranja ovog složenog prikaza. Kada se kod izvrši, lokacija zaposlenika XXX bit će ažurirana iz "Japan" u "Francuska".

Snimka zaslona u nastavku prikazuje uspješno ažuriranje putem okidača UMJESTO i osvježeni prikaz.

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

SELECT * FROM guru99_emp_view;

Code Objašnjenje:

• Code redak 49-53: Ažuriranje lokacije "XXX" na 'FRANCUSKA'. Uspješno je jer je okidač 'UMJESTO' zaustavio stvarnu naredbu ažuriranja na prikazu i izvršio ažuriranje osnovne tablice.
• Code redak 55: Provjera ažuriranog zapisa.

Izlaz:

PL/SQL procedure successfully completed

Složeni okidač

Složeni okidač je okidač koji vam omogućuje određivanje radnji za svaku od četiri vremenske točke u jednom tijelu okidača. Četiri različite vremenske točke koje podržava navedene su u nastavku.

• PRIJE IZJAVE – razina
• PRIJE REDA – razina
• POSLIJERED – razina
• NAKON IZJAVE – razina

Omogućuje kombiniranje radnji za različita vremena u isti okidač.

Snimka zaslona u nastavku prikazuje sintaksu složenog okidača s njegova četiri vremenska dijela.

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;

Objašnjenje sintakse:

• Gornja sintaksa prikazuje stvaranje okidača 'COMPOUND'.
• Deklarativni dio je zajednički za sve izvršne blokove u tijelu okidača.
• Ova četiri vremenska bloka mogu biti u bilo kojem redoslijedu. Nije obavezno imati sva četiri vremenska bloka. Možemo stvoriti COMPOUND okidač samo za vremena koja su potrebna.

Primjer 1: U ovom primjeru, stvorit ćemo okidač za automatsko popunjavanje stupca plaće s zadanom vrijednošću 5000.

Snimka zaslona u nastavku prikazuje primjer složenog okidača i njegov izlaz.

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 Objašnjenje:

• Code redak 2-10: Stvaranje složenog okidača. Stvara se za razinu BEFORE ROW kako bi se plaća popunila zadanom vrijednošću 5000. To će promijeniti plaću na zadanu vrijednost '5000' prije umetanja zapisa u tablicu.
• Code redak 11-14: Umetnite zapis u 'emp' tablicu.
• Code redak 16: Provjera umetnutog zapisa.

Izlaz:

Trigger created

PL/SQL procedure successfully completed.

Omogućivanje i onemogućavanje okidača

Okidači se mogu omogućiti ili onemogućiti. Za omogućavanje ili onemogućavanje okidača potrebno je navesti naredbu ALTER (DDL) za okidač koja ga onemogućuje ili omogućuje.

U nastavku slijedi sintaksa za omogućavanje/onemogućavanje okidača.

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

Objašnjenje sintakse:

• Prva sintaksa pokazuje kako omogućiti/onemogućiti jedan okidač.
• Druga izjava pokazuje kako omogućiti/onemogućiti sve okidače na određenoj tablici.

Pitanja i odgovori