Oracle PL/SQL-utløseropplæring: i stedet for sammensatt [eksempel]

by: Fiona Brown Fiona Brown
Hours oppdatert

Hva er Trigger i PL/SQL?

TRIGGERE er lagrede programmer som utløses av Oracle motor automatisk når DML-setninger som sett inn, oppdater, slett utfø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 der utløseren skal utløses og tidspunktet for utførelsen. Hensikten med trigger er å opprettholde integriteten til informasjonen i databasen.

Fordeler med triggere

Følgende er fordelene med triggere.

  • Genererer noen avledede kolonneverdier automatisk
  • Håndheve referensiell integritet
  • Hendelseslogging og lagring av informasjon om bordtilgang
  • Revisjon
  • Synchronous replikering av tabeller
  • Påtvinge sikkerhetsautorisasjoner
  • Forhindrer ugyldige transaksjoner

Typer triggere i Oracle

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

  • Klassifisering basert på timing
  • FØR Trigger: Den utløses før den angitte hendelsen har skjedd.
  • ETTER Trigger: Den utløses etter at den angitte hendelsen har skjedd.
  • I STEDET FOR Trigger: En spesiell type. Du vil lære mer om de videre temaene. (kun for DML)
  • Klassifisering basert på nivå
  • STATEMENT nivå Trigger: Den utløses én gang for den angitte hendelsessetningen.
  • ROW level Trigger: Den utløses for hver post som ble påvirket i den angitte hendelsen. (kun for DML)
  • Klassifisering basert på Event
  • DML Trigger: Den utløses når DML-hendelsen er spesifisert (INSERT/UPDATE/DELETE)
  • DDL Trigger: Den utløses når DDL-hendelsen er spesifisert (CREATE/ALTER)
  • DATABASE-utløser: Den utløses når databasehendelsen er spesifisert (LOGG PÅ/LOGG AV/STARTUP/SHUTDOWN)

Så hver trigger er kombinasjonen av parameterne ovenfor.

Hvordan lage trigger

Nedenfor er syntaksen for å lage en trigger.

Lag Trigger

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;

Syntaks forklaring:

  • Syntaksen ovenfor viser de forskjellige valgfrie setningene som er til stede i utløseroppretting.
  • FØR/ETTER vil spesifisere hendelsestidene.
  • INSERT/UPDATE/LOGON/CREATE/etc. vil spesifisere hendelsen som utløseren må utløses for.
  • ON-klausulen vil spesifisere på hvilket objekt den ovennevnte hendelsen er gyldig. For eksempel vil dette være tabellnavnet som DML-hendelsen kan oppstå på i tilfelle DML Trigger.
  • Kommando "FOR HVER ROW" vil spesifisere ROW-nivåutløseren.
  • WHEN-klausulen spesifiserer tilleggstilstanden der utløseren må utløses.
  • Deklarasjonsdelen, utførelsesdelen og unntakshåndteringsdelen er den samme som den andre PL/SQL-blokker. Erklæringsdel og unntakshåndteringsdel er valgfri.

RELATERTE ARTIKLER

:NY og :GAMMEL klausul

I en utløser på radnivå 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 RECORD-nivåutløseren for å holde disse verdiene. Vi kan bruke disse klausulene til å referere til de gamle og nye verdiene inne i utløserkroppen.

  • :NYHET – Den har en ny verdi for kolonnene i basistabellen/visningen under utløserkjøringen
  • :GAMMEL – Den holder den gamle verdien av kolonnene i basistabellen/visningen under utløserkjøringen

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

INSERT OPPDATERING SLETT
:NY GYLDIG GYLDIG Ugyldig. Det er ingen ny verdi i delete case.
:GAMMEL Ugyldig. Det er ingen gammel verdi i innleggsboks GYLDIG GYLDIG

I STEDET FOR Trigger

"INSTEAD OF trigger" er den spesielle typen trigger. Den brukes bare i DML-utløsere. Den brukes når en DML-hendelse skal oppstå 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 denne brukes I STEDET FOR trigger. 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 grunntabeller.

  • Table_1 er emp table og
  • Tabell_2 er avdelingstabell.

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

  • Trinn 1: Lage tabell 'emp' og 'dept' med passende kolonner
  • Trinn 2: Fyll tabellen med eksempelverdier
  • Trinn 3: Opprette visning for tabellen ovenfor opprettet
  • Trinn 4: Oppdatering av visning før utløseren i stedet for
  • Trinn 5: Oppretting av utløseren i stedet for
  • Trinn 6: Oppdatering av visning etter i stedet for trigger

