Trigeriai

1. Trigeriai 10 Paskaita

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

3. 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).

4. 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.

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

6. 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ą

7. DB trigerio pavyzdys Aplikacija INSERT INTO EMPLOYEES...; EMPLOYEES table employee_id last_name job_id salary .......................................................... .......................................................... CHECK_SAL trigeris ................................ ................................

8. 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

9. 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.

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

11. 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ė nerą 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.

12. DML trigerio komponentai Trigerio kūnas: kokį veiksmą trigeris turi atlikti? Trigerio kūnas yra PL/SQL blokas ar procedūros kvietimas.

13. 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

14. 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

15. Veikimo tvarka Lentelės trigerių, kai keičiamos kelios eilutės,vykdymo seka: DML sakinys UPDATE employees SET salary=salary*1.1 WHEREdepartment_id=30; Pakeičiamos 6 eilutės. Trigerį iškviečiantis veiksmas department_iddepartment_namelocation_id ----------------------------------------------- ----------------------------------------------- …………….. ----------------------------------------------- ----------------------------------------------- BEFORE STATEMENT trigeris BEFORE ROW trigeris AFTER ROW trigeris BEFORE ROW trigeris AFTER ROW trigeris AFTER STATEMENT trigeris

16. 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

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

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

19. 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,’Youmaydeletefrom EMPLOYEES tableonlyduringbusinesshours.’); IF INSERTNG THEN RAISE_APPLICATION_EROR (-20500,’Youmayinsertinto EMPLOYEES tableonlyduringbusinesshours.’); IF UPDATING (‘SALARY’) THEN RAISE_APPLICATION_EROR (-20503,’Youmayupdate SALARY onlyduringbusinesshours.’); ELSE RAISE_APPLICATION_EROR (-20504, ’Youmayupdate EMPLOYEES tableonlyduringnormalhours.’);\ END IF; END IF; END;

20. 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

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

22. 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.

23. 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.

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

25. 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

26. ROW trigerio apribojimas CREATE OR REPLACE TRIGGER derive_commision_pct BEFORE INSERT OR UPDATE OF salaryON employees FOR EACH ROW WHEN (NEW.job_id=‘SA_REP’)---nereikiadvitaškio (:) BEGIN IF INSERTINGTHEN :NEW.commission_pct:=0; ELSIF :OLD. commission_pctIS NULL THEN :NEW.commission_pct:=0; ELSE :NEW.commission_pct:=:OLD.commission_pct; END IF; END; /

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

28. 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

29. 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, COMMIT, SAVEPOINT, ROLLBACK ROLLBACK Neleidžiami leidžiami (galima atlikti kviečiant procedūrą, Tačiau nerekomenduojama dėl Pašalinų efektų)

30. 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

31. 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ę.

32. 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.

33. 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 jeitrigeriuose pakeisim dep. nr. Vsiems darbuotojams • Įvykdykite visus AFTER STATEMENT trigerius.

34. 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

35. Santrauka ProcedūraPaketasTrigeris Procedūros A deklaracija ---------- ---------- ---------- ---------- Procedūros B apibrėžimas ---------- ---------- ---------- ---------- Procedūros A apibrėžimas Lokalus kintamasis

36. 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)

37. 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;

38. 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;

39. 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;

40. 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

41. 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) /

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

43. 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

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

45. 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;

46. 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

47. Trigerių trūkumai • DB sudėtingumas • Paslėpta logika • Paslėpta įtaka našumui