Distributed Database Applications

1. Distributed Database Applications COSC 5050 Week Six

2. Outline • Triggers • DML triggers • Mutating tables • Other triggers Distributed Database Applications

3. Trigger • Trigger • A named PL/SQL block stored in a database and executed implicitly when a triggering event occurs • Triggering event • DML statement, DDL statement, and database event executed or occurred on database • A trigger can fire before or after a triggering event Distributed Database Applications

4. Use of Triggers • Perform validation on changes being made to tables • Automate maintenance of the database • Apply rules about acceptable database administration activity Distributed Database Applications

5. Type of Trigger • DML statement • DDL statement • Database event • Instead of • Suspended statement Distributed Database Applications

6. DML Trigger • Fires when records are inserted into, updated within, or deleted from a table Distributed Database Applications

7. DML Trigger • DML Trigger concepts • Before trigger • After trigger • Statement-level trigger • For a SQL statement as a whole • Row-level trigger • For a single affected row • NEW and OLD pseudo-record • Only available within a DML trigger • WHEN clause Distributed Database Applications

8. DML Trigger • Transaction participation • DML triggers participate in the transaction from which they were fired • If trigger raise an exception? • If trigger performs any DML itself? • Cannot issue COMMIT or ROLLBACK from within a DML trigger • Unless autonomous transaction is used Distributed Database Applications

9. Twelve DML Triggers • Action • Insert, update, and delete • Level • Statement-level and row-level • Timing • Before and after Distributed Database Applications

10. Twelve Triggers Distributed Database Applications

11. Trigger Name • Trigger name must be unique within a schema Distributed Database Applications

12. Statement vs. Row Trigger -- an after statement level trigger CREATE OR REPLACE TRIGGER statement_trigger AFTER update ON employee BEGIN DBMS_OUTPUT.PUT_LINE('After update statement level'); END; / -- an after row level trigger CREATE OR REPLACE TRIGGER row_trigger AFTER update ON employee FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('After update row level'); END; / Distributed Database Applications

13. Before vs. After Trigger -- a before statement level trigger CREATE OR REPLACE TRIGGER before_statement_trigger BEFORE update ON employee BEGIN DBMS_OUTPUT.PUT_LINE('Before update statement level'); END; / -- an after statement level trigger CREATE OR REPLACE TRIGGER after_statement_trigger AFTER update ON employee BEGIN DBMS_OUTPUT.PUT_LINE('After update statement level'); END; / Distributed Database Applications

14. Trigger for Various DML -- after insert statement CREATE OR REPLACE TRIGGER after_insert_statement AFTER INSERT ON employee BEGIN DBMS_OUTPUT.PUT_LINE('After insert statement'); END; / -- after update statement CREATE OR REPLACE TRIGGER after_update_statement AFTER UPDATE ON employee BEGIN DBMS_OUTPUT.PUT_LINE('After update statement'); END; / -- after delete statement CREATE OR REPLACE TRIGGER after_delete_statement AFTER DELETE ON employee BEGIN DBMS_OUTPUT.PUT_LINE('After delete statement'); END; / Distributed Database Applications

15. When Clause • When clause must evaluate to TRUE for the trigger to fire create or replace trigger bef_ins_ceo_comp after update on ceo_compensation for each row when ( old.compensation * 1.2 < new.compensation ) pragma autonomous_transaction; begin insert into ceo_comp_history values (:new.name, :old.compensation, :new.compensation, ‘after update’, sysdate); commit; end; Distributed Database Applications

16. When Clause -- an after row level trigger CREATE OR REPLACE TRIGGER row_when_trigger AFTER update ON employee FOR EACH ROW WHEN (OLD.code > 1000 and NEW.code > OLD.code) BEGIN DBMS_OUTPUT.PUT_LINE('After update when code < 1000'); DBMS_OUTPUT.PUT_LINE('Old value = ' || :OLD.code || ', ' ); DBMS_OUTPUT.PUT_LINE('New value = ' || :NEW.code || '. ' ); END; / CREATE OR REPLACE TRIGGER valid_when_clause BEFORE INSERT ON frame FOR EACH ROW WHEN ( TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 ) ... Distributed Database Applications