Trinn 1) Opprette tabell 'emp' og 'dept' med passende kolonner

I STEDET FOR Trigger

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: Oppretting av tabell 'emp'.
  • Kodelinje 8-12: Oppretting av tabell 'avdeling'.

Produksjon

Tabell opprettet

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

I STEDET FOR Trigger

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

Kode Forklaring

  • Kodelinje 13-19: Setter inn data i 'avdeling'-tabellen.
  • Kodelinje 20-26: Setter inn data i 'emp'-tabellen.

Produksjon

PL/SQL-prosedyre ferdig

Trinn 3) Opprette en visning for tabellen ovenfor opprettet.

I STEDET FOR Trigger

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;

Kode Forklaring

  • Kodelinje 27-32: Oppretting av 'guru99_emp_view'-visning.
  • Kodelinje 33: Spørrende guru99_emp_view.

Produksjon

Visning opprettet

EMPLOYEE_NAME DEPT_NAME OPPMØTE
ZZZ HR Norge
ÅÅÅ SALGS UK
XXX FINANSIELL JAPAN

Trinn 4) Oppdatering av visning før i stedet for trigger.

I STEDET FOR Trigger

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

Kode Forklaring

  • Kodelinje 34-38: Oppdater plasseringen av "XXX" til "FRANCE". Det reiste unntaket fordi DML uttalelser er ikke 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å feilstøtet under oppdateringsvisningen i forrige trinn, skal vi i dette trinnet bruke "i stedet for trigger."

I STEDET FOR Trigger

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

Kode Forklaring

  • Kodelinje 39: Oppretting av INSTEAD OF-utløseren for «UPDATE»-hendelsen i «guru99_emp_view»-visningen på ROW-nivå. Den inneholder oppdateringssetningen for å oppdatere plasseringen i basistabellen 'avdeling'.
  • Kodelinje 44: Oppdateringssetningen bruker ':NEW' og ': OLD' for å finne verdien av kolonner før og etter oppdateringen.

Produksjon

Utløser opprettet

Trinn 6) Oppdatering av visning etter i stedet for trigger. Nå kommer ikke feilen da "i stedet for utløser" vil håndtere oppdateringsoperasjonen til denne komplekse visningen. Og når koden er utført, vil plasseringen til ansatt XXX bli oppdatert til "Frankrike" fra "Japan."

I STEDET FOR Trigger

BEGIN
UPDATE 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 "FRANCE". Det er vellykket fordi "INSTEAD OF"-utløseren har stoppet den faktiske oppdateringssetningen på visning og utført oppdateringen av basistabellen.
  • Kodelinje 55: Bekrefter den oppdaterte posten.

Utgang:

PL/SQL-prosedyre fullført

EMPLOYEE_NAME DEPT_NAME OPPMØTE
ZZZ HR Norge
ÅÅÅ SALGS UK
XXX FINANSIELL FRANKRIKE

Sammensatt trigger

Sammensatt utløser er en utløser som lar deg spesifisere handlinger for hvert av fire tidspunkter i enkeltutlø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 mulighet for å kombinere handlingene for forskjellig timing i samme trigger.

Sammensatt trigger

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;

Syntaks forklaring:

  • Syntaksen ovenfor viser opprettelsen av 'COMPOUND'-utløseren.
  • Deklarativ del er felles for alle utførelsesblokkene i utløserkroppen.
  • Disse 4 tidsblokkene kan være i hvilken som helst rekkefølge. Det er ikke obligatorisk å ha alle disse 4 tidsblokkene. Vi kan lage en COMPOUND-utløser bare for de tidspunktene som kreves.

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

Sammensatt trigger

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;

Kodeforklaring:

  • Kodelinje 2-10: Oppretting av sammensatt trigger. Den er opprettet for timing FØR RAD-nivå for å fylle lønnen med standardverdien 5000. Dette vil endre lønnen til standardverdien '5000' før posten settes inn i tabellen.
  • Kodelinje 11-14: Sett inn posten i 'emp'-tabellen.
  • Kodelinje 16: Bekrefter den innsatte posten.

Utgang:

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 triggeren, må en ALTER (DDL)-setning gis for triggeren som deaktiverer eller aktiverer den.

Nedenfor er syntaksen for å aktivere/deaktivere utløsere.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [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øsere på en bestemt tabell.

Sammendrag

I dette kapittelet har vi lært om PL/SQL-utløsere og deres fordeler. Vi har også lært oss de forskjellige klassifikasjonene og diskutert I STEDET FOR trigger og COMPOUND trigger.

Oppsummer dette innlegget med: