Oracle PL / SQL Trigger Tutorial: istället för, förening [exempel]

i den här handledningen lär du dig –

• fördelar med Triggers
• typer av Triggers i Oracle
• hur man skapar Trigger
• :ny och :gammal Klausul
• istället för Trigger
• sammansatt Trigger

fördelar med Triggers

Följande är fördelarna med triggers. generera några härledda kolumnvärden automatiskt

genomdriva referensintegritet

händelseloggning och lagring av information om tabellåtkomst

revision

synkron replikering av tabeller

införande av säkerhetstillstånd

förhindra ogiltiga transaktioner

typer av Triggers i Oracle

Triggers kan klassificeras baserat på följande parametrar.

• klassificering baserad på tidpunkten
  • före utlösaren: den avfyrar innan den angivna händelsen har inträffat.
  • efter utlösare: den aktiveras efter att den angivna händelsen har inträffat.
  • istället för Trigger: en speciell typ. Du kommer att lära dig mer om de ytterligare ämnena. (endast för DML )
• klassificering baserat på nivån
  • STATEMENT level Trigger: den avfyrar en gång för det angivna händelseutdraget.
  • ROW level Trigger: den avfyrar för varje post som påverkades i den angivna händelsen. (endast för DML)
• klassificering baserad på händelsen
  • DML-utlösare: den aktiveras när DML-händelsen anges (infoga / uppdatera / radera)
  • DDL-utlösare: Den avfyrar när DDL-händelsen anges (skapa/ändra)
  • DATABASUTLÖSARE: den avfyrar när databashändelsen anges (inloggning/utloggning/start/avstängning)

så varje utlösare är kombinationen av ovanstående parametrar.

hur man skapar Trigger

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

CREATE TRIGGER <trigger_name> ON<name of underlying object> DECLARE<Declaration part>BEGIN<Execution part> EXCEPTION<Exception handling part> END;

Syntaxförklaring:

• ovanstående syntax visar de olika valfria uttalandena som finns i trigger creation.
• före / efter anger tidpunkten för händelsen.
• infoga / uppdatera / logga in / Skapa / etc. kommer att ange händelsen för vilken avtryckaren måste avfyras.
• on-klausulen anger på vilket objekt ovannämnda händelse är giltig. Detta kommer till exempel att vara tabellnamnet på vilket DML-händelsen kan inträffa vid DML-utlösare.
• kommandot ”för varje rad” anger RADNIVÅUTLÖSAREN.
• when-klausulen anger det ytterligare villkoret där utlösaren måste avfyras.
• deklarationsdelen, exekveringsdelen, undantagshanteringsdelen är densamma som för de andra PL/SQL-blocken. Deklarationsdel och undantagshanteringsdel är valfria.

: ny och gammal Klausul

i en radnivåutlösare utlöses utlösaren för varje relaterad rad. Och ibland är det nödvändigt att känna till värdet före och efter DML-uttalandet.

Oracle har tillhandahållit två klausuler i postnivåutlösaren 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 inuti triggerkroppen.

• :Nytt-det innehåller ett nytt värde för kolumnerna i bastabellen / vyn under triggerutförandet
• : gammalt-det innehåller Gammalt värde för kolumnerna i bastabellen/vyn under triggerutförandet

denna klausul ska användas baserat på DML-händelsen. Tabellen nedan anger vilken klausul som är giltig för vilken DML-uttalande (infoga/uppdatera/radera).

	infoga	uppdatera	ta bort
: ny	giltig	giltig	ogiltig. Det finns inget nytt värde I delete case.
:Gammal	ogiltig. Det finns inget Gammalt värde i insert case	giltig	giltig

istället för Trigger

”istället för trigger” är den speciella typen av trigger. Den används endast i DML-triggers. 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 3 bastabeller. När någon DML-händelse utfärdas över den här vyn blir den ogiltig eftersom data tas från 3 olika tabeller. Så i detta istället för utlösare används. Istället för utlösaren används för att ändra bastabellerna direkt istället för att ändra vyn för den givna händelsen.

exempel 1: i det här exemplet kommer vi att skapa en komplex vy från två bastabell.

• Table_1 är emp-tabell och

• Table_2 är avdelningstabell.

då ska vi se hur istället för utlösaren används för att utfärda uppdatera platsdetaljuttalandet i denna komplexa vy. Vi ska också se hur: nytt och: gammalt är användbart i triggers.

• Steg 1: Skapa tabell ’emp’ och ’dept’ med lämpliga kolumner
• steg 2: fylla i tabellen med exempelvärden
• steg 3: Skapa vy för ovanstående skapade tabell
• steg 4: uppdatering av vyn före istället för utlösaren
• Steg 5: skapande av istället för utlösaren
• steg 6: uppdatering av vyn efter istället för utlösaren

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

