Databázové systémy II

1. Databázové systémy II Přednáška č. 6 RNDr. David Žák, Ph.D. Fakulta elektrotechniky a informatikydavid.zak@upce.cz

2. Obsah • Triggery pro DML příkazy nad tabulkami. • Triggery pro databázové a klientské události. • Příklady použití triggerů. • Příklady využití triggerů a omezení pro dodržení integritních omezení. Databázové systémy II - př. 6

3. Triggery Trigger je uložená procedura, která se spouští za přesně definovaných událostí. Můžeme tedy říci, že trigger je množina příkazů, které se automaticky provedou v případě předem definované operace (INSERT, DELETE, UPDATE) s daty (a to buď před či po vlastní operaci). Použití: • kontrola zadaných dat, • zajištění referenční integrity. Procedury, triggery, funkce jsou uložené přímo v databázi spolu s dalšími databázovými strukturami jako jsou tabulky, pohledy, sekvence, indexy, … To znamená, že do databáze se umísťují nejenom data, ale i aplikační logika pro jejich zpracování. To umožňuje jednodušší distribuci, přispívá ke spolehlivosti. Procedury jsou uloženy v databázi v předkompilované podobě. Databázové systémy II - př. 6

4. Triggery Databázové systémy II - př. 6

5. Triggery Použití: • kontrola zadaných dat • zajištění komplexní referenční integrity v databázi • automatické generováníodvozené hodnotyhodnot sloupců • zamezení invalidním transakcím • zajištění komplexní bezpečnostní autorizace • implementace business pravidel • zajištění logování událostí • poskytování auditů • zajištění synchronní replikace tabulek • generování statistik přístupu k tabulkám • modifikacedat v tabulce, když DML příkaz používá pohled • publikace informací o událostech a příkazech do jiných aplikací Databázové systémy II - př. 6

6. Triggery Zajištění integrity Triggery by se měly využít pouze v případě, kdy: • není možné použít následujících integritních omezení: • NOT NULL, UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • DELETE CASCADE • DELETE SET NULL • tabulky nejsou v jedné databázi atd. Databázové systémy II - př. 6

7. Triggery – DML Syntaxe CREATE [OR REPLACE] TRIGGER <název triggeru> { BEFORE | AFTER } [INSTEAD OF] { INSERT | UPDATE | DELETE } ON <název tabulky> [FOR EACH ROW [WHEN <podmínka provedení triggeru>]] BEGIN … výkonná sekce …………………………. END; Musíme určit: • akci, která trigger aktivuje, • kdy se trigger aktivuje - před či po, • zda se trigger spouští pro každý zpracovávaný řádek FOR EACH ROW nebo jen jednou, • případně, zda trigger nahrazuje operaci, která jej spustila INSTEAD OF(pouze pro pohledy) • více spouštěcích příkazů lze oddělit klazulí OR (např. INSERT OR DELETE) Databázové systémy II - př. 6

8. Triggery Příklady CREATE [OR REPLACE] TRIGGERpotvrzeni_1 AFTER UPDATE ONucitele BEGIN DBMS_OUTPUT.PUT_LINE(`Záznam(y) byl(y) úspěšně změněn(y)`); END; CREATE [OR REPLACE] TRIGGERpotvrzeni_all AFTER UPDATE ONucitele FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(`Záznam byl úspěšně změněn`); END; Databázové systémy II - př. 6

9. Triggery Příklad CREATE [OR REPLACE] TRIGGERzaloha_pracovniku BEFORE DELETE ONpracovnici FOR EACH ROW BEGIN INSERT INTO byvali_pracovnici VALUES (:OLD.id_prac, :OLD.jmeno, …); END; • :OLD označuje staré hodnoty (použití pro příkazy DELETE, UPDATE) • :NEW označuje nové hodnoty (použití pro příkazy INSERT, UPDATE) Databázové systémy II - př. 6

10. Triggery – poznámky k použití Upřesnění: • INSTEAD OF triggery jsou jen pro pohledy a mají implicitně nastaveno FOR EACH ROW • AFTER a BEFORE triggery nelze pro pohledy použít • pro akci UPDATE je možné specifikovat sloupce, při jejichž změně bude trigger aktivován pomocí klauzule UPDATE OF seznam sloupců BEFORE DELETE OR INSERT OR UPDATE OF ename ON Emp_tab • příklad volání procedury v těle triggeru: CREATE TRIGGER hr.salary_check BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees FOR EACH ROW WHEN (new.job_id <> 'AD_VP') CALL check_sal(:new.job_id, :new.salary, :new.last_name); Databázové systémy II - př. 6

