Oracle PL / SQL Trigger-zelfstudie: in plaats van, samengesteld (voorbeeld)

Inhoudsopgave:

Anonim

Wat is Trigger in PL / SQL?

TRIGGERS zijn opgeslagen programma's die automatisch worden geactiveerd door de Oracle-engine wanneer DML-verklaringen zoals invoegen, bijwerken en verwijderen op de tafel worden uitgevoerd of als zich bepaalde gebeurtenissen voordoen. De code die moet worden uitgevoerd in het geval van een trigger, kan worden gedefinieerd volgens de vereiste. U kunt de gebeurtenis kiezen waarop de trigger moet worden geactiveerd en de timing van de uitvoering. Het doel van trigger is om de integriteit van de informatie in de database te behouden.

In deze tutorial leer je-

  • Voordelen van triggers
  • Typen triggers in Oracle
  • Hoe een trigger te maken
  • : NIEUW en: OUDE clausule
  • IN PLAATS VAN Trigger
  • Samengestelde trigger

Voordelen van triggers

Hieronder volgen de voordelen van triggers.

  • Automatisch enkele afgeleide kolomwaarden genereren
  • Referentiële integriteit afdwingen
  • Gebeurtenisregistratie en opslag van informatie over tafeltoegang
  • Auditing
  • Synchrone replicatie van tabellen
  • Beveiligingsautorisaties opleggen
  • Voorkomen van ongeldige transacties

Typen triggers in Oracle

Triggers kunnen worden geclassificeerd op basis van de volgende parameters.

  • Classificatie op basis van de timing
    • BEFORE Trigger: het wordt geactiveerd voordat de opgegeven gebeurtenis heeft plaatsgevonden.
    • NA Trigger: het wordt geactiveerd nadat de opgegeven gebeurtenis heeft plaatsgevonden.
    • IN PLAATS VAN Trigger: een speciaal type. U leert meer over de verdere onderwerpen. (alleen voor DML)
  • Classificatie op basis van het niveau
    • STATEMENT-niveau Trigger: het wordt één keer geactiveerd voor de opgegeven gebeurtenisinstructie.
    • ROW-niveau Trigger: het wordt geactiveerd voor elk record dat is beïnvloed in de opgegeven gebeurtenis. (alleen voor DML)
  • Classificatie op basis van het evenement
    • DML-trigger: het wordt geactiveerd wanneer de DML-gebeurtenis is opgegeven (INSERT / UPDATE / DELETE)
    • DDL-trigger: het wordt geactiveerd wanneer de DDL-gebeurtenis is opgegeven (CREATE / ALTER)
    • DATABASE-trigger: het wordt geactiveerd wanneer de databasegebeurtenis is opgegeven (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Dus elke trigger is de combinatie van bovenstaande parameters.

Hoe een trigger te maken

Hieronder staat de syntaxis voor het maken van een trigger.

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

Syntaxis Uitleg:

  • De bovenstaande syntaxis toont de verschillende optionele instructies die aanwezig zijn bij het maken van triggers.
  • BEFORE / AFTER zal de timing van het evenement specificeren.
  • INSERT / UPDATE / LOGON / CREATE / enz. specificeert de gebeurtenis waarvoor de trigger moet worden geactiveerd.
  • ON-clausule specificeert op welk object de bovengenoemde gebeurtenis geldig is. Dit is bijvoorbeeld de tabelnaam waarop de DML-gebeurtenis kan plaatsvinden in het geval van DML Trigger.
  • Het commando "FOR EACH ROW" specificeert de ROW-niveau-trigger.
  • WHEN-clausule specificeert de aanvullende voorwaarde waarin de trigger moet worden geactiveerd.
  • Het declaratiegedeelte, het uitvoeringsgedeelte, het uitzonderingsafhandelingsgedeelte is hetzelfde als dat van de andere PL / SQL-blokken. Het declaratiegedeelte en het gedeelte voor het afhandelen van uitzonderingen zijn optioneel.

: NIEUW en: OUDE clausule

In een trigger op rijniveau wordt de trigger geactiveerd voor elke gerelateerde rij. En soms is het vereist om de waarde voor en na de DML-instructie te kennen.

Oracle heeft twee clausules in de RECORD-niveau-trigger voorzien om deze waarden vast te houden. We kunnen deze clausules gebruiken om te verwijzen naar de oude en nieuwe waarden in de triggerbody.

  • : NIEUW - Het bevat een nieuwe waarde voor de kolommen van de basistabel / weergave tijdens de uitvoering van de trigger
  • : OLD - Het bevat de oude waarde van de kolommen van de basistabel / weergave tijdens de uitvoering van de trigger

Deze clausule moet worden gebruikt op basis van de DML-gebeurtenis. Onderstaande tabel geeft aan welke clausule geldig is voor welke DML-instructie (INSERT / UPDATE / DELETE).

INVOEGEN BIJWERKEN VERWIJDEREN
:NIEUW GELDIG GELDIG ONGELDIG. Er is geen nieuwe waarde in verwijder-case.
:OUD ONGELDIG. Er is geen oude waarde in de insteekmap GELDIG GELDIG

IN PLAATS VAN Trigger

"IN PLAATS VAN trigger" is het speciale type trigger. Het wordt alleen gebruikt in DML-triggers. Het wordt gebruikt wanneer er een DML-gebeurtenis zal plaatsvinden in de complexe weergave.

Beschouw een voorbeeld waarin een aanzicht is gemaakt van 3 basistafels. Wanneer een DML-gebeurtenis wordt uitgegeven via deze weergave, wordt deze ongeldig omdat de gegevens uit 3 verschillende tabellen worden gehaald. Dus in deze INSTEAD OF-trigger wordt gebruikt. De INSTEAD OF-trigger wordt gebruikt om de basistabellen rechtstreeks te wijzigen in plaats van de weergave voor de gegeven gebeurtenis te wijzigen.

Voorbeeld 1 : In dit voorbeeld gaan we een complexe weergave maken op basis van twee basistabellen.

  • Table_1 is emp table en
  • Table_2 is de afdelingstabel.

Vervolgens gaan we zien hoe de INSTEAD OF-trigger wordt gebruikt om UPDATE de locatiegegevensverklaring over deze complexe weergave te geven. We gaan ook kijken hoe: NEW en: OLD nuttig is bij triggers.

  • Stap 1: Tabel 'emp' en 'dept' maken met de juiste kolommen
  • Stap 2: De tabel vullen met voorbeeldwaarden
  • Stap 3: Creëren van een weergave voor de hierboven gemaakte tabel
  • Stap 4: Update van weergave vóór de trigger in plaats van
  • Stap 5: Creëren van de trigger in plaats van
  • Stap 6: Update van weergave na in plaats van trigger

Stap 1) Tabel 'emp' en 'dept' maken met de juiste kolommen

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

