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

Innholdsfortegnelse:

Anonim

Hva er utløser i PL / SQL?

TRIGGERS er lagrede programmer som blir sparket av Oracle-motor automatisk når DML-utsagn som innsetting, oppdatering, sletting kjøres på bordet eller noen hendelser inntreffer. Koden som skal utføres i tilfelle en utløser kan defineres i henhold til kravet. Du kan velge hendelsen som utløseren må avfyres på og tidspunktet for utførelsen. Hensikten med trigger er å opprettholde integriteten til informasjonen i databasen.

I denne veiledningen vil du lære-

  • Fordeler med utløsere
  • Typer av utløsere i Oracle
  • Hvordan lage utløser
  • : NY og: GAMLE Klausul
  • I stedet for utløser
  • Sammensatt utløser

Fordeler med utløsere

Følgende er fordelene med utløsere.

  • Genererer noen avledede kolonneverdier automatisk
  • Håndheve referanseintegritet
  • Hendelseslogging og lagring av informasjon om tilgang til bord
  • Revisjon
  • Synkron replikering av tabeller
  • Pålegge sikkerhetstillatelser
  • Forebygge ugyldige transaksjoner

Typer av utløsere i Oracle

Utløsere kan klassifiseres basert på følgende parametere.

  • Klassifisering basert på tidspunktet
    • FØR utløseren: Den utløses før den spesifiserte hendelsen har skjedd.
    • ETTER utløser: Den utløses etter at den angitte hendelsen har skjedd.
    • I stedet for utløser: En spesiell type. Du vil lære mer om de videre emnene. (bare for DML)
  • Klassifisering basert på nivå
    • UTTALELSE nivå Utløser: Den utløses en gang for den angitte hendelseserklæringen.
    • ROW-nivå utløser: Den utløses for hver post som ble berørt i den angitte hendelsen. (bare for DML)
  • Klassifisering basert på arrangementet
    • DML-utløser: Den utløses når DML-hendelsen er spesifisert (INSERT / UPDATE / DELETE)
    • DDL-utløser: Den utløses når DDL-hendelsen er spesifisert (CREATE / ALTER)
    • DATABASE-utløser: Den utløses når databasehendelsen er spesifisert (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Så hver trigger er kombinasjonen av parametrene ovenfor.

Hvordan lage utløser

Nedenfor er syntaksen for å lage en utløser.

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

Syntaks Forklaring:

  • Ovennevnte syntaks viser de forskjellige valgfrie setningene som er tilstede i utløseren.
  • FØR / ETTER vil spesifisere tidspunktet for hendelsen.
  • INSERT / UPDATE / LOGON / CREATE / etc. vil spesifisere hendelsen som utløseren må utløses for.
  • ON-ledd vil spesifisere hvilket objekt ovennevnte hendelse er gyldig. For eksempel vil dette være tabellnavnet som DML-hendelsen kan forekomme i tilfelle DML Trigger.
  • Kommandoen "FOR HVER RAD" vil spesifisere ROW-nivåutløseren.
  • NÅR klausul vil spesifisere tilleggstilstanden der utløseren må utløses.
  • Deklarasjonsdelen, utførelsesdel, unntakshåndteringsdel er den samme som de andre PL / SQL-blokkene. Deklarasjonsdel og unntakshåndteringsdel er valgfri.

: NY og: GAMLE Klausul

I en radnivåutløser utløses utløseren for hver relatert rad. Noen ganger kreves det å vite verdien før og etter DML-setningen.

Oracle har gitt to ledd i RECORD-nivåutløseren for å holde disse verdiene. Vi kan bruke disse leddene til å referere til de gamle og nye verdiene inne i utløserkroppen.

  • : NYTT - Den inneholder en ny verdi for kolonnene i basistabellen / visningen under utførelsen av utløseren
  • : GAMLE - Den har gammel verdi av kolonnene i basistabellen / visningen under utførelsen av utløseren

Denne paragrafen bør brukes basert på DML-hendelsen. Tabellen nedenfor vil spesifisere hvilken paragraf som er gyldig for hvilken DML-setning (INSERT / UPDATE / DELETE).

SETT INN OPPDATER SLETT
:NY GYLDIG GYLDIG UGYLDIG. Det er ingen ny verdi i slettingssaken.
:GAMMEL UGYLDIG. Det er ingen gammel verdi i innleggssaken GYLDIG GYLDIG

I stedet for utløser

"INSTEAD OF trigger" er den spesielle typen trigger. Den brukes bare i DML-utløsere. Den brukes når en DML-hendelse skal skje i den komplekse visningen.

Tenk på et eksempel der en visning er laget av tre basistabeller. Når en DML-hendelse blir utstedt over denne visningen, blir den ugyldig fordi dataene er hentet fra 3 forskjellige tabeller. Så i denne INSTEAD OF trigger brukes. INSTEAD OF-utløseren brukes til å modifisere basistabellene direkte i stedet for å endre visningen for den gitte hendelsen.

Eksempel 1 : I dette eksemplet skal vi lage en kompleks visning fra to basistabeller.

  • Table_1 er emp-tabell og
  • Table_2 er avdelingstabell.

Deretter skal vi se hvordan INSTEAD OF-utløseren brukes til å utstede OPPDATERING detaljinformasjonen om denne komplekse visningen. Vi skal også se hvordan: NEW og: OLD er nyttig i utløsere.

  • Trinn 1: Opprette tabell 'emp' og 'dept' med passende kolonner
  • Trinn 2: Fyll ut tabellen med prøveverdier
  • Trinn 3: Opprette visning for tabellen ovenfor
  • Trinn 4: Oppdater visningen før i stedet for utløseren
  • Trinn 5: Opprettelse av stedet for trigger
  • Trinn 6: Oppdater visningen etter i stedet for utløseren

Trinn 1) Opprette tabell '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 : Opprettelse av tabell 'emp'.
  • Kodelinje 8-12 : Oppretting av tabell 'avd.'

