Oracle PL/SQL Trigger Tutorial: I stedet for sammensat [Eksempel]

Af: Fiona Brown Fiona Brown
Hours Opdateret

Hvad er Trigger i PL/SQL?

TRIGGERE er gemte programmer, der affyres af Oracle motor automatisk, når DML-udsagn som indsæt, opdatering, sletning udføres på bordet, eller nogle hændelser opstår. Den kode, der skal udføres i tilfælde af en trigger, kan defineres i henhold til kravet. Du kan vælge den begivenhed, som udløseren skal udløses efter, og tidspunktet for udførelsen. Formålet med trigger er at opretholde integriteten af ​​informationer på databasen.

Fordele ved triggere

Følgende er fordelene ved triggere.

  • Generering af nogle afledte kolonneværdier automatisk
  • Håndhævelse af referentiel integritet
  • Hændelseslogning og lagring af information om bordadgang
  • Revision
  • Synchøflig replikering af tabeller
  • Indførelse af sikkerhedsgodkendelser
  • Forebyggelse af ugyldige transaktioner

Typer af triggere i Oracle

Triggere kan klassificeres baseret på følgende parametre.

  • Klassificering baseret på timing
  • FØR Trigger: Den udløses, før den angivne hændelse har fundet sted.
  • EFTER Trigger: Den udløses, efter at den angivne hændelse har fundet sted.
  • I STEDET FOR Trigger: En speciel type. Du vil lære mere om de yderligere emner. (kun for DML)
  • Klassificering baseret på niveau
  • STATEMENT niveau Trigger: Den udløses én gang for den angivne hændelseserklæring.
  • ROW level Trigger: Den udløses for hver post, der blev påvirket i den specificerede hændelse. (kun for DML)
  • Klassificering baseret på Begivenhed
  • DML Trigger: Den udløses, når DML-hændelsen er angivet (INSERT/UPDATE/DELETE)
  • DDL Trigger: Den udløses, når DDL-hændelsen er angivet (CREATE/ALTER)
  • DATABASE Trigger: Den udløses, når databasehændelsen er angivet (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Så hver trigger er kombinationen af ​​ovenstående parametre.

Sådan opretter du trigger

Nedenfor er syntaksen til at oprette en trigger.

Opret 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:

  • Ovenstående syntaks viser de forskellige valgfrie udsagn, der er til stede i triggeroprettelse.
  • BEFORE/AFTER specificerer begivenhedstiderne.
  • INSERT/OPDATERING/LOGON/CREATE/osv. vil angive den hændelse, som udløseren skal udløses for.
  • ON-klausulen vil specificere på hvilket objekt den ovennævnte hændelse er gyldig. For eksempel vil dette være det tabelnavn, som DML-hændelsen kan forekomme på i tilfælde af DML Trigger.
  • Kommandoen "FOR HVER RÆKKE" vil specificere ROW-niveauudløseren.
  • WHEN-klausulen vil specificere den yderligere tilstand, hvori udløseren skal udløses.
  • Erklæringsdelen, udførelsesdelen og undtagelseshåndteringsdelen er den samme som den anden PL/SQL blokke. Erklæringsdel og undtagelseshåndteringsdel er valgfri.

RELATEREDE ARTIKLER

:NY og :GAMMEL klausul

I en trigger på rækkeniveau udløses triggeren for hver relateret række. Og nogle gange er det nødvendigt at kende værdien før og efter DML-sætningen.

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

  • :NEW – Den indeholder en ny værdi for kolonnerne i basistabellen/visningen under triggerudførelsen
  • :OLD – Den holder den gamle værdi af kolonnerne i basistabellen/visningen under triggerudførelsen

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

INSERT OPDATER SLET
:NY GYLDIG GYLDIG Ugyldig. Der er ingen ny værdi i delete tilfælde.
:GAMMEL Ugyldig. Der er ingen gammel værdi i indsatskasse GYLDIG GYLDIG

I STEDET FOR Trigger

"I STEDET FOR trigger" er den specielle type trigger. Det bruges kun i DML-triggere. Det bruges, når en hvilken som helst DML-begivenhed vil forekomme på den komplekse visning.

Overvej et eksempel, hvor en visning er lavet af 3 bundborde. Når en DML-hændelse udsendes over denne visning, bliver den ugyldig, fordi dataene er taget fra 3 forskellige tabeller. Så i denne I STEDET FOR bruges trigger. INSTEAD OF-triggeren bruges til at ændre basistabellerne direkte i stedet for at ændre visningen for den givne hændelse.

Eksempel 1: I dette eksempel skal vi skabe en kompleks visning fra to basistabeller.

  • Table_1 er emp table og
  • Tabel_2 er afdelingstabel.

Så skal vi se, hvordan INSTEAD OF-triggeren bruges til at udstede OPDATERING af placeringsdetaljeerklæringen på denne komplekse visning. Vi skal også se, hvordan :NEW og :OLD er nyttige i triggere.

  • Trin 1: Oprettelse af tabel 'emp' og 'dept' med passende kolonner
  • Trin 2: Udfyldning af tabellen med eksempelværdier
  • Trin 3: Oprettelse af visning for ovenstående oprettede tabel
  • Trin 4: Opdatering af visning før udløseren i stedet for
  • Trin 5: Oprettelse af udløseren i stedet for
  • Trin 6: Opdatering af visning efter i stedet for trigger

Trin 1) Oprettelse af tabel '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: Oprettelse af tabel 'emp'.
  • Kodelinje 8-12: Oprettelse af tabel 'afdeling'.

