Oracle Handledning för PL/SQL-utlösare: Istället för sammansatt [Exempel]

Vad är Trigger i PL/SQL?

TRIGGERS är lagrade program som avfyras av Oracle motorn automatiskt när DML-satser som infoga, uppdatera, ta bort exekveras på bordet eller några händelser inträffar. Koden som ska exekveras i händelse av en trigger kan definieras enligt kravet. Du kan välja händelsen som utlösaren måste aktiveras på och tidpunkten för utförandet. Syftet med trigger är att upprätthålla integriteten hos informationen i databasen.

Fördelarna med triggers

Följande är fördelarna med triggers.

• Genererar några härledda kolumnvärden automatiskt
• Upprätthållande av referensintegritet
• Händelseloggning och lagring av information om bordsåtkomst
• Revision
• Synchederlig replikering av tabeller
• Att införa säkerhetstillstånd
• Förhindra ogiltiga transaktioner

Typer av triggers i Oracle

Triggers kan klassificeras baserat på följande parametrar.

• Klassificering baserad på Tidpunkten
• BEFORE Trigger: Den utlöses innan den angivna händelsen har inträffat.
• EFTER Trigger: Den utlöses efter att den angivna händelsen har inträffat.
• I STÄLLET FÖR Trigger: En speciell typ. Du kommer att lära dig mer om de ytterligare ämnena. (endast för DML)
• Klassificering baserad på nivå
• STATEMENT nivå Trigger: Den avfyras en gång för den angivna händelsesatsen.
• ROW level Trigger: Den aktiveras för varje post som påverkades i den angivna händelsen. (endast för DML)
• Klassificering baserad på Event
• DML-utlösare: Den aktiveras när DML-händelsen är specificerad (INSERT/UPDATE/DELETE)
• DDL-utlösare: Den aktiveras när DDL-händelsen är specificerad (CREATE/ALTER)
• DATABAS-utlösare: Den aktiveras när databashändelsen är specificerad (LOGGA PÅ/LOGGA AV/STARTUP/STÄNG)

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

Hur man skapar trigger

Nedan är syntaxen för att skapa en 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;

Syntaxförklaring:

• Ovanstående syntax visar de olika valfria uttalanden som finns i triggerskapande.
• BEFORE/AFTER kommer att specificera händelsens tider.
• INFOGA/UPPDATERA/LOGGA PÅ/SKAPA/etc. kommer att ange händelsen för vilken utlösaren måste aktiveras.
• ON-satsen kommer att specificera på vilket objekt ovan nämnda händelse är giltig. Detta kommer till exempel att vara tabellnamnet där DML-händelsen kan inträffa i fallet med DML Trigger.
• Kommandot "FÖR VARJE RAD" kommer att specificera ROW-nivåutlösaren.
• WHEN-satsen kommer att specificera det ytterligare tillståndet i vilket utlösaren måste aktiveras.
• Deklarationsdelen, exekveringsdelen, undantagshanteringsdelen är densamma som den andra PL/SQL-block. Deklarationsdel och undantagshanteringsdel är valfria.

:NY och :GAMMEL klausul

I en utlösare på radnivå aktiveras utlösaren för varje relaterad rad. Och ibland krävs det att man känner till värdet före och efter DML-satsen.

Oracle har tillhandahållit två satser i triggern på RECORD-nivå för att hålla dessa värden. Vi kan använda dessa klausuler för att referera till de gamla och nya värdena inuti triggerkroppen.

• :NEW – Den innehåller ett nytt värde för kolumnerna i bastabellen/vyn under triggerexekveringen
• :OLD – Den håller det gamla värdet för kolumnerna i bastabellen/vyn under triggerkörningen

Denna klausul ska användas baserat på DML-händelsen. Tabellen nedan kommer att specificera vilken sats som är giltig för vilken DML-sats (INSERT/UPDATE/DELETE).

	INFOGA	UPPDATERING	RADERA
:NY	GILTIG	GILTIG	OGILTIG. Det finns inget nytt värde i raderingsfall.
:GAMMAL	OGILTIG. Det finns inget gammalt värde i insatsfodral	GILTIG	GILTIG

I STÄLLET FÖR Trigger

"INSTEAD OF trigger" är den speciella typen av trigger. Det 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.

Betrakta ett exempel där en vy är gjord av 3 basbord. När någon DML-händelse skickas över den här vyn blir den ogiltig eftersom data hämtas från 3 olika tabeller. Så i detta används ISTÄLLET FÖR trigger. Triggern INSTEAD OF används för att modifiera bastabellerna direkt istället för att modifiera vyn för den givna händelsen.

Exempelvis 1: I det här exemplet kommer vi att skapa en komplex vy från två bastabeller.

• Table_1 är emp table och

• Tabell_2 är avdelningstabell.

