Trigeriai

1
Trigeriai

• 10 Paskaita

2
Santrauka

• Ivairiu tipu trigeriai
• DB trigeriai ir ju naudojimas
• DB trigeriu kurimas
• DB trigeriu veikimo taisykles
• DB trigeriu šalinimas

3
Duomenu bazes trigeriai

• Kas yra trigeris? Trigeris tai aibe veiksmu,
  kurie automatiškai atliekami vykdant
• lenteles eiluciu šalinima (vykdant SQL sakini
  DELETE),
• keitima (UPDATE),
• nauju eiluciu iterpima (INSERT).

4
Trigeriu tipai

• Trigeris tai
• Pl/SQL blokas ar PL/SQL procedura susieta su
• lentele,
• vaizdu (view),
• schema,
• duomenu baze.
• Vykdomas neatvirai, kai vyksta tam tikri ivykiai.
• Gali buti
• Aplikacijos trigeris vykdomas, kai ivykis iškyla
  tam tikroje aplikacijoje.
• Duomenu bazes trigeris vykdomas, kai duomenu
  (DML) ar sistemos ivykis (logon ar shutdown)
  iškyla schemoje ar D.B.

5
DB trigeriu paskirtis

• Palaikyti duomenu neprieštaringuma
• Atlikti operacijas lentelese, nedalyvaujanciose
  operacijos metu

6
DB trigeriu kurimo gaires

• Trigeriai kuriami norint
• Atlikti susijusius veiksmus.
• Centralizuoti globalias operacijas (DB trigeriai
  veikiantys nepriklausomai nuo to kuris vartotojas
  ar aplikacija vykdo sakini).
• Trigeriu nereikia kurti
• Kai funkcionalumas jau igyvendintas Oracle
  serveryje
• Dubliuojamas kitas Trigeris
• Kuriamos DB proceduros ir iškvieciamos trigeryje,
  jei PL/SQL kodas yra labai ilgas.
• Per didelis trigeriu naudojimas gali sukelti
  sudetingas priklausomybes ir pasunkinti dideliu
  aplikaciju palaikyma

7
DB trigerio pavyzdys

• Aplikacija
• INSERT INTO EMPLOYEES...

EMPLOYEES table employee_id last_name job_id
salary ...........................................
............... ..................................
........................
CHECK_SAL trigeris ...............................
. ................................
8
DB trigeriu kurimas

• Trigeriai kuriami sakiniu CREATE TRIGGER,
• kuriame nurodoma
• Kuri SQL duomenu modifikavimo sakini vykdant
• (INSERT, UPDATE ar DELETE), kviesti trigeri.
• Kokiu momentu trigeri aktyvuoti
• Lentelese prieš vykdant modifikavimo operacija
  (BEFORE),
• Lentelese po (AFTER)
• Vaizduose (view) vietoj (INSTEAD OF),
• Lenteles vardas lentele ar vaizdas (view)
• Trigerio tipas
• aktyvuojamas kiekviena karta vykdant SQL sakini
  (STATEMEN),
• aktyvuojamas kiekvienai operacijoje
  dalyvaujanciai eilutei (ROW).
• trigerio kunas - vienas ar keli SQL sakiniai,
  kuriuos reikia ivykdyti, kai trigeris
  iškvieciamas
• trigerio kuno vykdymo salyga iškviesto kuno
  sakinius vykdyti besalygiškai ar tik tuomet, kai
  patenkinta konkreti salyga

9
DML trigerio komponentai

• Trigerio veikimo momentas
• BEFORE trigerio kunas vykdomas prieš
  iškviecianti DML ivyki ant lenteles.
• AFTER trigerio kunas vykdomas po iškvieciancio
  DML ivykio ant lenteles.
• INSTEAD OF trigerio kunas vykdomas vietoj
  iškvieciancio ivykio. Šis tipas naudojamas
  vaizudose (view), kurie nera kitaip keiciami.

10
DML trigerio komponentai

• Trigeri kviecianti vartotojo ivykis kuris DML
• sakinys iškviecia trigeri? Galima naudoti
• INSERT
• UPDATE
• DELETE

11
DML trigerio komponentai

• Trigerio tipas ar trigerio kunas t.b. vykdomas
• kiekvienai eilutei, kuria paveikia sakinys, ar
  tik 1 karta?
• STATEMENT
• Trigerio kunas vykdomas tik 1 karta trigeri
  sukelusiam ivykiui.
• Šis tipas yra parenkamas pagal nutylejima.
• STATEMENT trigeris suveikia tik 1 karta, net jei
  ne viena eilute nera paveikta.
• ROW
• Trigerio kunas vykdomas viena karta kiekvienai
  trigeri sukelusio ivykio paveiktai eilutei.