17. Pseudo-Record • Data structures that like records for a row level trigger • NEW -- new values, for insert and update • OLD -- original values, for update and delete • Is of a type triggering_table%rowtype • Preface with a colon within trigger • :new.salary • No colon with WHEN clause Distributed Database Applications

18. Pseudo-Record • Use of REFERENCING clause Create or replace trigger audit_update after update on frame referencing old as prior_to_cheat new as after_cheat for each row Begin insert into frame_audit (bowler_id, game_id, frame_number, old_strike, new_strike, …) values (:after_cheat.bowler_id, :after_cheat.game_id, :after_cheat.game_id, :prior_to_cheat.strike, :after_cheat.strike, …) End; Distributed Database Applications

19. Determining DML Action • INSERTING • UPDATING • DELETING PACKAGE DBMS_STANDARD IS FUNCTION INSERTING RETURN BOOLEAN; FUNCTION DELETING RETURN BOOLEAN; FUNCTION UPDATING RETURN BOOLEAN; FUNCTION UPDATING (COLNAM VARCHAR2) RETURN BOOLEAN; … END DBMS_STANDARD Distributed Database Applications

20. Determining DML Action alter table employee add ( created_by varchar2(20) , created_date date , modified_by varchar2(20) , modified_date date ); create or replace trigger three_in_one before delete or insert or update on employee for each row begin if inserting then :new.created_by := user; :new.created_date := sysdate; elsif deleting then dbms_output.put_line('audit_deletion(user, sysdate)'); elsif updating then :new.modified_by := user; :new.modified_date := sysdate; end if; end; Distributed Database Applications

21. Multiple Triggers • Multiple triggers of the same type for a single table • A single trigger is easier to maintain • Multiple same type triggers reduce parse and execution time • No guarantee of the order of firing Distributed Database Applications

22. Data Dictionary • USER_TRIGGERS SELECT TRIGGER_NAME, STATUS FROM USER_TRIGGERS; SELECT TRIGGER_BODY FROM USER_TRIGGERS WHERE TRIGGER_NAME = '&1'; SELECT TEXT FROM USER_SOURCE WHERE NAME = '&1'; Distributed Database Applications

23. Mutating Tables • Mutating table • A table having a DML statement issued against it • Mutating table error • Trigger tries to read or modify such a table • When a row level trigger tries to examine or change a table that is already undergoing change Distributed Database Applications

24. Mutating Tables • Table is mutating when it • Is being modified by insert, update, or delete statement • Is being read by Oracle to enforce a referential integrity constraint • Is being updated by Oracle to enforce a delete cascade constraint • What tables mutate? • When do tables mutate? Distributed Database Applications

25. What Tables Mutate • For any insert, update, or delete statement, the table that is the target will mutate • Foreign key constraints, both parent and child tables will mutate whenever: • Update parent (parent update restrict) • Delete from parent (parent delete restrict) • Insert into child (child insert restrict) • Update child (child update restrict) • When delete cascade, the parent table and all relative child tables will mutate Distributed Database Applications

26. Mutating Problem • The problem occurs when a trigger attempts to read from a table or update a table while it is mutating • A row trigger cannot issue a read/write against a table that is mutating • Statement level trigger is fine Distributed Database Applications

27. When Tables Mutate create table tab (n number); -- The before insert statement create or replace trigger tab_bis before insert on tab --for each row declare row_count number; begin dbms_output.put_line( 'enter before insert statement trigger'); select count(*) into row_count from tab; dbms_output.put_line( 'leave before insert statement trigger'); end; / Distributed Database Applications

28. When Tables Mutate • Triggers generate mutating table error Distributed Database Applications

29. When Tables Mutate Before Statement No Error Before Statement No Error Before Statement Mutating Error Before Row Mutating Error Before Row Mutating Error Before Row Mutating Error After Row Mutating Error After Row Mutating Error After Row Mutating Error After Statement No Error After Statement No Error After Statement Mutating Error Delete Cascade • Think of four triggers as a series of events: Distributed Database Applications

30. DDL Triggers • Create table / alter table • Create index • Create trigger / drop trigger Distributed Database Applications