Produktion

Bord oprettet

Trin 2) Nu, da vi har oprettet tabellen, vil vi udfylde denne tabel med eksempelværdier og oprettelse af visninger for ovenstående tabeller.

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: Indsættelse af data i 'dept' tabel.
  • Kodelinje 20-26: Indsættelse af data i 'emp'-tabellen.

Produktion

PL/SQL procedure afsluttet

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

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: Oprettelse af 'guru99_emp_view'-visning.
  • Kodelinje 33: Forespørger guru99_emp_view.

Produktion

Visning oprettet

ANSATTES NAVN DEPT_NAME ADRESSE
ZZZ HR Danmark
yyy SALES UK
XXX FINANSIEL JAPAN

Trin 4) Opdatering af 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: Opdater placeringen af ​​"XXX" til "FRANCE". Det rejste undtagelsen, fordi DML erklæringer er ikke tilladt i den komplekse visning.

Produktion

ORA-01779: kan ikke ændre en kolonne, der er knyttet til en ikke-nøglebevaret tabel

ORA-06512: på linje 2

Trin 5)For at undgå fejlmødet under opdateringsvisningen i det foregående trin, vil vi i dette trin bruge "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: Oprettelse af INSTEAD OF trigger for 'UPDATE' begivenhed på 'guru99_emp_view' visningen på ROW niveau. Den indeholder opdateringserklæringen for at opdatere placeringen i basistabellen 'dept'.
  • Kodelinje 44: Opdateringssætning 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 trigger. Nu kommer fejlen ikke, da "i stedet for trigger" vil håndtere opdateringsoperationen af ​​denne komplekse visning. Og når koden er udført, vil medarbejder XXX's placering blive opdateret til "Frankrig" 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: Opdatering af placeringen af ​​"XXX" til "FRANCE". Det lykkes, fordi 'INSTEAD OF'-udløseren har stoppet den faktiske opdateringssætning og udført opdateringen af ​​basistabel.
  • Kodelinje 55: Bekræfter den opdaterede post.

Output:

PL/SQL-proceduren er gennemført

ANSATTES NAVN DEPT_NAME ADRESSE
ZZZ HR Danmark
yyy SALES UK
XXX FINANSIEL FRANKRIG

Sammensat trigger

Den sammensatte udløser er en udløser, der giver dig mulighed for at angive handlinger for hvert af fire tidspunkter i den enkelte udløserkropp. De fire forskellige tidspunkter, den understøtter, er som nedenfor.

  • FØR UDTALELSE – niveau
  • FØR RÆKKE – niveau
  • EFTER RÆKKE – niveau
  • EFTER UDTALELSE – niveau

Det giver mulighed for at kombinere handlingerne for forskellig timing i den samme trigger.

Sammensat 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:

  • Ovenstående syntaks viser oprettelsen af ​​'COMPOUND' trigger.
  • Deklarativ sektion er fælles for alle udførelsesblokken i triggerlegemet.
  • Disse 4 timingblokke kan være i enhver rækkefølge. Det er ikke obligatorisk at have alle disse 4 tidsblokke. Vi kan kun oprette en COMPOUND-udløser for de tider, der kræves.

Eksempel 1: I dette eksempel skal vi oprette en trigger for automatisk at udfylde lønkolonnen med standardværdien 5000.

Sammensat 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: Oprettelse af sammensat trigger. Den er oprettet til timing FØR RÆKKE-niveau for at udfylde lønnen med standardværdien 5000. Dette vil ændre lønnen til standardværdien '5000', før posten indsættes i tabellen.
  • Kodelinje 11-14: Indsæt posten i 'emp'-tabellen.
  • Kodelinje 16: Bekræfter den indsatte post.

Output:

Trigger oprettet

PL/SQL-proceduren er gennemført.

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

Aktivering og deaktivering af triggere

Udløsere kan aktiveres eller deaktiveres. For at aktivere eller deaktivere triggeren skal der gives en ALTER (DDL)-sætning for triggeren, der deaktiverer eller aktiverer den.

Nedenfor er syntaksen for aktivering/deaktivering af triggere.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Syntaks forklaring:

  • Den første syntaks viser, hvordan man aktiverer/deaktiverer den enkelte trigger.
  • Den anden sætning viser, hvordan du aktiverer/deaktiverer alle triggere på en bestemt tabel.

Resumé

I dette kapitel har vi lært om PL/SQL-triggere og deres fordele. Vi har også lært de forskellige klassifikationer og diskuteret I STEDET FOR trigger og COMPOUND trigger.

Du kan lide: