Trigeriai

10 Paskaita

Santrauka

• • • • • Įvairių tipų trigeriai DB trigeriai ir jų naudojimas DB trigerių kūrimas DB trigerių veikimo taisyklės DB trigerių šalinimas

Duomenų bazės trigeriai

Kas yra trigeris? Trigeris tai aibė veiksmų, kurie automatiškai atliekami vykdant: • lentelės eilučių šalinimą (vykdant SQL sakinį DELETE

)

, • keitimą (UPDATE

)

, • naujų eilučių įterpimą (INSERT

)

.

Trigerių tipai

• • • Trigeris tai: Pl/SQL blokas ar PL/SQL procedūra susieta su • lentele, • vaizdu (view), • schema, • duomenų baze.

Vykdomas neatvirai, kai vyksta tam tikri įvykiai.

Gali būti: • Aplikacijos trigeris: vykdomas, kai įvykis iškyla tam tikroje aplikacijoje.

• Duomenų bazės trigeris: vykdomas, kai duomenų (DML) ar sistemos įvykis (logon ar shutdown) iškyla schemoje ar D.B.

DB trigerių paskirtis

• • Palaikyti duomenų neprieštaringumą; Atlikti operacijas lentelėse, nedalyvaujančiose operacijos metu;

DB trigerių kūrimo gairės

• • • Trigeriai kuriami norint: • Atlikti susijusius veiksmus.

• Centralizuoti globalias operacijas (DB trigeriai veikiantys nepriklausomai nuo to kuris vartotojas ar aplikacija vykdo sakinį).

Trigerių nereikia kurti: • Kai funkcionalumas jau įgyvendintas Oracle serveryje • Dubliuojamas kitas Trigeris Kuriamos DB procedūros ir iškviečiamos trigeryje, jei PL/SQL kodas yra labai ilgas.

• Per didelis trigerių naudojimas gali sukelti sudėtingas priklausomybes ir pasunkinti didelių aplikacijų palaikymą

DB trigerio pavyzdys

Aplikacija INSERT INTO EMPLOYEES...; EMPLOYEES table employee_id last_name job_id salary ..........................................................

..........................................................

CHECK_SAL trigeris ................................

................................

DB trigerių kūrimas

Trigeriai kuriami sakiniu

CREATE TRIGGER

, • • kuriame nurodoma: • Kurį SQL duomenų modifikavimo sakinį vykdant: (

INSERT

,

UPDATE

ar

DELETE

), kviesti trigerį.

• Kokiu momentu trigerį aktyvuoti: ▫ ▫ ▫ Lentelėse prieš vykdant modifikavimo operaciją (BEFORE), Lentelėse po (AFTER) Vaizduose (view) vietoj (INSTEAD OF), Lentelės vardas: lentelė ar vaizdas (view) • • Trigerio tipas: ▫ ▫ aktyvuojamas kiekvieną kartą vykdant SQL sakinį (STATEMEN), aktyvuojamas kiekvienai operacijoje dalyvaujančiai eilutei (ROW).

trigerio kūnas - vienas ar keli SQL sakiniai, kuriuos reikia įvykdyti, kai trigeris iškviečiamas; trigerio kūno vykdymo sąlyga: iškviesto kūno sakinius vykdyti besąlygiškai ar tik tuomet, kai patenkinta konkreti sąlyga

DML trigerio komponentai

• • • Trigerio veikimo momentas: BEFORE: trigerio kūnas vykdomas prieš iškviečiantį DML įvykį ant lentelės. AFTER: trigerio kūnas vykdomas po iškviečiančio DML įvykio ant lentelės.

INSTEAD OF: trigerio kūnas vykdomas vietoj iškviečiančio įvykio. Šis tipas naudojamas vaizudose (view), kurie nėra kitaip keičiami.

DML trigerio komponentai

• • Trigerį kviečianti vartotojo įvykis: kuris DML • sakinys iškviečia trigerį? Galima naudoti: INSERT UPDATE DELETE

DML trigerio komponentai

Trigerio tipas: ar trigerio kūnas t.b. vykdomas kiekvienai eilutei, kurią paveikia sakinys, ar tik 1 kartą?

• STATEMENT: ▫ ▫ ▫ Trigerio kūnas vykdomas tik 1 kartą trigerį sukėlusiam įvykiui.

Šis tipas yra parenkamas pagal nutylėjimą.

STATEMENT trigeris suveikia tik 1 kartą, net jei nė viena eilutė n erą paveikta.

• ROW: ▫ ▫ Trigerio kūnas vykdomas vieną kartą kiekvienai trigerį sukėlusio įvykio paveiktai eilutei.

ROW trigeris nevykdomas jei jį sukėlęs įvykis nepaveikia nė vienos eilutės.

DML trigerio komponentai

Trigerio kūnas: kokį veiksmą trigeris turi atlikti?

Trigerio kūnas yra PL/SQL blokas ar procedūros kvietimas.

Trigerių apribojimai

• • • • • • Trigerio dydis negali viršyti 32K bitų Trigerio kūnas gali susidėti iš SQL DML sakinių, tačiau privalo būti SELECT... INTO... sakiniai Trigerio kūne neleidžiama rašyti DDL sakinių Negalima naudoti ROLLBACK, COMMIT ir SAVEPOINT Sisteminiams trigeriams {CREATE/ALTER/DROP} TABLE sakiniai ir ALTER...COMPILE yra leidžiami

Veikimo tvarka

Lentelės trigerių, kai keičiama viena eilutė, vykdymo seka:

DML sakinys

INSERT INTO deparments (department_id, department_name, location_id) VALUES (400, ‘CONSULTING’, 2400) Įterpiama 1 eilutė.

Trigerį iškviečiantis veiksmas

department_id department_name location_id ---------------------------------------------- ......

---------------------------------------------- BEFORE STATEMENT trigeris BEFORE ROW trigeris AFTER ROW trigeris AFTER STATEMENT trigeris

Veikimo tvarka

Lentelės trigerių, kai keičiamos kelios eilutės,vykdymo seka:

DML sakinys

UPDATE employees SET salary=salary*1.1 WHERE department_id=30; Pakei čiamos 6 eilutės.

Trigerį iškviečiantis veiksmas

department_id department_name location_id BEFORE STATEMENT trigeris ---------------------------------------------- ---------------------------------------------- BEFORE ROW trigeris AFTER ROW trigeris ……………..

BEFORE ROW trigeris ---------------------------------------------- ---------------------------------------------- AFTER ROW trigeris AFTER STATEMENT trigeris

DML STATEMENT trigerių kūrimo sintaksė

CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name trigger_body

Pastaba:

trigerio vardai t.b. schemos viduje uninkalūs

Pvz. Lentelei Emp_tab sukuriamas trigeris:

/ CREATE OR REPLACE TRIGGER Print_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab FOR EACH ROW WHEN (new.Empno > 0) DECLARE sal_diff number; BEGIN sal_diff := :new.sal - :old.sal; dbms_output.put('Old salary: ' || :old.sal); dbms_output.put(' New salary: ' || :new.sal); dbms_output.put_line(' Difference ' || sal_diff); END;

Dar vienas DB trigerio pavyzdys

/ CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT ON employees BEGIN IF (TO_CHAR (SYSDATE,’DY’) IN (‘SAT’)) THEN RAISE_APPLICATION_ERROR ( 20500, ‘You may insert into EMPLOYEES table only during business hours.’); END IF; END;

Sąlyginių predikatų naudojimas

CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT OR UPDATE OR DELETE ON employees BEGIN IF (TO_CHAR (SYSDATE,’DY’) IN (‘SAT’)) THEN IF

DELETING

THEN RAISE_APPLICATION_EROR ( 20502,’You may delete from EMPLOYEES table only during business hours .’); IF

INSERTNG

THEN RAISE_APPLICATION_EROR ( 20500,’You may insert into EMPLOYEES table only during business hours .’); IF

UPDATING

( ‘

SALARY

’) THEN RAISE_APPLICATION_EROR ( 20503,’You may update SALARY only during business hours .’); ELSE RAISE_APPLICATION_EROR (-20504, ’You may update EMPLOYEES table only during normal hours .’);\ END IF; END IF; END;

DML ROW trigerio kūrimas

CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [or event2 OR event3] ON table_name [REFERENCING OLD AS old / NEW AS new] FOR EACH ROW [WHEN (condition)] trigger_body

DML ROW trigerio kūrimo pavyzdys

/ CREATE [OR REPLACE] TRIGGER restrict_salary BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW BEGIN IF NOT (:NEW.job_id IN (‘AD_PRES’,’AD_VP’)) AND :NEW.salary>15000 THEN RAISE_APPLICATION_ERROR (-20202, ‘Employee cannot earn this amount.’); END IF; END;

OLD ir NEW charakteristikos

• • PL/SQL kodas ir SQL sakiniai gali prieiti prie senų (old ) ir naujų (new) stulpelio tam tikros eilutės reikšmių. Old ir New egzistuoja kiekviename modifikuojame stulpelyje: pirmasis skirtas senai stulpelio reikšmei saugoti, antrasis – naujai.

Trigerių priėjimo būdai prie OLD ir NEW charakteristikų

