Oracle PL / SQL Trigger Tutorial: I Stedet For, Sammensatte [Eksempel]

I denne opplæringen vil du lære –

• Fordeler Med Triggere
• Typer Triggere I Oracle
• Hvordan Lage Trigger
• : NY Og: GAMMEL Klausul
• I STEDET For Trigger
• Sammensatte Trigger

Fordeler Med Triggere

følgende er fordelene med triggere.

• Generering av noen avledede kolonneverdier automatisk
• Håndheving av referanseintegritet
• Hendelseslogging og lagring av informasjon om tabelltilgang
• Overvåking
• Synkron replikering av tabeller
• Innføring av sikkerhetsautorisasjoner
• Forhindre ugyldige transaksjoner

Typer Utløsere I Oracle

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

• Klassifisering basert på tidspunktet
  • Før Utløseren: den brenner før den angitte hendelsen har skjedd.
  • AFTER Trigger: den utløses etter at den angitte hendelsen har oppstått.
  • I STEDET For Trigger: en spesiell type. Du vil lære mer om de videre emnene. (kun FOR DML )
• Klassifisering basert på NIVÅ
  • SETNINGSNIVÅ Trigger: det fyrer av en gang for den angitte hendelses setningen.
  • ROW level Trigger: den brenner for hver post som ble påvirket i den angitte hendelsen. (bare FOR DML)
• Klassifisering basert på Hendelsen
  • DML-Utløser: den utløses når dml-hendelsen er angitt (SETT INN/OPPDATER/SLETT)
  • DDL-Utløser: DEN brenner når databasehendelsen er spesifisert (PÅLOGGING/AVLOGGING/OPPSTART/AVSLUTNING)

Slik at hver utløser er kombinasjonen av parametrene ovenfor.

Hvordan Lage Trigger

Nedenfor er syntaksen for å lage en trigger.

CREATE TRIGGER <trigger_name> ON<name of underlying object> DECLARE<Declaration part>BEGIN<Execution part> EXCEPTION<Exception handling part> END;

Syntaksforklaring:

• syntaksen ovenfor viser de forskjellige valgfrie setningene som finnes i utløseropprettelsen.
• FØR / ETTER vil angi hendelsestidspunkt.
• SETT INN / OPPDATER / LOGG PÅ/OPPRETT / etc. vil spesifisere hendelsen som utløseren må avfyres.
• on klausul vil spesifisere på hvilket objekt den ovennevnte hendelsen er gyldig. Dette vil For eksempel være tabellnavnet SOM dml-hendelsen kan oppstå i TILFELLE AV DML-Utløser.
• Kommando «for HVER RAD» vil spesifisere RADNIVÅUTLØSEREN.
• NÅR klausulen vil angi tilleggsbetingelsen der utløseren må brann.
• deklarasjonsdelen, utførelsesdelen, unntakshåndteringsdelen er den samme som for de andre pl / SQL-blokkene. Erklæring del og unntak håndtering del er valgfritt.

:NY Og: GAMMEL Klausul

i en radnivåutløser utløses utløseren for hver relatert rad. Og noen ganger er det nødvendig å vite verdien før OG etter dml-setningen.

Oracle har gitt to klausuler i UTLØSEREN PÅ POSTNIVÅ for å beholde disse verdiene. Vi kan bruke disse klausulene til å referere til de gamle og nye verdiene i utløserkroppen.

• :NY – DEN har en ny verdi for kolonnene i grunntabellen/visningen under utløserutførelsen
• :GAMMEL-DEN har gammel verdi for kolonnene i grunntabellen / visningen under utløserutførelsen

denne klausulen skal brukes basert på dml-hendelsen. Tabellen nedenfor angir hvilken klausul som er gyldig for hvilken dml-setning (SETT INN/OPPDATER/SLETT).

	SETT inn	OPPDATERING	SLETT
:NY	GYLDIG	GYLDIG	UGYLDIG. Det er ingen ny verdi i slett sak.
:GAMMEL	UGYLDIG. DET er ingen gammel verdi i sett inn saken	GYLDIG

I STEDET For Trigger

«I STEDET for trigger» er den spesielle typen trigger. Den brukes kun I DML-utløsere. Den brukes når EN DML-hendelse skal skje på den komplekse visningen.

Tenk på et eksempel der en visning er laget av 3 grunntabeller. Når EN DML-hendelse utstedes over denne visningen, blir den ugyldig fordi dataene er hentet fra 3 forskjellige tabeller. Så i DETTE I STEDET for trigger brukes. I STEDET for trigger brukes til å endre basistabellene direkte i stedet for å endre visningen for den gitte hendelsen.

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

• Table_1 er emp-tabell og

• Table_2 er avdelingstabell.

så skal VI se HVORDAN i STEDET for trigger brukes TIL å utstede OPPDATERE plasseringen detalj uttalelse på denne komplekse visningen. Vi skal også se hvordan: NY OG: GAMMEL er nyttig i utløsere.

• Trinn 1: Opprette tabell ’emp’ Og ‘dept’ med passende kolonner
• Trinn 2: Fylle tabellen med eksempelverdier
• Trinn 3: Opprette visning for tabellen ovenfor opprettet
• Trinn 4: Oppdatering av visningen før stedet-av trigger
• Trinn 5: Opprettelse av stedet-av trigger
• Trinn 6: Oppdatering av visningen etter stedet-av trigger

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

kodeforklaring

• kodelinje 1-7: Tabell ‘ emp ‘ skapelse.
• Kode linje 8-12: Tabell ‘dept’ opprettelse.