• ROW trigeris nevykdomas jei ji sukeles ivykis
  nepaveikia ne vienos eilutes.

12
DML trigerio komponentai

• Trigerio kunas koki veiksma trigeris turi
  atlikti?
• Trigerio kunas yra PL/SQL blokas ar proceduros
• kvietimas.

13
Trigeriu apribojimai

• Trigerio dydis negali viršyti 32K bitu
• Trigerio kunas gali susideti iš SQL DML sakiniu,
  taciau privalo buti SELECT... INTO... sakiniai
• Trigerio kune neleidžiama rašyti DDL sakiniu
• Negalima naudoti ROLLBACK, COMMIT ir SAVEPOINT
• Sisteminiams trigeriams CREATE/ALTER/DROP
  TABLE
• sakiniai ir ALTER...COMPILE yra leidžiami

14
Veikimo tvarka

• Lenteles trigeriu, kai keiciama viena eilute,
  vykdymo seka
• DML sakinys
• INSERT INTO deparments (department_id,
  department_name, location_id)
• VALUES (400, CONSULTING, 2400)
• Iterpiama 1 eilute.
• Trigeri iškvieciantis veiksmas
• department_id department_name location_id
• -----------------------------------------------
• ......
• -----------------------------------------------

BEFORE STATEMENT trigeris
BEFORE ROW trigeris
AFTER ROW trigeris
AFTER STATEMENT trigeris
15
Veikimo tvarka

• Lenteles trigeriu, kai keiciamos kelios
  eilutes,vykdymo seka
• DML sakinys
• UPDATE employees SET salarysalary1.1 WHERE
  department_id30
• Pakeiciamos 6 eilutes.
• Trigeri iškvieciantis 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
16
DML STATEMENT trigeriu kurimo sintakse

• CREATE OR REPLACE TRIGGER trigger_name
• timing
• event1 OR event2 OR event3
• ON table_name
• trigger_body
• Pastaba trigerio vardai t.b. schemos viduje
  uninkalus

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 gt 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
Salyginiu predikatu 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

20
DML ROW trigerio kurimas

• 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 kurimo 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.salarygt15000
• 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
  senu (old) ir nauju (new) stulpelio tam tikros
  eilutes reikšmiu.
• Old ir New egzistuoja kiekviename modifikuojame
  stulpelyje pirmasis skirtas senai stulpelio
  reikšmei saugoti, antrasis naujai.

23
Trigeriu priejimo budai prie OLD ir NEW
charakteristiku

• Trigeris, iškviestas INSERT sakiniu, turi
  prasminga priejima tik prie nauju stulpelio
  reikšmiu.
• Kadangi su INSERT sukurtos eilutes sena reikšme
  yra lygi NULL
• Trigeris, iškviestas UPDATE sakiniu, turi
  priejima prie abieju stulpelio reikšmiu
• Senos ir naujos reikšmes pasiekiamos BEFORE ir
  AFTER eiles trigeriais. Nauja stulpelio reikšme
  gali buti paskirta tik BEFORE eiles 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 žymejimaipvz. 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_nameSmith
• WHERE employee_id999
• 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 salary ON employees
• FOR EACH ROW
• WHEN (NEW.job_idSA_REP)---nereikia dvitaškio
  ()
• BEGIN
• IF INSERTING THEN
• NEW.commission_pct0
• ELSIF OLD. commission_pct IS NULL THEN
• NEW.commission_pct0
• ELSE
• NEW.commission_pctOLD.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 kurimas

• 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. trigeriu ir saugomu
proceduru

• Trigeriai Proceduros
• Apibrežiami su CREATE TRIGGER
  Apibrežiamos su CREATE PROCEDURE
• Kodas saugomas duomenu žodyno Kodas
  saugomas duomenu žodyno
• viewe USER_TRIGERS viewe USER_SOURCE
• Kvieciamas neatvirai Kvieciamas atvirai
• COMMIT, SAVEPOINT, COMMIT, SAVEPOINT, ROLLBACK
• ROLLBACK Neleidžiami leidžiami
• (galima atlikti kvieciant procedura,
• Taciau nerekomenduojama del
• Pašalinu efektu)

30
Skirtumai tarp D.B. trigeriu ir Form Builder
trigeriu

• INSERT INTO employees ...

EMPLOYEES lentele
CHECK_SAL trigeris
EMPLOYEE_ID LAST_NAME JOB_ID --------------------
------------------------------- ------------------
--------------------------------


BEFORE INSERT ROW
31
Trigeriu valdymas

• D.B. trigeriu išjungimas ir pakartotinas
  ijungimas