11. Triggery - doporučení Doporučení pro použití BEFORE a AFTER triggerů: • použijteBEFORE row triggerypro modifikování řádku před zápisem dat na disk • použijteAFTER row triggeryk získání a zajištění operací používajících ROWID • BEFORE triggery jsou více efektivní, protože nevyžadují dvojí čtení dat • BEFORE triggery umožňují změnit hodnotu :newpřed provedením vlastní akce :new.id := 1000 + :new.id Databázové systémy II - př. 6

12. Triggery – odvozené hodnoty sloupců Triggery mohou odvozovat hodnoty sloupců automaticky na základě hodnot zadaných v příkazech INSERT nebo UPDATE. Tento typ triggerů je vhodný pro případ, kdy odvozené hodnoty jsou vkládány do stejných řádků jako hodnoty uvedené v příkazech INSERT nebo UPDATE. Pro toto řešení je nezbytné použít BEFORE row triggery. Příklad: CREATE OR REPLACE TRIGGER Derived BEFORE INSERT OR UPDATE OF EnameON Emp99 /* Před změnou sloupce ENAME urči hodnotu UPPERNAME Omezte uživatele ke změnám tohoto sloupce přímo: */ FOR EACH ROW BEGIN :new.Uppername := UPPER(:new.Ename); END; Databázové systémy II - př. 6

13. Triggery – poznámky k použití • Přílišné využití triggerů může způsobit nepřehlednost aplikace (jejich přehlížení) • Nepoužívejte rekursivní triggery • Pozor při použití kaskádních triggerů • Nepoužívejte triggery tam, kde si můžete pomoci například omezeními (CHECK na vkládané hodnoty). • Nedělejte kód triggerů delší než 32KB. Pokud by měl být delší, použijte procedur a funkcí. • Není možné použít příkazy COMMIT, ROLLBACK nebo SAVEPOINT v těle triggeru. Protože DDL příkazy způsobují implicitní COMMIT, nemohou být také proto použity v triggerech, s výjimkou příkazů CREATE, ALTER, DROP TABLE a ALTER...COMPILE pro systémové triggery. Databázové systémy II - př. 6

14. Triggery - INSTEAD OF INSTEAD OF triggery umožní provést změny do tabulek, na které se pohledyodkazují. CREATE VIEW emp_locations AS SELECT e.employee_id, e.last_name || ', ' || e.first_name name, d.department_name department, l.city city, c.country_name country FROM employees e, departments d, locations l, countries c WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id ORDER BY last_name; Příklad INSTEAD OF triggeru update_name_view_trigger pro změnu jména zaměstnance. CREATE OR REPLACE TRIGGER update_name_view_trigger INSTEAD OF UPDATE ON emp_locations BEGIN -- allow only the following update(s) UPDATE employees SET first_name = substr( :NEW.name, instr( :new.name, ',' )+2), last_name = substr( :NEW.name, 1, instr( :new.name, ',')-1) WHERE employee_id = :OLD.employee_id; END; Databázové systémy II - př. 6

15. Triggery - modifikace CREATE OR REPLACE TRIGGER new_evaluation_trigger BEFORE INSERT ON evaluations FOR EACH ROW BEGIN :NEW.evaluation_id := evaluations_seq.NEXTVAL; END; Databázové systémy II - př. 6

16. Triggery - odstranění Pro odstranění triggeru použijte příkaz DROP DROP TRIGGER triggername; Databázové systémy II - př. 6

17. Triggery – deaktivace a aktivace(blokování a povolení) V určitých případech může být vhodné/požadované dočasně deaktivovat funkci triggeru, například při vkládání řádků odkazujících na neexistující objekty, importu větších objemů dat (obnova dat ze zálohy atd.) bez prodlevy. Pro konkrétní trigger: ALTER TRIGGER triggername DISABLE; ALTER TRIGGER triggername ENABLE; Pro všechny triggery svázané s konkrétní tabulkou: ALTER TABLE tablename DISABLE ALL TRIGGERS; ALTER TABLE tablename ENABLE ALL TRIGGERS; Databázové systémy II - př. 6

