Oracle PL / SQL Trigger Tutorial: Istället för, Compound (Exempel)

Vad är Trigger i PL / SQL?

TRIGGERS är lagrade program som avfyras av Oracle-motorn automatiskt när DML-uttalanden som infoga, uppdatera, radera körs på bordet eller vissa händelser inträffar. Koden som ska exekveras vid en utlösare kan definieras enligt kravet. Du kan välja den händelse som utlösaren ska avfyras på och tidpunkten för utförandet. Syftet med utlösaren är att upprätthålla integriteten i informationen i databasen.

I den här handledningen lär du dig-

Fördelar med utlösare
Typer av utlösare i Oracle
Hur man skapar utlösare
: NY och: GAMLA klausul
I stället för utlösaren
Sammansatt utlösare

Fördelar med utlösare

Följande är fördelarna med triggers.

Generera några härledda kolumnvärden automatiskt
Tillämpa referensintegritet
Händelseloggning och lagring av information om tabellåtkomst
Granskning
Synkron replikering av tabeller
Att införa säkerhetsbehörigheter
Förhindra ogiltiga transaktioner

Typer av utlösare i Oracle

Utlösare kan klassificeras baserat på följande parametrar.

Klassificering baserat på tidpunkten
- INNAN Trigger: Den avfyras innan den angivna händelsen har inträffat.
- EFTER Trigger: Den utlöses efter att den angivna händelsen inträffat.
- INSTEAD OF Trigger: En speciell typ. Du lär dig mer om de ytterligare ämnena. (endast för DML)
Klassificering baserat på nivå
- UTTALANDE-nivå Trigger: Den aktiveras en gång för det angivna händelsedeklarationen.
- ROW level Trigger: Den aktiveras för varje post som påverkades i den angivna händelsen. (endast för DML)
Klassificering baserat på händelsen
- DML Trigger: Den aktiveras när DML-händelsen anges (INSERT / UPDATE / DELETE)
- DDL-utlösare: Den aktiveras när DDL-händelsen anges (CREATE / ALTER)
- DATABAS Trigger: Den aktiveras när databashändelsen anges (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Så varje trigger är kombinationen av ovanstående parametrar.

Hur man skapar utlösare

Nedan är syntaxen för att skapa en trigger.

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

Syntaxförklaring:

Ovanstående syntax visar de olika valfria påståenden som finns i utlösarskapandet.
FÖR / EFTER kommer att specificera händelsetiderna.
INSERT / UPDATE / LOGON / CREATE / etc. kommer att specificera den händelse som utlösaren måste avfyras för.
ON-klausulen anger på vilket objekt ovan nämnda händelse är giltig. Till exempel kommer detta att vara det tabellnamn som DML-händelsen kan inträffa i fallet med DML Trigger.
Kommandot "FÖR VARJE RAD" anger ROW-nivåutlösaren.
WHEN-klausulen anger det ytterligare tillstånd i vilket utlösaren måste utlösas.
Deklarationsdelen, exekveringsdel, undantagshanteringsdel är samma som de andra PL / SQL-blocken. Deklarationsdel och undantagshanteringsdel är valfria.

: NY och: GAMLA klausul

I en radnivåutlösare aktiveras utlösaren för varje relaterad rad. Och ibland krävs det att veta värdet före och efter DML-uttalandet.

Oracle har tillhandahållit två klausuler i utlösaren på RECORD-nivå för att hålla dessa värden. Vi kan använda dessa klausuler för att hänvisa till de gamla och nya värdena i utlösarkroppen.

: NYTT - Det har ett nytt värde för kolumnerna i bastabellen / vyn under avfyrningskörningen
: GAMLA - Det innehåller gamla värden på kolumnerna i bastabellen / vyn under utlösaren

Denna klausul bör användas baserat på DML-händelsen. Nedanstående tabell anger vilken klausul som är giltig för vilket DML-uttalande (INSERT / UPDATE / DELETE).

	FÖRA IN	UPPDATERING	RADERA
:NY	GILTIG	GILTIG	OGILTIG. Det finns inget nytt värde i borttagningsfallet.
:GAMMAL	OGILTIG. Det finns inget gammalt värde i insättningsfallet	GILTIG	GILTIG

I stället för utlösaren

"INSTEAD OF trigger" är den speciella typen av trigger. Den används endast i DML-utlösare. Den används när någon DML-händelse kommer att inträffa i den komplexa vyn.

Tänk på ett exempel där en vy är gjord av tre bastabeller. När någon DML-händelse utfärdas över den här vyn blir den ogiltig eftersom data hämtas från tre olika tabeller. Så i denna INSTEAD OF trigger används. INSTEAD OF trigger används för att modifiera bastabellerna direkt istället för att ändra vyn för den givna händelsen.

Exempel 1 : I det här exemplet ska vi skapa en komplex vy från två bastabeller.

Table_1 är emp-tabell och

Table_2 är avdelningstabell.

Då ska vi se hur INSTEAD OF-utlösaren används för att utfärda UPPDATERING av detaljinformation om denna komplexa vy. Vi kommer också att se hur: NEW och: OLD är användbart i triggers.

Steg 1: Skapa tabell 'emp' och 'dept' med lämpliga kolumner
Steg 2: Fyll tabellen med exempelvärden
Steg 3: Skapa vy för tabellen ovan skapade
Steg 4: Uppdatera vyn före istället för utlösaren
Steg 5: Skapande av istället för trigger
Steg 6: Uppdatera vyn efter istället för trigger

Steg 1) Skapa tabell 'emp' och 'dept' med lämpliga kolumner

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

