Oracle Vodič za PL/SQL okidač: umjesto, spoj [primjer]

Što je Trigger u PL/SQL?

TRIGGERI su pohranjeni programi koje pokreće Oracle motor automatski kada se na tablici izvrše DML naredbe kao što su umetanje, ažuriranje, brisanje ili se dogode neki događaji. Kod koji će se izvršiti u slučaju okidača može se definirati prema zahtjevu. Možete odabrati događaj na koji treba aktivirati okidač 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 Vrijeme
PRIJE Okidač: Aktivira se prije nego se dogodi navedeni događaj.
Okidač NAKON: Aktivira se nakon što se dogodi navedeni događaj.
UMJESTO Okidača: Posebna vrsta. Saznat ćete više o daljnjim temama. (samo za DML)
Klasifikacija na temelju nivo
Okidač razine STATEMENT: aktivira se jednom za navedenu naredbu događaja.
Okidač razine ROW: aktivira se za svaki zapis koji je zahvaćen navedenim događajem. (samo za DML)
Klasifikacija na temelju događaj
DML okidač: aktivira se kada je naveden DML događaj (INSERT/UPDATE/DELETE)
DDL okidač: aktivira se kada je naveden DDL događaj (CREATE/ALTER)
Okidač BAZE PODATAKA: pokreće se kada je naveden događaj baze podataka (PRIJAVA/ODJAVA/POKRETANJE/GAŠENJE)

Dakle, svaki okidač je kombinacija gore navedenih parametara.

Kako stvoriti okidač

Ispod je sintaksa za stvaranje okidača.

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/ POSLIJE odredit će vrijeme događaja.
INSERT/UPDATE/PRIJAVA/KREIRANJE/itd. odredit će događaj za koji se okidač treba aktivirati.
ON klauzula će odrediti na kojem je objektu gore spomenuti događaj valjan. Na primjer, ovo će biti naziv tablice na kojoj se može dogoditi DML događaj u slučaju DML okidača.
Naredba “ZA SVAKI RED” odredit će okidač razine REDA.
WHEN klauzula navest će dodatni uvjet u kojem se okidač treba aktivirati.
Dio za deklaraciju, dio za izvođenje, dio za rukovanje iznimkama isti je kao onaj kod ostalih PL/SQL blokovi. Deklaracijski dio i dio za rukovanje iznimkama nisu obavezni.

: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 osigurao dvije klauzule u okidaču na razini RECORD za držanje ovih vrijednosti. Ove klauzule možemo koristiti za pozivanje na stare i nove vrijednosti unutar tijela okidača.

:NEW – Sadrži novu vrijednost za stupce osnovne tablice/pogleda tijekom izvršenja okidača
:OLD – Zadržava staru vrijednost stupaca osnovne tablice/pogleda tijekom izvršavanja okidača

Ovu klauzulu treba koristiti na temelju DML događaja. Donja tablica će odrediti koja je klauzula važeća za koju DML izjavu (INSERT/UPDATE/DELETE).

	INSERT	UPDATE	DELETE
:NOVI	VRIJEDI	VRIJEDI	NEVAŽEĆE. Nema nove vrijednosti u brisanju velikih i malih slova.
:STAR	NEVAŽEĆE. U umetnutom slučaju nema stare vrijednosti	VRIJEDI	VRIJEDI

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.

Razmotrite primjer u kojem je pogled napravljen od 3 osnovne tablice. Kada se preko ovog prikaza izda bilo koji DML događaj, on će postati nevažeći jer su podaci preuzeti iz 3 različite tablice. Dakle, u ovom se koristi okidač INSTEAD OF. Okidač INSTEAD OF koristi se za izravnu izmjenu osnovnih tablica umjesto za izmjenu pogleda za dati događaj.

Primjer 1: U ovom primjeru stvorit ćemo složeni prikaz iz dvije osnovne tablice.

Table_1 je prazna tablica i

Tablica_2 je tablica odjela.

Zatim ćemo vidjeti kako se okidač INSTEAD OF koristi za izdavanje UPDATE izjave o detaljima lokacije na ovom složenom prikazu. Također ćemo vidjeti kako su :NEW i :OLD korisni u okidačima.

Korak 1: Stvaranje tablice 'emp' i 'dept' s odgovarajućim stupcima
Korak 2: Popunjavanje tablice uzorcima vrijednosti
Korak 3: Stvaranje prikaza za gore stvorenu tablicu
Korak 4: ažuriranje prikaza prije okidača umjesto
Korak 5: Stvaranje okidača umjesto
Korak 6: ažuriranje prikaza nakon okidača umjesto umjesto

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

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

Objašnjenje koda

Redak koda 1-7: Stvaranje tablice 'emp'.
Redak koda 8-12: Stvaranje tablice 'dept'.

Izlaz

Tablica je stvorena