Produksjon

Tabell opprettet

Trinn 2) Nå siden vi har opprettet tabellen, vil vi fylle ut denne tabellen med eksempelverdier og opprette visninger for tabellene ovenfor. 

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 : Sette inn data i 'dept' tabellen.
  • Kodelinje 20-26: Sette inn data i 'emp' tabellen.

Produksjon

PL / SQL-prosedyre fullført

Trinn 3) Opprette en visning for tabellen som er opprettet ovenfor. 

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: Oppretting av visningen 'guru99_emp_view'.
  • Kodelinje 33: Spørring av guru99_emp_view.

Produksjon

Visning opprettet

ARBEIDSTAKERS NAVN DEPT_NAME PLASSERING
ZZZ HR USA
ÅÅÅ SALG Storbritannia
XXX FINANSIELL JAPAN

Trinn 4) Oppdater visningen før i stedet for utløseren.

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

Kode Forklaring

  • Kodelinje 34-38: Oppdater plasseringen til "XXX" til 'FRANCE'. Det hevet unntaket fordi DML-uttalelsene ikke er tillatt i den komplekse visningen.

Produksjon

ORA-01779: kan ikke endre en kolonne som tilordnes til en ikke-nøkkelbevart tabell

ORA-06512: på linje 2

Trinn 5) For å unngå feilmeldingen under oppdateringsvisningen i forrige trinn, skal vi i dette trinnet bruke "i stedet for utløser".

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: Oppretting av INSTEAD OF trigger for 'UPDATE' hendelse i 'guru99_emp_view' visningen på ROW-nivå. Den inneholder oppdateringserklæringen for å oppdatere plasseringen i basistabellen 'avd.'.
  • Kodelinje 44: Oppdateringserklæring bruker ': NEW' og ': OLD' for å finne verdien av kolonnene før og etter oppdateringen.

Produksjon

Utløser opprettet

Trinn 6) Oppdater visningen etter i stedet for utløseren. Nå kommer ikke feilen da "i stedet for utløser" vil håndtere oppdateringen av denne komplekse visningen. Og når koden har utført, vil lokasjonen til ansatt XXX bli oppdatert til "Frankrike" 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: Oppdatering av plasseringen av "XXX" til 'FRANCE'. Det er vellykket fordi 'INSTEAD OF' -utløseren har stoppet den faktiske oppdateringserklæringen på visningen og utført oppdateringen av basistabellen.
  • Kodelinje 55: Verifisere den oppdaterte posten.

Produksjon:

PL / SQL-prosedyre fullført

ARBEIDSTAKERS NAVN DEPT_NAME PLASSERING
ZZZ HR USA
ÅÅÅ SALG Storbritannia
XXX FINANSIELL FRANKRIKE

Sammensatt utløser

Den sammensatte utløseren er en utløser som lar deg spesifisere handlinger for hvert av de fire timingpunktene i den eneste utløserkroppen. De fire forskjellige tidspunktene den støtter er som nedenfor.

  • FØR UTTALELSE - nivå
  • FØR RAD - nivå
  • ETTER RAD - nivå
  • ETTER UTTALELSE - nivå

Det gir muligheten til å kombinere handlingene for forskjellig timing i 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:

  • Ovennevnte syntaks viser etableringen av 'COMPOUND' -utløseren.
  • Deklarativ seksjon er vanlig for all utførelsesblokk i utløserhuset.
  • Disse 4 timingblokkene kan være i hvilken som helst sekvens. Det er ikke obligatorisk å ha alle disse 4 timingblokkene. Vi kan bare opprette en COMPOUND-utløser for de nødvendige tidspunktene.

Eksempel 1 : I dette eksemplet skal vi lage en utløser for automatisk å fylle ut lønnskolonnen med standardverdien 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 : Opprettelse av sammensatt utløser. Den er opprettet for timing FØR RAD-nivå for å fylle ut lønnen med standardverdien 5000. Dette vil endre lønnen til standardverdien '5000' før du legger inn posten i tabellen.
  • Kodelinje 11-14 : Sett inn posten i 'emp' tabellen.
  • Kodelinje 16 : Verifisere den innsatte posten.

Produksjon:

Utløser opprettet

PL / SQL-prosedyre fullført.

EMP_NAME EMP_NO LØNN SJEF DEPT_NO
CCC 1004 5000 AAA 30

Aktivere og deaktivere utløsere

Utløsere kan aktiveres eller deaktiveres. For å aktivere eller deaktivere utløseren, må det gis en ALTER (DDL) uttalelse for utløseren som deaktiverer eller aktiverer den.

Nedenfor er syntaksen for aktivering / deaktivering av utløserne.

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

Syntaks Forklaring:

  • Den første syntaksen viser hvordan du aktiverer / deaktiverer enkeltutløseren.
  • Den andre setningen viser hvordan du aktiverer / deaktiverer alle utløserne på et bestemt bord.

Sammendrag

I dette kapittelet har vi lært om PL / SQL-utløsere og fordelene deres. Vi har også lært de forskjellige klassifiseringene og diskutert INSTEAD OF trigger og COMPOUND trigger.