Kodförklaring

Kodrad 1-7 : Skapa tabell 'emp'.
Kodrad 8-12 : Skapande av tabell 'avdelning'.

Produktion

Tabell skapad

Steg 2) Sedan vi har skapat tabellen kommer vi att fylla i denna tabell med exempelvärden och skapa vyer för ovanstående tabeller.

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

Kodförklaring

Kodrad 13-19 : Infoga data i tabellen 'dept'.
Kodrad 20-26: Infoga data i 'emp' -tabellen.

Produktion

PL / SQL-proceduren slutförd

Steg 3) Skapa en vy för tabellen ovan skapade.

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;

Kodförklaring

Kodrad 27-32: Skapande av 'guru99_emp_view' -vy.

Kodrad 33: Fråga guru99_emp_view.

Produktion

Visa skapad

ANSTÄLLD NAMN	DEPT_NAME	PLATS
ZZZ	HR	USA
ÅÅÅ	FÖRSÄLJNING	Storbritannien
XXX	FINANSIELL	JAPAN

Steg 4) Uppdatera vyn före istället för utlösare.

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

Kodförklaring

Kodrad 34-38: Uppdatera platsen för "XXX" till "FRANKRIKE". Det tog upp undantaget eftersom DML-uttalanden inte är tillåtna i den komplexa vyn.

Produktion

ORA-01779: kan inte ändra en kolumn som mappas till en icke-nyckelbevarad tabell

ORA-06512: vid rad 2

Steg 5) För att undvika felmeddelandet under uppdateringsvyn i föregående steg, i det här steget kommer vi att använda "istället för trigger".

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

Kodförklaring

Kodrad 39: Skapande av INSTEAD OF trigger för 'UPDATE' händelse i vyn 'guru99_emp_view' på ROW-nivån. Den innehåller uppdateringsförklaringen för att uppdatera platsen i bastabellen 'avdelning'.
Kodrad 44: Uppdateringsuttalande använder ': NEW' och ': OLD' för att hitta värdet på kolumner före och efter uppdateringen.

Produktion

Trigger skapad

Steg 6) Uppdatera vyn efter istället för trigger. Nu kommer inte felet eftersom "istället för trigger" hanterar uppdateringsfunktionen för denna komplexa vy. Och när koden har körts kommer platsen för anställd XXX att uppdateras till "Frankrike" från "Japan."

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

SELECT * FROM guru99_emp_view;

Kodförklaring:

Kodrad 49-53: Uppdatering av platsen för "XXX" till "FRANKRIKE". Det är framgångsrikt eftersom 'INSTEAD OF' -utlösaren har stoppat den aktuella uppdateringsuttalandet och har gjort bastabelluppdateringen.
Kodrad 55: Verifiera den uppdaterade posten.

Produktion:

PL / SQL-proceduren har slutförts

ANSTÄLLD NAMN	DEPT_NAME	PLATS
ZZZ	HR	USA
ÅÅÅ	FÖRSÄLJNING	Storbritannien
XXX	FINANSIELL	FRANKRIKE

Sammansatt utlösare

Den sammansatta utlösaren är en utlösare som låter dig ange åtgärder för var och en av fyra tidpunkter i den enda utlösarkroppen. De fyra olika tidpunkterna den stöder är som nedan.

FöR UTTALANDE - nivå
INNAN RAD - nivå
EFTER RAD - nivå
EFTER UTTALANDE - nivå

Det ger möjlighet att kombinera åtgärder för olika timing i samma 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;

Syntaxförklaring:

Ovanstående syntax visar skapandet av "COMPOUND" -utlösaren.
Deklarativt avsnitt är vanligt för alla exekveringsblock i utlösarkroppen.
Dessa fyra timingblock kan vara i valfri ordning. Det är inte obligatoriskt att ha alla dessa fyra tidsblock. Vi kan bara skapa en COMPOUND-utlösare för de tidsinställningar som krävs.

Exempel 1 : I det här exemplet ska vi skapa en utlösare för att automatiskt fylla i lönekolumnen med standardvärdet 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;

Kodförklaring:

Kodrad 2-10 : Skapande av sammansatt utlösare. Den skapas för timing FÖRE RAD-nivå för att fylla lönen med standardvärdet 5000. Detta ändrar lönen till standardvärdet '5000' innan du infogar posten i tabellen.
Kodrad 11-14 : Infoga posten i 'emp' -tabellen.
Kodrad 16 : Verifiera den infogade posten.

Produktion:

Trigger skapad

PL / SQL-proceduren har slutförts.

EMP_NAME	EMP_NO	LÖN	CHEF	DEPT_NO
CCC	1004	5000	AAA	30

Aktivera och inaktivera utlösare

Utlösare kan aktiveras eller inaktiveras. För att aktivera eller inaktivera utlösaren måste ett ALTER (DDL) uttalande ges för utlösaren som inaktiverar eller aktiverar den.

Nedan finns syntaxen för att aktivera / inaktivera utlösarna.

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

Syntaxförklaring:

Den första syntaxen visar hur man aktiverar / inaktiverar den enskilda utlösaren.
Det andra uttalandet visar hur du aktiverar / inaktiverar alla triggers på en viss tabell.

Sammanfattning

I detta kapitel har vi lärt oss om PL / SQL-triggers och deras fördelar. Vi har också lärt oss de olika klassificeringarna och diskuterat INSTEAD OF trigger och COMPOUND trigger.