18. Triggery – pohled USER_TRIGGERS Příkaz pro zjištění triggerů v uživatelově schématu: SELECT Trigger_type, Triggering_event, Table_name FROM USER_TRIGGERS WHERE Trigger_name = 'REORDER'; TYPE TRIGGERING_STATEMENT TABLE_NAME ---------------- -------------------------- ------------ AFTER EACH ROW UPDATE INVENTORY Databázové systémy II - př. 6

19. Triggery – pohled USER_TRIGGERS Příkazy pro zjištění triggerů a jejich obsahu v uživatelově schématu: SELECT Trigger_body FROM USER_TRIGGERS WHERE Trigger_name = 'REORDER'; TRIGGER_BODY -------------------------------------------- DECLARE x NUMBER; BEGIN SELECT COUNT(*) INTO x FROM Pending_orders WHERE Part_no = :new.Part_no; IF x = 0 THEN INSERT INTO Pending_orders VALUES (:new.Part_no, :new.Reorder_quantity, sysdate); END IF; END; Databázové systémy II - př. 6

20. Triggery - auditování Triggery jsou obvykle použity k doplnění vestavěných auditovacích funkcí Oracle databáze. Ačkoli triggery mohou ukládat informace obdobně těm ukládaným příkazem AUDIT, používejte triggery jenom v případě, kdy jsou potřeba více podrobné auditovací informace - například pro value-based auditpro každý řádek. Občas se používaní příkazu AUDIT považuje za bezpečnostní audit, kdežto používání triggerů za finanční audit. DML a DDL audit: • Standardní auditovací vlastnosti umožňují audit DML a DDL příkazů v souvislosti se všemi objekty a strukturami schématu • Triggery umožňují audit DML příkazů nad tabulkami a audit DDL příkazů na úrovni SCHEMA nebo DATABASE. Více lze najít na:http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm Databázové systémy II - př. 6

21. Triggery pro databázové události Databázové události STARTUP SHUTDOWN DB_ROLE_CHANGE SERVERERROR Databázové systémy II - př. 6

22. Triggery pro klientské události Klientské události BEFORE / AFTER ALTER BEFORE / AFTER DROP BEFORE / AFTER ANALYZE BEFORE / AFTER ASSOCIATE STATISTICS BEFORE / AFTER AUDIT BEFORE / AFTER NOAUDIT BEFORE / AFTER COMMENT BEFORE / AFTER CREATE BEFORE / AFTER DDL BEFORE / AFTER DISASSOCIATE STATISTICS BEFORE / AFTER GRANT AFTER LOGON / BEFORE LOGOFF BEFORE / AFTER RENAME BEFORE / AFTER REVOKE AFTER SUSPEND BEFORE / AFTER TRUNCATE Příklad CREATE OR REPLACE TRIGGER On_Logon AFTER LOGON ON The_user.Schema BEGIN Do_Something; END; Databázové systémy II - př. 6

23. Triggery - LOGON a LOGOFF Vytváření LOGON a LOGOFF Triggerů , které monitorujíuživatele databáze do logovací tabulky. Vytvořme tabulku hr_users_log pro ukládání informací o událostech LOGON a LOGOFF. Následně vytvoříme triggery note_hr_logon_trigger a note_hr_logoff_trigger pro zápis informací o těchto událostech do tabulky. CREATE TABLE hr_users_log (user_name VARCHAR2(30), activity VARCHAR2(20),event_date DATE); CREATE OR REPLACE TRIGGER note_hr_logon_trigger AFTER LOGON ON HR.SCHEMA BEGIN INSERT INTO hr_users_log VALUES (USER, 'LOGON', SYSDATE); END; CREATE OR REPLACE TRIGGER note_hr_logoff_trigger BEFORE LOGOFF ON HR.SCHEMA BEGIN INSERT INTO hr_users_log VALUES (USER, 'LOGOFF', SYSDATE); END; Databázové systémy II - př. 6