Code Verklaring

  • Coderegel 1-7 : Tabel 'emp' aanmaken.
  • Coderegel 8-12 : Aanmaak van tabel 'afdeling'.

Uitvoer

Tafel gemaakt

Stap 2) Nu we de tabel hebben gemaakt, zullen we deze tabel vullen met voorbeeldwaarden en het maken van weergaven voor de bovenstaande tabellen. 

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

Code Verklaring

  • Coderegel 13-19 : gegevens invoegen in de 'afd'-tabel.
  • Coderegel 20-26: gegevens invoegen in de 'emp'-tabel.

Uitvoer

PL / SQL-procedure voltooid

Stap 3) Een weergave maken voor de hierboven gemaakte 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;

Code Verklaring

  • Coderegel 27-32: Creëren van de weergave 'guru99_emp_view'.
  • Coderegel 33: guru99_emp_view opvragen.

Uitvoer

Bekijk gemaakt

NAAM WERKNEMER DEPT_NAME PLAATS
ZZZ HR VS
JJJ VERKOOP UK
XXX FINANCIEEL JAPAN

Stap 4) Update van weergave vóór in plaats van trigger.

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

Code Verklaring

  • Coderegel 34-38: Werk de locatie van "XXX" bij naar 'FRANKRIJK'. Het veroorzaakte de uitzondering omdat de DML-instructies niet zijn toegestaan ​​in de complexe weergave.

Uitvoer

ORA-01779: kan geen kolom wijzigen die is toegewezen aan een tabel zonder sleutelbewaring

