Oracle PL/SQL-trigger: I stedet for &-sammensatte typer

Hvad er Trigger i PL/SQL?

TRIGGERE er gemt PL / SQL programmer, der udløses af Oracle motoren automatisk når DML erklæringer som f.eks. insert, update og delete udføres på tabellen, eller når der opstår hændelser. Den kode, der skal udføres i tilfælde af en trigger, kan defineres efter behov. Du kan vælge den hændelse, hvorpå triggeren skal udløses, og timingen af ​​udførelsen. Formålet med en trigger er at opretholde integriteten af ​​informationen i 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å tidspunktet

• FØR udløser: Den udløses, før den angivne hændelse er indtruffet.
• EFTER udløser: Den udløses efter den angivne hændelse er indtruffet.
• I STEDET FOR Udløser: En særlig type. Du vil lære mere i de følgende emner. (kun for DML)

Klassificering baseret på niveau

• Udløser på STATUS-niveau: Den udløses én gang for den angivne hændelsessætning.
• ROW-niveau-udløser: Den udløses for hver post, der blev påvirket af den angivne hændelse. (kun for DML)

Klassificering baseret på begivenheden

• DML-udløser: Den udløses, når DML-hændelsen er angivet (INSERT/UPDATE/DELETE).
• DDL-udløser: Den udløses, når DDL-hændelsen er angivet (CREATE/ALTER).
• DATABASE-trigger: Den aktiveres, når databasehændelsen er angivet (LOGON/LOGOFF/STARTUP/SHUTDOWN).

Så hver trigger er en kombination af ovenstående parametre.

Sådan opretter du trigger

Nedenfor er syntaksen for oprettelse af en trigger. Skærmbilledet nedenfor viser denne syntaks for oprettelse af trigger i Oracle.

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.
• FØR/EFTER vil specificere begivenhedstidspunkterne.
• INSERT/OPDATERING/LOGON/CREATE/osv. vil angive den hændelse, som udløseren skal udløses for.
• ON-klausulen angiver det objekt, hvor ovennævnte hændelse er gyldig. For eksempel vil dette være tabelnavnet, hvor DML-hændelsen kan forekomme i tilfælde af en DML-trigger.
• Kommandoen "FOR EACH ROW" angiver ROW-niveau-triggeren.
• WHEN-klausulen angiver den yderligere betingelse, hvorunder udløseren skal udløses.
• Deklarationsdelen, udførelsesdelen og undtagelseshåndteringsdelen er de samme som i de andre PL/SQL blokkeErklæringsdelen og undtagelse håndtering del er valgfri.

: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 angivet to klausuler i rækkeniveau-triggeren til at indeholde disse værdier. Vi kan bruge disse klausuler til at referere til de gamle og nye værdier i triggerens brødtekst.

• :NY – Den indeholder en ny værdi for kolonnerne i basistabellen/-visningen under udløserudførelsen.
• :GAMMEL – Den gemmer den gamle værdi af kolonnerne i basistabellen/-visningen under triggerudførelsen.

Denne klausul skal bruges baseret på DML-hændelsen. Tabellen nedenfor angiver, hvilken klausul der er gyldig for hvilken DML-sætning (INSERT/UPDATE/DELETE).

I STEDET FOR Trigger

En "INSTEAD OF trigger" er en særlig type trigger. Den bruges kun i DML-triggere. Den bruges, når en DML-hændelse vil forekomme i en kompleks visning.

Overvej et eksempel, hvor en visning er lavet ud fra tre basistabeller. Når en DML-hændelse udstedes over denne visning, bliver den ugyldig, fordi dataene er taget fra tre forskellige tabeller. Så i dette tilfælde bruges en INSTEAD OF-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 oprette en kompleks visning ud fra to basistabeller, hvor Tabel_1 er lejrtabellen og Tabel_2 er afdelingstabellen.

Derefter skal vi se, hvordan INSTEAD OF-triggeren bruges til at udstede en UPDATE af placeringsdetaljerne i denne komplekse visning. Vi skal også se, hvordan :NEW og :OLD er nyttige i triggere. Eksemplet udføres i følgende trin:

• Trin 1: Oprettelse af tabellerne 'emp' og 'dept' med passende kolonner
• Trin 2: Udfyldning af tabellerne med eksempelværdier
• Trin 3: Oprettelse af en visning for de ovennævnte tabeller
• Trin 4: Opdatering af visningen før INSTEAD OF-triggeren
• Trin 5: Oprettelse af INSTEAD OF-triggeren
• Trin 6: Opdatering af visningen efter INSTEAD OF-triggeren

Trin 1) Oprettelse af tabellerne 'emp' og 'dept' med passende kolonner.