24. ROLLBACK in PL/SQL Následující příklad vkládá informace o zaměstnancido třech různých databázových tabulek. Jestliže nějaký INSERT se pokouší uložit duplicitní číslo zaměstnance, je vyvolána předdefinovaná výjimka DUP_VAL_ON_INDEX. Pro zajištění integrity je třeba zajistit, že v případě vyvolání výjimky jsou změny ve všech tabulkách odvolány, k čemuž je použit příkaz ROLLBACK.NELZE použít v triggeru. CREATE TABLE emp_name AS SELECT employee_id, last_nameFROM employees; CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id); CREATE TABLE emp_sal AS SELECT employee_id, salary FROM employees; CREATE UNIQUE INDEX empsal_ix ON emp_sal (employee_id); CREATE TABLE emp_job AS SELECT employee_id, job_id FROM employees; CREATE UNIQUE INDEX empjobid_ix ON emp_job (employee_id); DECLARE emp_id NUMBER(6);emp_lastname VARCHAR2(25); emp_salary NUMBER(8,2);emp_jobid VARCHAR2(10); BEGIN SELECT employee_id, last_name, salary,job_id INTO emp_id, emp_lastname, emp_salary, emp_jobid FROM employeesWHERE employee_id = 120; INSERT INTO emp_name VALUES (emp_id, emp_lastname); INSERT INTO emp_sal VALUES (emp_id, emp_salary); INSERT INTO emp_job VALUES (emp_id, emp_jobid); EXCEPTION WHEN DUP_VAL_ON_INDEX THENROLLBACK; DBMS_OUTPUT.PUT_LINE('Inserts were rolled back'); END; Databázové systémy II - př. 6

25. Triggery – vyvolání chyby Příklad demonstruje vyvolání chyb, která zamezí vložení trpaslíka do tabulky trpaslíků, pokud jich v této tabulce již bude 7: CREATE OR REPLACE TRIGGER omezeni_trpasliku BEFORE INSERT on TRPASLICI FOR EACH ROW DECLARE v_pocet_trpasliku NUMBER; BEGIN SELECT COUNT(*) INTO v_pocet_trpasliku FROM trpaslici; IF v_pocet_trpasliku >= 7 THEN RAISE_APPLICATION_ERROR(-20005,'Trpaslíků může být maximálně sedm'); END IF; END omezeni_trpasliku; Databázové systémy II - př. 6

26. Triggery - příklad Příklad 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; Trigger bude aktivován pro příkazy: DELETEFROMEmp_tab; INSERT INTO Emp_tab VALUES ( ... ); INSERT INTO Emp_tab SELECT ... FROM ... ; UPDATEEmp_tabSET ... ; Databázové systémy II - př. 6

27. Triggery - kaskádování Databázové systémy II - př. 6

28. FOREIGN KEY – DML operacezajištěné omezeními Databázové systémy II - př. 6

29. FOREIGN KEY – DML operaces nutností zajištění triggery Databázové systémy II - př. 6

30. Triggery Příklad create or replace Trigger Dodavatele_update_cascade after update of dodavatel_id on dodavatele for each row begin -- Změní hodnoty cizího klíče pokud hodnota v rodičovské tabulce byla změněna if (:old.dodavatel_id != :new.dodavatel_id) then begin update Produkty set dodavatel_id = :new.dodavatel_id where dodavatel_id = :old.dodavatel_id; end; end if; end; Databázové systémy II - př. 6

31. Omezení - constraint • Při importu dat (většinou z jedné databáze do jiné) mohou nastat problémy s integritními omezeními. • Příkladem je nevhodné pořadí vytvářených tabulek a dat do nich vkládaných (tj. například není vložen řádek do nadřízené tabulky, na níž se daný řádek v podřízené tabulce odkazuje). • Řešením je vypnutí omezení během importu a poté je opět obnovit. • Je třeba mít na paměti, že pokud data nebudou splňovat požadavky omezení, nebude možné již tato omezení obnovit. V takovém případě je nezbytné nejdříve data upravit a teprve poté aktivovat omezení, například: • ALTER TABLE tablenameDISABLE CONSTRAINT constraint_name; • ALTER TABLE tablenameENABLE CONSTRAINT constraint_name; Databázové systémy II - př. 6

32. Omezení - constraint • Jakékoli omezení zvyšuje náklady na práci s řádky tabulky • (INSERT, UPDATE, DELETE) • Zjednodušeně lze říci že nároky rostou v pořadí • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • TRIGGERY Databázové systémy II - př. 6

33. Triggery Vytvoření triggeru v SQL developeru http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28843/tdddg_triggers.htm#BABECIAE http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#i1007097 Databázové systémy II - př. 6