• • Trigeris, iškviestas INSERT sakiniu, turi prasmingą priėjimą tik prie naujų stulpelio reikšmių. Kadangi su INSERT sukurtos eilutės sena reikšmė yra lygi NULL • Trigeris, iškviestas UPDATE sakiniu, turi priėjimą prie abiejų stulpelio reikšmių • Senos ir naujos reikšmės pasiekiamos BEFORE ir AFTER eilės trigeriais. Nauja stulpelio reikšmė gali būti paskirta tik BEFORE eilės trigeriu.

OLD ir NEW naudojimo pavyzdys

/ CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR INSERT OR UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO audit_emp_table (user_name, timestamp, id, old_last_name,new_last_name, old_title, new_title, old_salary, new_salary) VALUES (USER, SUSDATE,

:OLD.employee_id, :OLD.last_name, :NEW.last_name, :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary

); END;

OLD ir NEW žymėjimai: pvz. su AUDIT_EMP_TABLE lentele

INSERT INTO employees (employee_id, last_name, job_id, salary,...) VALUES (999, ‘Temp emp’, ‘SA_REP’, 1000,…); UPDATE employees SET salary =2000, last_name=‘Smith’ WHERE employee_id=999; 1 row updated 1 row inserted SELECT user_name, timestamp,…FROM audit_emp_table

ROW trigerio apribojimas

CREATE OR REPLACE TRIGGER derive_commision_pct BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW WHEN (

NEW.job_id

=‘SA_REP’)---nereikia dvitaškio (:) BEGIN IF INSERTING THEN

:NEW.commission_pct

:=0; ELSIF

:OLD. commission_pct

IS NULL THEN

:NEW.commission_pct

:=0; ELSE

:NEW.commission_pct:=:OLD.commission_pct;

END IF; / END;

INSTEAD OF trigeris

Aplikacija INSERT INTO my_view ...; INSTEAD OF trigeris INSERT TABLE1 My_view UPDATE TABLE2

INSTEAD OF TRIGERIO kūrimas

CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF event1 [or event2 OR event3] ON table_name [REFERENCING OLD AS old / NEW AS new] FOR EACH ROW trigger_body

Skirtumai tarp D.B. trigerių ir saugomų procedūrų

Trigeriai Procedūros Apibrėžiami su CREATE TRIGGER Apibrėžiamos su CREATE PROCEDURE Kodas saugomas duomenų žodyno Kodas saugomas duomenų žodyno view’e USER_TRIGERS view’e USER_SOURCE Kviečiamas neatvirai Kviečiamas atvirai COMMIT, SAVEPOINT, ROLLBACK Neleidžiami (galima atlikti kviečiant procedūrą, Tačiau nerekomenduojama dėl Pašalinų efektų) COMMIT, SAVEPOINT, ROLLBACK leidžiami

Skirtumai tarp D.B. trigerių ir Form Builder trigerių

INSERT INTO employees ...; EMPLOYEES lentelė CHECK_SAL trigeris EMPLOYEE_ID LAST_NAME JOB_ID -------------------------------------------------- ------------------------------------------------- BEFORE INSERT ROW

Trigerių valdymas

D.B. trigerių išjungimas ir pakartotinas įjungimas: ALTER TRIGGER trigger_name DISABLE | ENABLE Visų lentelės trigerių išjungimas ir pakartotinas įjungimas: ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS Lentelės trigerio perkompiliavimas: ALTER TRIGGER trigger_name COMPILE Trigerio šalinimas DROP TRIGGER triger_name; Pvz.: DROP TRIGGER secure_emp;

Pastaba:

visi trigeriai ištrinami, ištrynus lentelę.

Trigerio testavimas

• • • • • Reikia patikrinti kiekvieną duomenų operaciją, kuri iškviečia trigerį.

Reikia patikrinti kiekvieną WHEN dalį.

Reikia patikrinti trigerio veikimą tiesiogiai ant lentos ir netiesiogiai iš procedūros.

Reikia patikrinti trigerio poveikį kt. Trigeriams.

Reikia patikrinti kt. trigerių poveikį trigeriui.

Pastaba:

trigeris netiesiogiai gali iškviesti kt. trigerius.

Trigerio vykdymo modelis ir apribojimų (constraint) tikrinimas

• • Įvykdykite visus BEFORE STATEMENT trigerius.

Kiekvienai paveiktai eilutei: • Įvykdykite visus BEFORE ROW trigerius.

• Įvykdykite visus AFTER ROW trigerius.

• Įvykdykite visus DML sakinius ir įvykdykite visus duomenų vientisumo apribojimų (constraint) tikrinimus.

Apribojimai tikrinami po ROW lygio trigerių, ne prieš.

Pvz. Keisti departamento nr. kai jame yra darbuotojai galima jei trigeriuose pakeisim dep. nr. Vsiems darbuotojams • Įvykdykite visus AFTER STATEMENT trigerius.