• kodlinje 1-7: Tabell ’ emp ’ skapande.
• kodlinje 8-12: tabell’ dept ’ skapande.

utgång

tabell skapad

steg 2) Nu sedan vi har skapat tabellen kommer vi att fylla denna tabell med provvä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

• kodlinje 13-19: infoga data i ”dept” – tabellen.
• kodrad20-26: infoga data i’ emp ’ – tabellen.

utgång

PL/SQL-proceduren slutförd

steg 3) Skapa en vy för ovanstående skapade tabell.

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

• kodlinje 27-32: skapande av ’guru99_emp_view’ vy.

• kodrad33: fråga guru99_emp_view.

Output

View created

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
YYY	SALES	UK
XXX	FINANCIAL	JAPAN

Step 4) Update of view before instead-of trigger.

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

Code Explanation

• Code line 34-38: Update the location of ”XXX” to ’FRANCE’. Det tog upp undantaget eftersom DML-uttalandena inte är tillåtna i den komplexa vyn.

utgång

ORA-01779: Det går inte att ändra en kolumn som mappas till en icke-nyckelbevarad tabell

ORA-06512: på rad 2

Steg 5)för att undvika att felet uppstår under uppdateringsvyn i föregående steg, kommer vi i detta steg att använda ”istället för utlösare.”

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

• kodlinje 39: Skapande av istället för utlösare för ’UPDATE’ – händelsen i ’guru99_emp_view’ – vyn på radnivå. Den innehåller uppdateringsuttalandet för att uppdatera platsen i bastabellen ’dept’.
• kodrad44: Update statement använder’: NEW ’och’: OLD ’ för att hitta värdet på kolumnerna före och efter uppdateringen.

utgång

Trigger skapad

steg 6) uppdatering av vyn efter istället-av trigger. Nu kommer felet inte att komma som ”istället för trigger” kommer att hantera uppdateringsoperationen för denna komplexa vy. Och när koden har utfört platsen för anställd XXX kommer 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:

• kodlinje 49-53: uppdatering av platsen för ”XXX” till ”Frankrike”. Det lyckas eftersom utlösaren ’istället för’ har stoppat det faktiska uppdateringsuttalandet i vyn och utfört bastabelluppdateringen.
• kodrad55: verifiera den uppdaterade posten.

utgång:

PL/SQL-proceduren har slutförts

EMPLOYEE_NAME	DEPT_NAME	plats
ZZZ	HR	USA
ååå	försäljning	Storbritannien
xxx	finansiella	Frankrike

sammansatt Trigger

den sammansatta utlösaren är en utlösare som låter dig ange åtgärder för var och en av fyra tidspunkter i den enda utlösningskroppen. De fyra olika timing punkt stöder är som nedan.

• före STATEMENT – level
• före ROW – level
• efter ROW – level
• efter STATEMENT – level

det ger möjlighet att kombinera åtgärder för olika timing i samma trigger.

CREATE TRIGGER <trigger_name> FORON <name of underlying object><Declarative part>‭ ‬BEFORE STATEMENT ISBEGIN<Execution part>;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN<Execution part>;END EACH ROW;AFTER EACH ROW ISBEGIN<Execution part>;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN<Execution part>;END AFTER STATEMENT;END;

Syntaxförklaring:

• ovanstående syntax visar skapandet av ”sammansatt” utlösare.
• deklarativ sektion är vanligt för alla exekveringsblock i utlösarkroppen.
• dessa 4 tidsblock kan vara i vilken sekvens som helst. Det är inte obligatoriskt att ha alla dessa 4 tidsblock. Vi kan bara skapa en sammansatt utlösare för de tidpunkter som krävs.

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

CREATE TRIGGER emp_trig FOR INSERT ON empCOMPOUND TRIGGER BEFORE EACH ROW IS BEGIN: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:

• kodlinje 2-10: skapande av sammansatt utlösare. Det är skapat för timing före radnivå för att fylla lönen med standardvärde 5000. Detta ändrar lönen till standardvärdet ’5000’ innan du sätter in posten i tabellen.
• kodrad11-14: sätt in posten i’ emp ’ – tabellen.
• kodrad16: verifiera den infogade posten.

utgång:

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 triggers

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

nedan är syntaxen för att aktivera / inaktivera utlösarna.

ALTER TRIGGER <trigger_name> ;ALTER TABLE <table_name> ALL TRIGGERS;

Syntaxförklaring:

• den första syntaxen visar hur du aktiverar/inaktiverar den enda utlösaren.
• det andra uttalandet visar hur du aktiverar / inaktiverar alla utlösare på en viss tabell.

sammanfattning

i det här kapitlet har vi lärt oss om PL / SQL triggers och deras fördelar. Vi har också lärt oss de olika klassificeringarna och diskuterat istället för trigger och COMPOUND trigger.