Oracle PL / SQL Trigger Tutorial: I stedet for, Compound (eksempel)

Indholdsfortegnelse:

Anonim

Hvad er udløser i PL / SQL?

TRIGGERS er lagrede programmer, der automatisk affyres af Oracle-motoren, når DML-udsagn som indsættelse, opdatering, sletning udføres på bordet, eller nogle begivenheder opstår. Koden, der skal udføres i tilfælde af en trigger, kan defineres i henhold til kravet. Du kan vælge den begivenhed, hvorpå udløseren skal affyres, og tidspunktet for udførelsen. Formålet med trigger er at opretholde integriteten af ​​oplysninger i databasen.

I denne vejledning lærer du-

  • Fordele ved udløsere
  • Typer af udløsere i Oracle
  • Sådan oprettes udløser
  • : NY og: GAMLE Klausul
  • I stedet for udløser
  • Sammensat udløser

Fordele ved udløsere

Følgende er fordelene ved udløsere.

  • Genererer automatisk nogle afledte kolonneværdier
  • Håndhævelse af referenceintegritet
  • Hændelseslogning og lagring af oplysninger om bordadgang
  • Revision
  • Synkron replikering af tabeller
  • Pålægge sikkerhedstilladelser
  • Forebyggelse af ugyldige transaktioner

Typer af udløsere i Oracle

Udløsere kan klassificeres ud fra følgende parametre.

  • Klassificering baseret på timing
    • FØR udløseren: Den udløses, før den angivne begivenhed er sket.
    • EFTER Trigger: Den udløses, efter at den angivne begivenhed har fundet sted.
    • I stedet for udløser: En særlig type. Du vil lære mere om de yderligere emner. (kun for DML)
  • Klassificering baseret på niveauet
    • UDTALELSESNiveau Udløser: Det affyres en gang for den angivne begivenhedserklæring.
    • ROW-niveau udløser: Det affyres for hver post, der blev påvirket i den specificerede begivenhed. (kun for DML)
  • Klassificering baseret på begivenheden
    • DML-udløser: Den aktiveres, når DML-begivenheden er angivet (INSERT / UPDATE / DELETE)
    • DDL-udløser: Den aktiveres, når DDL-hændelsen er angivet (CREATE / ALTER)
    • DATABASE-udløser: Den aktiveres, når databasehændelsen er angivet (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Så hver udløser er kombinationen af ​​ovenstående parametre.

Sådan oprettes udløser

Nedenfor er syntaksen for oprettelse af en trigger.

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

Syntaks Forklaring:

  • Ovenstående syntaks viser de forskellige valgfrie udsagn, der er til stede i udløseroprettelsen.
  • FØR / EFTER specificerer begivenhedens tidspunkter.
  • INSERT / UPDATE / LOGON / CREATE / etc. angiver den begivenhed, som udløseren skal affyres for.
  • ON-klausul angiver, hvilket objekt ovennævnte begivenhed er gyldig. For eksempel vil dette være det tabelnavn, som DML-hændelsen kan forekomme i tilfælde af DML Trigger.
  • Kommandoen "FOR HVER Række" angiver ROW-niveauudløseren.
  • NÅR-klausul angiver den yderligere tilstand, hvor udløseren skal affyres.
  • Erklæringsdelen, eksekveringsdel, undtagelseshåndteringsdel er den samme som de andre PL / SQL-blokke. Erklæringsdel og undtagelseshåndteringsdel er valgfri.

: NY og: GAMLE Klausul

I en række på niveauudløser aktiveres udløseren for hver relateret række. Og nogle gange er det nødvendigt at kende værdien før og efter DML-erklæringen.

Oracle har leveret to klausuler i RECORD-niveau-udløseren til at holde disse værdier. Vi kan bruge disse klausuler til at henvise til de gamle og nye værdier inde i triggerkroppen.

  • : NY - Den indeholder en ny værdi for kolonnerne i basistabellen / visningen under udførelsen af ​​udløseren
  • : OLD - Det indeholder gammel værdi af kolonnerne i basistabellen / -visningen under udførelsen af ​​udløseren

Denne klausul skal bruges baseret på DML-begivenheden. Nedenstående tabel specificerer, hvilken klausul der er gyldig for hvilken DML-sætning (INSERT / UPDATE / DELETE).

INDSÆT OPDATER SLET
:NY GYLDIG GYLDIG Ugyldig. Der er ingen ny værdi i slette sag.
:GAMMEL Ugyldig. Der er ingen gammel værdi i indsæt sag GYLDIG GYLDIG

I stedet for udløser

"INSTEAD OF trigger" er den specielle type trigger. Det bruges kun i DML-udløsere. Det bruges, når en DML-begivenhed skal forekomme i den komplekse visning.

Overvej et eksempel, hvor en visning er lavet af 3 basistabeller. Når der udstedes en DML-begivenhed over denne visning, bliver den ugyldig, fordi dataene hentes fra 3 forskellige tabeller. Så i denne INSTEAD OF trigger bruges. INSTEAD OF-udløseren bruges til at ændre basistabellerne direkte i stedet for at ændre visningen for den givne begivenhed.

Eksempel 1 : I dette eksempel skal vi oprette en kompleks visning fra to basistabel.

  • Table_1 er emp-tabel og
  • Table_2 er afdelingstabel.

Derefter skal vi se, hvordan INSTEAD OF-udløseren bruges til at udsende OPDATERING af placeringsdetaljer på denne komplekse visning. Vi vil også se, hvordan: NEW og: OLD er nyttigt i udløsere.

  • Trin 1: Oprettelse af tabel 'emp' og 'dept' med passende kolonner
  • Trin 2: Udfyld tabellen med prøveværdier
  • Trin 3: Oprettelse af visning til ovenstående oprettede tabel
  • Trin 4: Opdater visningen før i stedet for udløseren
  • Trin 5: Oprettelse af stedet for triggeren
  • Trin 6: Opdater visningen efter i stedet for udløseren

Trin 1) Oprettelse af tabel 'emp' og 'dept' med passende kolonner

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

Kode Forklaring

  • Kodelinje 1-7 : Oprettelse af tabel 'emp'.
  • Kodelinje 8-12 : Oprettelse af tabel 'dept'.

Produktion

Tabel oprettet

Trin 2) Siden vi har oprettet tabellen, udfylder vi denne tabel med prøveværdier og oprettelse af visninger for ovenstående tabeller. 

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

Kode Forklaring

  • Kodelinje 13-19 : Indsættelse af data i 'dept' -tabellen.
  • Kodelinje 20-26: Indsættelse af data i 'emp' -tabellen.

Produktion

PL / SQL-procedure afsluttet

Trin 3) Oprettelse af en visning til ovenstående oprettede tabel. 

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;

Kode Forklaring

  • Kodelinje 27-32: Oprettelse af 'guru99_emp_view' visning.
  • Kodelinje 33: Forespørgsel på guru99_emp_view.

Produktion

Visning oprettet

ANSATTES NAVN DEPT_NAME BELIGGENHED
ZZZ HR USA
ÅÅÅ SALG UK
XXX FINANSIEL JAPAN

Trin 4) Opdatering af visningen før i stedet for udløseren.

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

Kode Forklaring

  • Kodelinje 34-38: Opdater placeringen af ​​"XXX" til 'FRANKRIG'. Det rejste undtagelsen, fordi DML-udsagnene ikke er tilladt i den komplekse visning.

Produktion

ORA-01779: kan ikke ændre en kolonne, der kortlægges til en ikke-nøglekonserveret tabel

ORA-06512: ved linje 2

Trin 5) For at undgå den fejl, der opstår under opdateringsvisningen i det foregående trin, skal vi i dette trin bruge "i stedet for trigger".

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