Trigerio veikimo modelis ir apribojimų tikrinimas: pvz.

UPDATE employees SET department_id=999 WHERE employee_id=170; --duomen ų vientisumo apribojimo pažeidimas CREATE OR REPLACE TRIGGER constr_emp_trig AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO departments VALUES (999, ‘dept999’, 140, 2400); END; / UPDATE employees SET department_id=999 WHERE employee_id=170; --s ėkmingas po trigerio suveikimo

Santrauka

Proced ūra Paketas --------- --------- --------- --------- Procedūros A deklaracija Procedūros B apibrėžimas Procedūros A apibrėžimas Lokalus kintamasis Trigeris --------- --------- --------- ----------

Sisteminiai įvykiai

Trigeriai aktyvuojasi įvykus tam tikrai operacijai:

• • • DML sakiniai ( DELETE , INSERT , UPDATE ) DDL sakiniai ( CREATE , ALTER , DROP ) Duomenų bazės operacijos ( SERVERERROR , LOGON , LOGOFF , STARTUP , SHUTDOWN )

Pvz. Trigeris, naudojantis LogOff

CREATE OR REPLACE TRIGGER On_Logoff AFTER LOGOFF ON The_user.Schema BEGIN if (ora_sysevent = 'ASSOCIATE STATISTICS') then number_of_modified_objects := ora_dict_obj_owner_list(owner_list); end if; END;

1.Pvz. Trigeris su parinktimis

CREATE OR REPLACE TRIGGER Log_salary_increase AFTER UPDATE ON Emp_tab FOR EACH ROW WHEN (new.Sal > 1000) BEGIN INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action) VALUES (:new.Empno, SYSDATE, :new.SAL, 'NEW SAL'); END; Įvedame SQL sakinį: UPDATE Emp_tab SET Sal = Sal + 1000.0

WHERE Deptno = 20;

2.Pvz. Trigeris su parinktimis

CREATE OR REPLACE TRIGGER Log_emp_update AFTER UPDATE ON Emp_tab BEGIN INSERT INTO Emp_log (Log_date, Action) VALUES (SYSDATE, 'Emp_tab COMMISSIONS CHANGED'); END;

Trigerio kūnas

• • Trigerio kūnas yra CALL procedūra ar PL/SQL blokas, susidedantis iš vieno ar kelių SQL ir PL/SQL sakinių, kuriuos reikia vykdyti iškvietus trigerį Trigerio kūno vykdymo sąlyga: iškviesto kūno sakinius vykdyti besąlygiškai ar tik tuomet, kai patenkinta konkreti sąlyga

Pvz. Trigerio kūnas

CREATE OR REPLACE PROCEDURE foo (c VARCHAR2) AS BEGIN INSERT INTO Audit_table (user_at) VALUES(c); END; CREATE OR REPLACE TRIGGER logontrig AFTER LOGON ON DATABASE / CALL foo (ora_login_user)

Trigerių apribojimas mutuojančiose lentelėse

Mutuojanti lentelė

– tai lentelė, kuri tuo metu yra modifikuojama UPDATE DELETE CASCADE . , DELETE ar INSERT sakiniais ar lentele, galinčia atnaujinti apribojimu Apribojimas taikomas tiems trigeriams, kurie naudoja FOR EACH ROW i š lygą ir trigerio sakinį, kurio rezultatas yra DELETE CASCADE.

Pvz. Mutuojanti lentelė

CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Emp_tab FOR EACH ROW DECLARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM Emp_tab; DBMS_OUTPUT.PUT_LINE(' There are now ' || n || ' employees.'); END; SQL sakinys: DELETE FROM Emp_tab WHERE Empno = 7499; Pranešimas: ORA-04091: table SCOTT.Emp_tab is mutating, trigger/function may not see it

Trigerių galimybės

• Trigeriai gali būti naudojami ir kitų tiesiogiai nedalyvaujančių operacijoje lentelių modifikavimui, priklausomai nuo atliekamos operacijos pagrindinėje lentelėje

Pvz. Kitų lentelių modifikavimas trigeriu

CREATE TRIGGER ModifikuotiNr AFTER UPDATE OF Nr ON Tiekėjai REFERENCINGOLD AS OldTiek NEW AS NewTiek FOR EACH ROW MODE DB2SQL UPDATE Tiekimai SET Tiekimai.TiekNr = NewTiek.Nr WHERE TiekNr = OldTiek.Nr

; END;

Trigerių privalumai

Pagrindinis trigerių privalumas

jais apibrėžtos dalykinės taisyklės saugomos duomenų bazėje • pagreitina programavimą • palengvina dalykinių taisyklių užtikrinimą • yra globalūs

Trigerių trūkumai

• • • DB sudėtingumas Paslėpta logika Paslėpta įtaka našumui