Sedan ska vi se hur INSTEAD OF-utlösaren används för att utfärda UPPDATERA platsdetaljsatsen på denna komplexa vy. Vi kommer också att se hur :NEW och :OLD är användbara 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 ovan skapade tabell
• Steg 4: Uppdatering av vyn före utlösaren istället för
• Steg 5: Skapa en utlösare istället för
• Steg 6: Uppdatering av 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));
/

Code Förklaring

• Code rad 1-7: Skapa tabell 'emp'.
• Code rad 8-12: Skapa tabell 'avd'.

Produktion

Tabell skapad

Steg 2) Nu eftersom vi har skapat tabellen kommer vi att fylla den här tabellen med exempelvärden och Skapa vyer för ovanstående tabeller.

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

Code Förklaring

• Code rad 13-19: Infogar data i 'avd'-tabellen.
• Code rad 20-26: Infogar data i 'emp'-tabellen.

Produktion

PL/SQL-procedur avslutade

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

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;

Code Förklaring

• Code rad 27-32: Skapande av vyn 'guru99_emp_view'.

• Code rad 33: Frågar guru99_emp_view.

Produktion

Vy skapad

ANSTÄLLD NAMN	DEPT_NAME	PLATS
ZZZ	HR	Sverige
ÅÅÅÅ	OMSÄTTNING	UK
XXX	FINANSIELL	JAPAN

Steg 4) Uppdatering av vyn före istället för trigger.

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

Code Förklaring

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

Produktion

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

ORA-06512: vid linje 2

Steg 5)För att undvika felmötet under uppdateringsvyn i föregående steg, kommer vi i detta steg att använda "istället för 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;
/

Code Förklaring

• Code rad 39: Skapande av INSTEAD OF-utlösaren för 'UPDATE'-händelsen i vyn 'guru99_emp_view' på ROW-nivån. Den innehåller uppdateringssatsen för att uppdatera platsen i bastabellen 'avd'.
• Code rad 44: Uppdateringssatsen använder ':NEW' och ': OLD' för att hitta värdet på kolumner före och efter uppdateringen.

Produktion

Trigger skapad

Steg 6) Uppdatering av vyn efter istället för trigger. Nu kommer felet inte att komma eftersom "istället för utlösaren" kommer att hantera uppdateringen av denna komplexa vy. Och när koden har körts kommer platsen för anställd XXX att uppdateras till "Frankrike" från "Japan."

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

SELECT * FROM guru99_emp_view;

Code Förklaring:

• Code rad 49-53: Uppdatering av platsen för "XXX" till "FRANKRIKE". Det är framgångsrikt eftersom "INSTEAD OF"-utlösaren har stoppat den faktiska uppdateringssatsen som visas och utfört uppdateringen av bastabellen.
• Code rad 55: Verifierar den uppdaterade posten.

Produktion:

PL/SQL-proceduren har slutförts

ANSTÄLLD NAMN	DEPT_NAME	PLATS
ZZZ	HR	Sverige
ÅÅÅÅ	OMSÄTTNING	UK
XXX	FINANSIELL	FRANKRIKE

Sammansatt trigger

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

• FÖRE UTTALANDE – nivå
• FÖRE RAD – nivå
• EFTER RAD – nivå
• EFTER UTTALANDE – nivå

Det ger möjlighet att kombinera åtgärder för olika timing till samma utlösare.

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;

Syntaxförklaring:

• Ovanstående syntax visar skapandet av "COMPOUND"-utlösaren.
• Deklarativ sektion är gemensam för alla exekveringsblock i triggerkroppen.
• Dessa 4 tidsblock kan vara i vilken sekvens som helst. Det är inte obligatoriskt att ha alla dessa 4 tidsblock. Vi kan skapa en COMPOUND-utlösare endast för de tider som krävs.

Exempelvis 1: I det här exemplet kommer vi att skapa en utlösare för att automatiskt fylla i lönekolumnen med standardvärdet 5000.

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;

Code Förklaring:

• Code rad 2-10: Skapande av sammansatt trigger. Den skapas för timing FÖRE RAD-nivå för att fylla lönen med standardvärdet 5000. Detta kommer att ändra lönen till standardvärdet '5000' innan posten infogas i tabellen.
• Code rad 11-14: Infoga posten i 'emp'-tabellen.
• Code linje 16: Verifierar 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 triggern måste en ALTER (DDL)-sats ges för triggern som inaktiverar eller aktiverar den.

Nedan finns syntaxen för att aktivera/inaktivera triggers.

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

Syntaxförklaring:

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

Sammanfattning

I det här kapitlet har vi lärt oss om PL/SQL-utlösare och deras fördelar. Vi har också lärt oss de olika klassificeringarna och diskuterat INSTAD OF trigger och COMPOUND trigger.