Kode Forklaring

  • Kodelinje 39: Oprettelse af INSTEAD OF trigger for 'UPDATE' begivenhed i visningen 'guru99_emp_view' på ROW-niveau. Den indeholder opdateringserklæringen for at opdatere placeringen i basistabellen 'dept'.
  • Kodelinje 44: Opdateringserklæring bruger ': NEW' og ': OLD' til at finde værdien af ​​kolonner før og efter opdateringen.

Produktion

Trigger oprettet

Trin 6) Opdatering af visning efter i stedet for udløser. Nu kommer fejlen ikke, da "i stedet for trigger" håndterer opdateringsfunktionen for denne komplekse visning. Og når koden er udført, vil placeringen af ​​medarbejder XXX blive opdateret til "Frankrig" fra "Japan."

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;

Kode Forklaring:

  • Kodelinje 49-53: Opdatering af placeringen af ​​"XXX" til 'FRANKRIG'. Det lykkes, fordi triggeren 'INSTEAD OF' har stoppet den aktuelle opdateringserklæring ved visning og udført opdateringen af ​​basistabellen.
  • Kodelinje 55: Bekræftelse af den opdaterede post.

Produktion:

PL / SQL-proceduren blev gennemført

ANSATTES NAVN DEPT_NAME BELIGGENHED
ZZZ HR USA
ÅÅÅ SALG UK
XXX FINANSIEL FRANKRIG

Sammensat udløser

Den sammensatte udløser er en udløser, der giver dig mulighed for at specificere handlinger for hver af fire timingpunkter i det enkelte udløserhus. De fire forskellige timingpunkter, det understøtter, er som nedenfor.

  • FØR UDTALELSE - niveau
  • FØR RAD - niveau
  • EFTER RÆ - niveau
  • EFTER UDTALELSE - niveau

Det giver mulighed for at kombinere handlinger til forskellige timing i den samme trigger.

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;

Syntaks Forklaring:

  • Ovenstående syntaks viser oprettelsen af ​​'COMPOUND' trigger.
  • Deklarativ sektion er almindelig for al eksekveringsblok i triggerkroppen.
  • Disse 4 timingblokke kan være i enhver rækkefølge. Det er ikke obligatorisk at have alle disse 4 timingblokke. Vi kan kun oprette en COMPOUND-udløser til de krævede tidspunkter.

Eksempel 1 : I dette eksempel opretter vi en udløser til automatisk at udfylde lønkolonnen med standardværdien 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;

Kode Forklaring:

  • Kodelinje 2-10 : Oprettelse af sammensat udløser. Den oprettes til timing FØR ROW-niveau for at udfylde lønnen med standardværdien 5000. Dette vil ændre lønnen til standardværdien '5000', før den indsættes i tabellen.
  • Kodelinje 11-14 : Indsæt posten i 'emp' -tabellen.
  • Kodelinje 16 : Bekræftelse af den indsatte post.

Produktion:

Trigger oprettet

PL / SQL-proceduren blev gennemført.

EMP_NAME EMP_NO LØN MANAGER DEPT_NO
CCC 1004 5000 AAA 30

Aktivering og deaktivering af udløsere

Udløsere kan aktiveres eller deaktiveres. For at aktivere eller deaktivere udløseren skal der gives en ALTER (DDL) -erklæring for den udløser, der deaktiverer eller aktiverer den.

Nedenfor er syntaksen for aktivering / deaktivering af udløsere.

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

Syntaks Forklaring:

  • Den første syntaks viser, hvordan du aktiverer / deaktiverer den enkelte udløser.
  • Den anden erklæring viser, hvordan du aktiverer / deaktiverer alle udløsere på en bestemt tabel.

Resumé

I dette kapitel har vi lært om PL / SQL-udløsere og deres fordele. Vi har også lært de forskellige klassifikationer og diskuteret INSTEAD OF trigger og COMPOUND trigger.