ORA-06512: op regel 2

Stap 5) Om de fout te voorkomen tijdens het bijwerken van de weergave in de vorige stap, gaan we in deze stap "in plaats van trigger" gebruiken.

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

Code Verklaring

  • Code regel 39: Creëren van INSTEAD OF trigger voor 'UPDATE' gebeurtenis op de 'guru99_emp_view' weergave op het RIJ-niveau. Het bevat de update-instructie om de locatie in de basistabel 'afdeling' bij te werken.
  • Coderegel 44: Update-instructie gebruikt ': NEW' en ': OLD' om de waarde van kolommen voor en na de update te vinden.

Uitvoer

Trigger gemaakt

Stap 6) Update van weergave na in plaats van trigger. Nu zal de fout niet komen omdat de "in plaats van trigger" de updatebewerking van deze complexe weergave zal afhandelen. En wanneer de code is uitgevoerd, wordt de locatie van werknemer XXX bijgewerkt naar "Frankrijk" vanuit "Japan".

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

Code Verklaring:

  • Coderegel 49-53: Update van de locatie van "XXX" naar 'FRANKRIJK'. Het is gelukt omdat de 'INSTEAD OF'-trigger de actuele update-instructie heeft gestopt en de update van de basistabel heeft uitgevoerd.
  • Coderegel 55: het bijgewerkte record verifiëren.

Uitgang:

PL / SQL-procedure met succes voltooid

NAAM WERKNEMER DEPT_NAME PLAATS
ZZZ HR VS
JJJ VERKOOP UK
XXX FINANCIEEL FRANKRIJK

Samengestelde trigger

De samengestelde trigger is een trigger waarmee u acties kunt specificeren voor elk van de vier timingpunten in de enkele triggerbody. De vier verschillende timingpunten die het ondersteunt, zijn zoals hieronder.

  • VOOR VERKLARING - niveau
  • VOOR RIJ - niveau
  • NA RIJ - niveau
  • NA VERKLARING - niveau

Het biedt de mogelijkheid om de acties voor verschillende timing in dezelfde trigger te combineren.

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;

Syntaxis Uitleg:

  • De bovenstaande syntaxis toont het maken van de 'COMPOUND'-trigger.
  • Declaratieve sectie is gemeenschappelijk voor alle uitvoeringsblokken in de hoofdtekst van de trigger.
  • Deze 4 timingblokken kunnen in elke volgorde staan. Het is niet verplicht om al deze 4 timingblokken te hebben. We kunnen alleen een COMPOUND-trigger maken voor de vereiste timings.

Voorbeeld 1 : In dit voorbeeld gaan we een trigger maken om de salariskolom automatisch in te vullen met de standaardwaarde 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;

Code Verklaring:

  • Coderegel 2-10 : Creëren van samengestelde trigger. Het is gemaakt voor timing VOOR RIJ-niveau om het salaris te vullen met de standaardwaarde 5000. Hierdoor wordt het salaris gewijzigd in de standaardwaarde '5000' voordat het record in de tabel wordt ingevoegd.
  • Coderegel 11-14 : Voeg het record in de 'emp'-tabel in.
  • Coderegel 16 : verificatie van het ingevoegde record.

Uitgang:

Trigger gemaakt

PL / SQL-procedure met succes voltooid.

EMP_NAME EMP_NO SALARIS MANAGER DEPT_NO
CCC 1004 5000 AAA 30

Triggers in- en uitschakelen

Triggers kunnen worden in- of uitgeschakeld. Om de trigger in of uit te schakelen, moet een ALTER-instructie (DDL) worden gegeven voor de trigger die deze in- of uitschakelt.

Hieronder vindt u de syntaxis voor het in- / uitschakelen van de triggers.

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

Syntaxis Uitleg:

  • De eerste syntaxis laat zien hoe u de enkele trigger kunt in- / uitschakelen.
  • De tweede instructie laat zien hoe u alle triggers op een bepaalde tafel kunt in- / uitschakelen.

Overzicht

In dit hoofdstuk hebben we geleerd over PL / SQL-triggers en hun voordelen. We hebben ook de verschillende classificaties geleerd en IN PLAATS VAN trigger en COMPOUND trigger besproken.