Vodič za okidač Oracle PL / SQL: Umjesto, složeni (primjer)

Sadržaj:

Anonim

Što je okidač u PL / SQL-u?

TRIGGERS su pohranjeni programi koje Oracle engine automatski pokreće kada se na tablici izvrše DML izvodi poput umetanja, ažuriranja, brisanja ili nekih događaja. Kôd koji treba izvršiti u slučaju okidača može se definirati prema zahtjevu. Možete odabrati događaj nakon kojeg treba aktivirati okidač i vrijeme izvršenja. Svrha okidača je održati integritet podataka u bazi podataka.

U ovom vodiču naučit ćete-

  • Prednosti okidača
  • Vrste okidača u Oracleu
  • Kako stvoriti okidač
  • : NOVO i: STARA klauzula
  • UMJESTO Okidača
  • Složeni okidač

Prednosti okidača

Slijede prednosti okidača.

  • Automatsko generiranje nekih izvedenih vrijednosti stupaca
  • Provođenje referencijalnog integriteta
  • Zapisivanje događaja i spremanje podataka o pristupu tablici
  • Revizija
  • Sinkrona replikacija tablica
  • Izricanje sigurnosnih ovlaštenja
  • Sprječavanje nevaljanih transakcija

Vrste okidača u Oracleu

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

  • Klasifikacija na temelju vremena
    • PRIJE Okidača: Aktivira se prije nego što se dogodio navedeni događaj.
    • NAKON okidača: Aktivira se nakon što se dogodio navedeni događaj.
    • UMJESTO okidača: posebna vrsta. Saznat ćete više o daljnjim temama. (samo za DML)
  • Klasifikacija na temelju razine
    • Okidač na razini STATEMENT: Aktivira se jednom za navedeni iskaz događaja.
    • Okidač REDNE razine: Aktivira se za svaki zapis koji je pogođen u navedenom događaju. (samo za DML)
  • Klasifikacija na temelju događaja
    • 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č DATABASE: Aktivira se kada je naveden događaj baze podataka (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Dakle, svaki okidač je kombinacija gore navedenih parametara.

Kako stvoriti okidač

Ispod je sintaksa za stvaranje okidača.

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

Objašnjenje sintakse:

  • Gornja sintaksa prikazuje različite neobavezne izjave prisutne u stvaranju okidača.
  • BEFORE / AFTER će odrediti vrijeme događaja.
  • INSERT / UPDATE / LOGON / CREATE / itd. odredit će događaj za koji treba aktivirati okidač.
  • Klauzula ON odredit će na kojem je objektu gore spomenuti događaj valjan. Na primjer, ovo će biti naziv tablice na kojem se može dogoditi DML događaj u slučaju DML okidača.
  • Naredba "ZA SVAKI RED" odredit će okidač na razini ROW.
  • Klauzula WHEN odredit će dodatni uvjet u kojem okidač treba aktivirati.
  • Dio deklaracije, dio izvršenja, dio za rukovanje iznimkama isti je kao onaj ostalih PL / SQL blokova. Dio izjave i dio za rukovanje iznimkama nisu obvezni.

: NOVO i: STARA klauzula

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

Oracle je u okidaču na razini RECORD osigurao dvije klauzule koje drže ove vrijednosti. Te se klauzule možemo koristiti za pozivanje na stare i nove vrijednosti unutar tijela okidača.

  • : NOVO - Sadrži novu vrijednost za stupce osnovne tablice / pogleda tijekom izvršavanja okidača
  • : OLD - Sadrži staru vrijednost stupaca osnovne tablice / pogleda tijekom izvršavanja okidača

Ovu klauzulu treba koristiti na temelju DML događaja. Ispod će tablica odrediti koja je klauzula valjana za koji DML izraz (INSERT / UPDATE / DELETE).

UMETNUTI AŽURIRAJ IZBRISATI
:NOVI VRIJEDI VRIJEDI INVALIDNO. U slučaju brisanja nema nove vrijednosti.
:STAR INVALIDNO. U umetnutom slučaju nema stare vrijednosti VRIJEDI VRIJEDI

UMJESTO Okidača

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

Razmotrimo primjer u kojem je prikaz napravljen iz 3 osnovne tablice. Kad se za ovaj prikaz izda bilo koji DML događaj, to će postati nevaljano jer su podaci preuzeti iz 3 različite tablice. Tako se u ovom UMJESTO INA koristi okidač. Okidač UMJESTO OF koristi se za izravnu izmjenu osnovnih tablica umjesto izmjene pogleda za zadani događaj.

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

  • Table_1 je emp tablica i
  • Table_2 je stol odjeljenja.

Tada ćemo vidjeti kako se okidač INSTEAD OF koristi za izdavanje AŽURIRANJA izjave s detaljima lokacije na ovom složenom prikazu. Također ćemo vidjeti kako je: NEW i: OLD korisno u okidačima.

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

Korak 1) Stvaranje 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 'odjela' tablice.

Izlaz

Stvorena tablica

Korak 2) Budući da smo stvorili tablicu, napunit ćemo je tablicom uzorkom vrijednosti i stvaranjem prikaza za gornje tablice. 

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT 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 tablicu 'emp'.

Izlaz

Završena PL / SQL procedura

Korak 3) Stvaranje pogleda za gore stvorenu tablicu. 

CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE emp.dept_no=dept.dept_no;/
SELECT * FROM guru99_emp_view;

Objašnjenje koda

  • Redak koda 27-32: Stvaranje pogleda 'guru99_emp_view'.
  • Linija koda 33: Upit za guru99_emp_view.

Izlaz

Pogled stvoren

IME ZAPOSLENIKA DEPT_NAME MJESTO
ZZZ HR SAD
GGG PRODAJNI UK
XXX FINANCIJSKI JAPAN

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

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

Objašnjenje koda

  • Linijska oznaka 34-38: Ažurirajte mjesto "XXX" na "FRANCE". Pokrenula je iznimku jer DML izrazi nisu dopušteni u složenom prikazu.

Izlaz

ORA-01779: ne može izmijeniti stupac koji se preslikava na tablicu koja nije sačuvana ključem

ORA-06512: na liniji 2

Korak 5) Da bismo izbjegli pogrešku prilikom ažuriranja prikaza u prethodnom koraku, u ovom ćemo koraku koristiti "umjesto okidača".

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/

Objašnjenje koda

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

Izlaz

Okidač stvoren

Korak 6) Ažuriranje prikaza nakon okidača. Sada pogreška neće doći jer će "umjesto okidača" upravljati operacijom ažuriranja ovog složenog prikaza. A kada se kod izvrši, mjesto zaposlenika XXX bit će ažurirano na "Francuska" iz "Japana".

BEGINUPDATE 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" na "FRANCE". Uspješan je jer je okidač 'UMJESTO' zaustavio stvarni prikaz ažuriranja na pogledu i izvršio ažuriranje osnovne tablice.
  • Redak koda 55: Provjera ažuriranog zapisa.

Izlaz:

PL / SQL postupak uspješno završen

IME ZAPOSLENIKA DEPT_NAME MJESTO
ZZZ HR SAD
GGG PRODAJNI UK
XXX FINANCIJSKI FRANCUSKA

Složeni okidač

Složeni okidač okidač je koji vam omogućuje da odredite radnje za svaku od četiri vremenske točke u jednom tijelu okidača. Četiri različita vremenska mjesta koja podržava su kao u nastavku.

  • PRIJE IZJAVE - razina
  • PRIJE REDA - razina
  • NAKON REDA - razina
  • NAKON IZJAVE - razina

Pruža mogućnost kombiniranja radnji za različito vrijeme u isti okidač.

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;END AFTER STATEMENT;END;

Objašnjenje sintakse:

  • Gornja sintaksa prikazuje stvaranje okidača 'COMPOUND'.
  • Deklarativni odjeljak uobičajen je za sve blokove izvršenja u tijelu okidača.
  • Ova 4 vremenska bloka mogu biti u bilo kojem slijedu. Nije obavezno imati sva ova 4 vremenska bloka. Možemo stvoriti okidač COMPOUND samo za vremena koja su potrebna.

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

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/
BEGININSERT 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 vremensko mjerenje PRIJE REDOVE za popunjavanje plaće sa zadanom vrijednošću 5000. To će promijeniti plaću na zadanu vrijednost '5000' prije umetanja zapisa u tablicu.
  • Redak koda 11-14 : Umetnite zapis u tablicu 'emp'.
  • Redak koda 16 : Provjera umetnutog zapisa.

Izlaz:

Okidač stvoren

PL / SQL postupak uspješno završen.

EMP_NAME EMP_NO PLAĆA MENADŽER DEPT_NO
CCC 1004 5000 AAA 30

Omogućavanje i onemogućavanje okidača

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

Ispod je sintaksa omogućavanja / onemogućavanja okidača.

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [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.

Sažetak

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