• ALTER TRIGGER trigger_name DISABLE ENABLE
• Visu lenteles trigeriu išjungimas ir pakartotinas
  ijungimas
• ALTER TABLE table_name DISABLE ENABLE ALL
  TRIGGERS
• Lenteles 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
  lentele.

32
Trigerio testavimas

• Reikia patikrinti kiekviena duomenu operacija,
  kuri iškviecia trigeri.
• Reikia patikrinti kiekviena WHEN dali.
• Reikia patikrinti trigerio veikima tiesiogiai ant
  lentos ir netiesiogiai iš proceduros.
• Reikia patikrinti trigerio poveiki kt.
  Trigeriams.
• Reikia patikrinti kt. trigeriu poveiki trigeriui.
• Pastaba trigeris netiesiogiai gali iškviesti kt.
  trigerius.

33
Trigerio vykdymo modelis ir apribojimu
(constraint) tikrinimas

• Ivykdykite visus BEFORE STATEMENT trigerius.
• Kiekvienai paveiktai eilutei
• Ivykdykite visus BEFORE ROW trigerius.
• Ivykdykite visus AFTER ROW trigerius.
• Ivykdykite visus DML sakinius ir ivykdykite visus
  duomenu vientisumo apribojimu (constraint)
  tikrinimus.
• Apribojimai tikrinami po ROW lygio trigeriu, ne
  prieš.
• Pvz. Keisti departamento nr. kai jame yra
  darbuotojai galima jei trigeriuose pakeisim dep.
  nr. Vsiems darbuotojams
• Ivykdykite visus AFTER STATEMENT trigerius.

34
Trigerio veikimo modelis ir apribojimu
tikrinimas pvz.

• UPDATE employees SET department_id999
• WHERE employee_id170 --duomenu 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_id999
• WHERE employee_id170
• --sekmingas po trigerio suveikimo

35
Santrauka

• Procedura Paketas Trigeris

Proceduros A deklaracija
---------- ---------- ---------- ----------


Proceduros B apibrežimas
---------- ---------- ---------- ----------
Proceduros A apibrežimas
Lokalus kintamasis
36
Sisteminiai ivykiai

• Trigeriai aktyvuojasi ivykus tam tikrai
  operacijai
• DML sakiniai (DELETE, INSERT, UPDATE)
• DDL sakiniai (CREATE, ALTER, DROP)
• Duomenu bazes 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 gt 1000)
• BEGIN
• INSERT INTO Emp_log (Emp_id, Log_date,
  New_salary, Action) VALUES (new.Empno,
  SYSDATE, new.SAL, 'NEW SAL')
• END
• Ivedame SQL sakini
• 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 kunas

• Trigerio kunas yra CALL procedura ar PL/SQL
  blokas, susidedantis iš vieno ar keliu
• SQL ir PL/SQL sakiniu, kuriuos reikia
  vykdyti iškvietus trigeri
• Trigerio kuno vykdymo salyga iškviesto kuno
  sakinius vykdyti besalygiškai ar tik tuomet, kai
  patenkinta konkreti salyga

41
Pvz. Trigerio kunas

• 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
Trigeriu apribojimas mutuojanciose lentelese

• Mutuojanti lentele tai lentele, kuri tuo metu
  yra modifikuojama UPDATE, DELETE ar INSERT
  sakiniais ar lentele, galincia atnaujinti
  apribojimu DELETE CASCADE.
• Apribojimas taikomas tiems trigeriams, kurie
  naudoja FOR EACH ROW išlyga ir trigerio sakini,
  kurio rezultatas yra DELETE CASCADE.

43
Pvz. Mutuojanti lentele

• 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
Trigeriu galimybes

• Trigeriai gali buti naudojami ir kitu tiesiogiai
  nedalyvaujanciu operacijoje lenteliu
  modifikavimui, priklausomai nuo atliekamos
  operacijos pagrindineje lenteleje

45
Pvz. Kitu lenteliu modifikavimas trigeriu

• CREATE TRIGGER ModifikuotiNr   AFTER UPDATE OF
  Nr ON Tiekejai   REFERENCINGOLD AS OldTiek
    NEW AS NewTiek   FOR EACH ROW MODE DB2SQL
    UPDATE Tiekimai SET Tiekimai.TiekNr
  NewTiek.Nr   WHERE TiekNr OldTiek.Nr
• END

46
Trigeriu privalumai

• Pagrindinis trigeriu privalumas - jais apibrežtos
  dalykines taisykles saugomos duomenu bazeje
• pagreitina programavima
• palengvina dalykiniu taisykliu užtikrinima
• yra globalus

47
Trigeriu trukumai

• DB sudetingumas
• Paslepta logika
• Paslepta itaka našumui