Skærmbilledet nedenfor viser oprettelsen af ​​basistabellerne 'emp' og 'dept' i Oracle.

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 Forklaring

• Code linje 1-7: Oprettelse af tabel 'employ'.
• Code linje 8-12: Oprettelse af tabel 'afdeling'.

Output:

Table Created

Trin 2) Nu hvor vi har oprettet tabellerne, vil vi udfylde dem med eksempelværdier.

Skærmbilledet nedenfor viser eksempelrækkerne, der indsættes i tabellerne 'dept' og 'emp'.

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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Forklaring

• Code linje 13-19: Indsættelse af data i 'afdeling'-tabellen.
• Code linje 20-26: Indsættelse af data i tabellen 'emp'.

Output:

PL/SQL procedure completed

Trin 3) Opretter en visning for ovenstående tabeller.

Skærmbilledet nedenfor viser den komplekse visning, der oprettes og derefter forespørges.

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 Forklaring

• Code linje 27-32: Oprettelse af visningen 'guru99_emp_view'.
• Code linje 33: Forespørger guru99_emp_view.

Output:

View created

Trin 4) Opdatering af visningen før INSTEAD OF-triggeren.

Skærmbilledet nedenfor viser opdateringsforsøget i den komplekse visning og den resulterende fejl.

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

Code Forklaring

• Code linje 34-38: Opdater placeringen af ​​"XXX" til 'FRANCE'. Der opstod en undtagelse, fordi DML-sætninger ikke er tilladt direkte i den komplekse visning.

Output:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

Trin 5) For at undgå den fejl, der opstod under opdatering af visningen i det forrige trin, vil vi i dette trin bruge en "INSTEAD OF"-trigger.

Skærmbilledet nedenfor viser oprettelsen af ​​INSTEAD OF-triggeren.

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 Forklaring

• Code linje 39: Oprettelse af INSTEAD OF-triggeren for 'UPDATE'-hændelsen i 'guru99_emp_view'-visningen på ROW-niveau. Den indeholder opdateringssætningen til at opdatere placeringen i basistabellen 'dept'.
• Code linje 44: Opdateringssætningen bruger ':NEW' og ':OLD' til at finde værdien af ​​kolonner før og efter opdateringen.

Output:

Trigger Created

Trin 6) Opdatering af visningen efter INSTEAD OF-triggeren. Fejlen vil nu ikke vises, da "INSTEAD OF-triggeren" vil håndtere opdateringshandlingen for denne komplekse visning. Når koden udføres, vil placeringen af ​​medarbejder XXX blive opdateret til "Frankrig" fra "Japan".

Skærmbilledet nedenfor viser den vellykkede opdatering via INSTEAD OF-triggeren og den opdaterede visning.

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

SELECT * FROM guru99_emp_view;

Code Forklaring:

• Code linje 49-53: Opdatering af placeringen af ​​“XXX” til 'FRANCE'. Det er lykkedes, fordi 'INSTEAD OF'-triggeren har stoppet den faktiske opdateringssætning på visningen og udført opdateringen af ​​basistabellen.
• Code linje 55: Bekræfter den opdaterede post.

Output:

PL/SQL procedure successfully completed

Sammensat trigger

Den sammensatte trigger er en trigger, der giver dig mulighed for at angive handlinger for hvert af fire timingpunkter i en enkelt triggerkrop. De fire forskellige timingpunkter, den understøtter, er som følger.

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

Det giver mulighed for at kombinere handlingerne for forskellige timings i den samme trigger.

Skærmbilledet nedenfor viser den sammensatte triggersyntaks med dens fire timing-sektioner.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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 ​​en 'COMPOUND'-trigger.
• Den deklarative sektion er fælles for alle udførelsesblokke i trigger-brødteksten.
• Disse fire timingblokke kan være i en hvilken som helst rækkefølge. Det er ikke obligatorisk at have alle fire timingblokke. Vi kan oprette en COMPOUND-trigger kun for de timinger, der er nødvendige.

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

Skærmbilledet nedenfor viser eksemplet på den sammensatte trigger og dens output.

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

• Code linje 2-10: Oprettelse af den sammensatte trigger. Den oprettes til timingniveauet BEFORE ROW 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.
• Code linje 11-14: Indsæt posten i tabellen 'emp'.
• Code linje 16: Bekræfter den indsatte post.

Output:

Trigger created

PL/SQL procedure successfully completed.

Aktivering og deaktivering af triggere

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

Nedenfor er syntaksen for aktivering/deaktivering af udløserne.

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 en enkelt trigger.
• Den anden sætning viser, hvordan du aktiverer/deaktiverer alle triggere på en bestemt tabel.

Ofte Stillede Spørgsmål