31. CREATE OR REPLACE TRIGGER town_crier AFTER CREATE ON SCHEMA BEGIN DBMS_OUTPUT.PUT_LINE('I believe you have created something!'); END; / SET SERVEROUTPUT ON DROP TABLE a_table; CREATE TABLE a_table (col1 NUMBER); CREATE INDEX an_index ON a_table(col1); DROP FUNCTION a_function; CREATE FUNCTION a_function RETURN BOOLEAN AS BEGIN RETURN(TRUE); END; / /*-- a CRLF to flush DBMS_OUTPUTs buffer */ EXEC DBMS_OUTPUT.PUT_LINE(CHR(10)); Distributed Database Applications

32. Dropping the Undroppable CREATE OR REPLACE TRIGGER ON_CREATE BEFORE CREATE ON SCHEMA BEGIN RAISE_APPLICATION_ERROR( -20000, 'ERROR: OBJECTS CANNOT BE CREATED IN THIS DATABASE.'); END; CREATE OR REPLACE TRIGGER UNDROPPABLE BEFORE DROP ON SCHEMA BEGIN RAISE_APPLICATION_ERROR( -20000, 'YOU CANNOT DROP ME! '); END; Distributed Database Applications

33. Database Event Triggers • Database event triggers fire whenever database wide events occur • START UP • SHUTDOWN • SERVERERROR • LOGON • LOGOFF Distributed Database Applications

34. Database Event Triggers CREATE OR REPLACE TRIGGER error_log AFTER SERVERERROR ON SCHEMA DECLARE v_errnum NUMBER; v_now DATE := SYSDATE; v_counter NUMBER := 1; BEGIN LOOP v_errnum := ORA_SERVER_ERROR(v_counter); EXIT WHEN v_errnum = 0; INSERT INTO error_log( username, error_number, sequence, timestamp) VALUES(USER, v_errnum, v_counter, v_now); v_counter := v_counter + 1; END LOOP; END; / Distributed Database Applications

35. INSTEAD OF Triggers • View • A custom representation of data • Can be referred to as a “stored query” • Non-updateable view • Set operations such as union, union all, intersect, minus • Group functions such as avg, count, max, min,sum • Group by or having clauses • The distinct operator Distributed Database Applications

36. INSTEAD OF Triggers create view dept_summary as select department.dept_num, dept_name, count(ssn) as total_employees from department inner join employee on department.dept_num = employee.dept_num group by department.dept_num, dept_name; delete from dept_summary where dept_num = 2; Distributed Database Applications

37. INSTEAD OF Triggers create or replace trigger dept_summary_del instead of delete on dept_summary for each row begin update department set dept_mgr_ssn = null where dept_num = :old.dept_num; delete from employee where dept_num = :old.dept_num; delete from department where dept_num = :old.dept_num; end; delete from dept_summary where dept_num = 2; delete from dept_summary where dept_num = 15; delete from dept_summary where dept_num = 1; Distributed Database Applications

38. INSTEAD OF Triggers • Complexity of designing an INSTEAD OF trigger • The relationship among tables • Effect of a trigger design • Underlying tables may not be limited by the view query Distributed Database Applications

39. AFTER SUSPEND Triggers • Fire whenever a statement is suspended • Space issue • Suspended/resumable statement Distributed Database Applications

40. Maintaining Triggers • Disable triggers • Enable triggers • Drop triggers • View triggers • USER_TRIGGERS • Validity of triggers SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS; Distributed Database Applications

41. Administration of Triggers • Enable and disable • ALTER TABLE PURCHASE_ORDER ENABLE ALL TRIGGERS; • ALTER TABLE PURCHASE_ORDER DISABLE ALL TRIGGERS; • ALTER TRIGGER PURCHASE_ORDER_BIR DISABLE; • ALTER TRIGGER PURCHASE_ORDER_BIR ENABLE; • Drop • DROP TRIGGER PURCHASE_ORDER_BIR; Distributed Database Applications

42. Administration of Triggers • If the table is dropped, the table’s database triggers are dropped as well • Be careful to use “replace” • Replace existing function, procedure, or package with the same name • Associate a different table with your trigger, an error message is generated Distributed Database Applications

43. Homework • Create a PL/SQL update trigger on the employee table that caps the salary increment by 10%.   • What are the eventual update values for the PL/SQL commands? • Create an audit table and implement the audit_deletion trigger logic. • Lab activities. Distributed Database Applications