Output

Tabell Opprettet

Trinn 2) nå siden vi har opprettet tabellen, vil vi fylle ut denne tabellen med utvalgsverdier og Opprettelse Av 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;/

Kodeforklaring

• Kodelinje 13-19: Sette inn data i ‘dept’ – tabellen.
• Kodelinje 20-26: Sette inn data i ‘ emp ‘ – tabellen.

Utgang

PL/SQL-prosedyre fullført

Trinn 3) Opprette en visning for tabellen ovenfor opprettet.

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;

Kodeforklaring

• Kodelinje 27-32: opprettelse av ‘guru99_emp_view’ visning.

• Kode linje 33: Spørring guru99_emp_view.

Output

View created

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
YYY	SALES	UK
XXX	FINANCIAL	JAPAN

Step 4) Update of view before instead-of trigger.

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

Code Explanation

• Code line 34-38: Update the location of «XXX» to ‘FRANCE’. Det hevet unntaket fordi DML-setningene ikke er tillatt i den komplekse visningen. ORA-01779: kan ikke endre en kolonne som tilordner til et ikke-nøkkelbevart bord

  ORA-06512: på linje 2

  Trinn 5)for å unngå at feilen oppstår under oppdateringsvisning i forrige trinn, i dette trinnet skal vi bruke «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;/

  Kodeforklaring

  • Kodelinje 39: Opprettelse AV I STEDET for trigger for’ UPDATE ‘- hendelsen på’ guru99_emp_view ‘ – visningen på RADNIVÅ. Den inneholder update-setningen for å oppdatere plasseringen i basistabellen ‘dept’.
  • Kodelinje 44: Update-setning bruker’: NY ‘og’: GAMMEL ‘ for å finne verdien av kolonner før og etter oppdateringen.

  Utgang

  Trigger Opprettet

  Trinn 6) Oppdatering av visning etter i stedet-av trigger. Nå kommer feilen ikke som «i stedet for trigger» vil håndtere oppdateringsoperasjonen av denne komplekse visningen. Og når koden har utført plasseringen av ansatt XXX vil bli oppdatert til «Frankrike» Fra » Japan.»

  

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

  SELECT * FROM guru99_emp_view;

  Kodeforklaring:

  • Kodelinje 49-53: oppdatering av plasseringen av «xxx» til «frankrike». Det er vellykket fordi utløseren ‘I STEDET for’ har stoppet den faktiske oppdateringssetningen på visning og utført basistabelloppdateringen.
  • Kodelinje 55: Kontrollere den oppdaterte posten.

  Utgang:

  PL/SQL-prosedyre fullført

  finans FRANKRIKE

  ANSATTNAVN	DEPT_NAME	PLASSERING
  ZZZ	HR	usa
  		salg

  sammensatt utløser

  sammensatte trigger er en trigger SOM LAR deg angi handlinger for hver av fire timing poeng i enkelt utløser kroppen. De fire forskjellige timing punkt den støtter er som nedenfor.

  • Før SETNINGSNIVÅ
  • FØR RADNIVÅ
  • ETTER RADNIVÅ
  • Etter SETNINGSNIVÅ

  det gir mulighet til å kombinere handlingene for forskjellig timing i samme utløser.

  

  CREATE TRIGGER <trigger_name> FORON <name of underlying object><Declarative part>‭ ‬BEFORE STATEMENT ISBEGIN<Execution part>;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN<Execution part>;END EACH ROW;AFTER EACH ROW ISBEGIN<Execution part>;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN<Execution part>;END AFTER STATEMENT;END;

  Syntaksforklaring:

  • syntaksen ovenfor viser opprettelsen av «SAMMENSATT» utløser.Deklarativ seksjon er vanlig for alle utførelsesblokken i utløserkroppen.
  • Disse 4 timing blokkene kan være i hvilken som helst rekkefølge. Det er ikke obligatorisk å ha alle disse 4 timing blokker. Vi kan lage EN SAMMENSATT utløser bare for timings som kreves.

  Eksempel 1: I dette eksemplet skal vi opprette en utløser for å automatisk fylle ut lønnskolonnen med standardverdien 5000.

  

  CREATE TRIGGER emp_trig FOR INSERT ON empCOMPOUND TRIGGER BEFORE EACH ROW IS BEGIN: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;

  kodeforklaring:

  • kodelinje 2-10: opprettelse av sammensatt utløser. Den er opprettet FOR timing FØR RADNIVÅ for å fylle lønnen med standardverdi 5000. Dette vil endre lønnen til standardverdien ‘5000’ før du setter inn posten i tabellen.
  • Kodelinje 11-14: Sett inn posten i ‘ emp ‘ – tabellen.
  • Kodelinje 16: Verifiserer den innsatte posten.

  Utgang:

  Utløser opprettet

  pl / SQL-prosedyren er fullført.

  EMP_NAME	EMP_NO	LØNN	MANAGER
  CCC	1004	5000	aaa	30

  aktivering og DEAKTIVERING av utløsere

  utløsere kan aktiveres eller deaktiveres. For å aktivere eller deaktivere utløseren må EN ALTER (DDL) – setning gis for utløseren som deaktiverer eller aktiverer den.

  nedenfor er syntaksen for å aktivere / deaktivere utløserne.

  ALTER TRIGGER <trigger_name> ;ALTER TABLE <table_name> ALL TRIGGERS;

  Syntaksforklaring:

  • 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 triggere og deres fordeler. Vi har også lært de forskjellige klassifikasjonene OG diskutert I STEDET for trigger og COMPOUND trigger.