Databázové systémy II

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

2. Obsah • 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 – 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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