Korak 2) Budući da smo izradili tablicu, popunit ćemo ovu tablicu oglednim vrijednostima i stvaranjem prikaza za gornje tablice.

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

Objašnjenje koda

Redak koda 13-19: Umetanje podataka u 'dept' tablicu.
Redak koda 20-26: Umetanje podataka u 'emp' tablicu.

Izlaz

PL/SQL postupak dovršen

Korak 3) Izrada prikaza za gore stvorenu tablicu.

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;

Objašnjenje koda

Redak koda 27-32: Izrada pogleda 'guru99_emp_view'.

Redak koda 33: Upit guru99_emp_view.

Izlaz

Prikaz stvoren

IME ZAPOSLENIKA	DEPT_NAME	LOKACIJA
ZZZ	HR	SAD
GGG	SALES	UK
XXX	FINANCIJSKA	JAPAN

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

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

Objašnjenje koda

Redak koda 34-38: Ažurirajte lokaciju "XXX" u "FRANCUSKA". Pokrenula je iznimku jer DML izjave nisu dopušteni u složenom prikazu.

Izlaz

ORA-01779: nije moguće modificirati stupac koji se preslikava na tablicu bez očuvanja ključa

ORA-06512: u retku 2

Korak 5)Kako bismo izbjegli pojavu pogreške tijekom ažuriranja prikaza u prethodnom koraku, u ovom ćemo koraku koristiti "umjesto okidača".

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

Objašnjenje koda

Redak koda 39: Stvaranje okidača INSTEAD OF za događaj 'UPDATE' na prikazu 'guru99_emp_view' na razini ROW. Sadrži izjavu o ažuriranju za ažuriranje lokacije u osnovnoj tablici 'dept'.
Redak koda 44: Naredba ažuriranja koristi ':NEW' i ':OLD' za pronalaženje vrijednosti stupaca prije i nakon ažuriranja.

Izlaz

Okidač je stvoren

Korak 6) Ažuriranje prikaza nakon okidača umjesto okidača. Sada se pogreška neće pojaviti jer će "umjesto okidača" upravljati operacijom ažuriranja ovog složenog prikaza. A kada se kôd izvrši, lokacija zaposlenika XXX bit će ažurirana u "Francuska" iz "Japana".

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

SELECT * FROM guru99_emp_view;

Objašnjenje koda:

Redak koda 49-53: Ažuriranje lokacije "XXX" u "FRANCUSKA". Uspješan je jer je okidač 'UMJESTO'' zaustavio stvarni izraz ažuriranja na prikazu i izvršio ažuriranje osnovne tablice.
Redak koda 55: Provjera ažuriranog zapisa.

Izlaz:

PL/SQL procedura uspješno završena

IME ZAPOSLENIKA	DEPT_NAME	LOKACIJA
ZZZ	HR	SAD
GGG	SALES	UK
XXX	FINANCIJSKA	FRANCUSKA

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 su sljedeće.

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

Omogućuje kombiniranje radnji za različito vrijeme u isti okidač.

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;

Objašnjenje sintakse:

Gornja sintaksa prikazuje stvaranje okidača 'COMPOUND'.
Deklarativni odjeljak zajednički je za sve izvršne blokove u tijelu okidača.
Ova 4 vremenska bloka mogu biti u bilo kojem nizu. Nije obavezno imati sva ova 4 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 zadanom vrijednošću 5000.

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;

Objašnjenje koda:

Redak koda 2-10: Stvaranje složenog okidača. Stvoren je za mjerenje vremena PRIJE RETKA na razini za popunjavanje plaće sa zadanom vrijednošću 5000. Ovo će promijeniti plaću na zadanu vrijednost '5000' prije umetanja zapisa u tablicu.
Redak koda 11-14: Umetnite zapis u 'emp' tablicu.
Kodna linija 16: Provjera umetnutog zapisa.

Izlaz:

Okidač je stvoren

PL/SQL procedura uspješno završena.

EMP_NAME	EMP_NO	PLAĆA	MANAGER	DEPT_BR
HGK	1004	5000	AAA	30

Omogućivanje i onemogućavanje okidača

Okidači se mogu omogućiti ili onemogućiti. Da biste omogućili ili onemogućili okidač, treba dati naredbu ALTER (DDL) za okidač koji ga onemogućuje ili omogućuje.

Ispod je 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 pojedinačni okidač.
Druga izjava pokazuje kako omogućiti/onemogućiti sve okidače na određenoj tablici.

rezime

U ovom poglavlju naučili smo o PL/SQL okidačima i njihovim prednostima. Također smo naučili različite klasifikacije i raspravljali o okidaču UMJESTO okidača i SLOŽENI okidač.

Možda ti se svidi:

Što je Trigger u PL/SQL?

Prednosti okidača

Vrste okidača u Oracle

Kako stvoriti okidač

POVEZANI ČLANCI

:NEW i :STARA klauzula

UMJESTO Okidač

Složeni okidač

rezime