1 / 46

Creating Database Triggers

Creating Database Triggers. Objectives. After completing this lesson, you should be able to do the following: Describe database triggers and their use Create database triggers Describe database trigger firing rules Remove database triggers. Overview of Triggers.

gretchen-roth
Download Presentation

Creating Database Triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating Database Triggers

  2. Objectives After completing this lesson, you should be able to do the following: • Describe database triggers and their use • Create database triggers • Describe database trigger firing rules • Remove database triggers

  3. Overview of Triggers • A trigger is a PL/SQL block that executes implicitly whenever a particular event takes place. • A trigger can be either a database trigger or an application trigger.

  4. Designing Triggers: Guidelines • Design triggers to: • Perform related actions • Centralize global operations • Do not design triggers: • Where functionality already exists • Which duplicate other triggers

  5. Database Trigger: Example Application SQL> INSERT INTO EMP 2 . . .; EMP table CHECK_SAL trigger EMPNO 7838 7698 7369 7788 ENAME KING BLAKE SMITH SCOTT JOB PRESIDENT MANAGER CLERK ANALYST SAL 5000 2850 800 3000

  6. Creating Triggers • Trigger timing • For table: BEFORE, AFTER • For view: INSTEAD OF • Triggering event: INSERT, UPDATE, or DELETE • Table name: On table or view • Trigger type: Row or statement • When clause: Restricting condition • Trigger body: PL/SQL block

  7. Trigger Components Trigger timing: When should the trigger fire? • BEFORE: Execute the trigger body before the triggering DML event on a table. • AFTER: Execute the trigger body after the triggering DML event on a table. • INSTEAD OF: Execute the trigger body instead of the the triggering statement. Used for VIEWS that are not otherwise modifiable.

  8. Trigger Components Triggering user event: What DML statement will cause the trigger to execute? • INSERT • UPDATE • DELETE

  9. Trigger Components Trigger type: Should the trigger body execute for each row the statement affects or only once? • Statement: The trigger body executes once for the triggering event. This is the default. • Row: The trigger body executes once for each row affected by the triggering event.

  10. Trigger Components Trigger body: What action should the trigger perform? The trigger body is a PL/SQL block or a call to a procedure.

  11. BEFORE statement trigger BEFORE row trigger AFTER row trigger AFTER statement trigger Firing Sequence Firing sequence of a trigger on a table, when a single row is manipulated: DML Statement SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'EDUCATION', 'NEW YORK'); Triggering Action DEPTNO 10 20 30 40 DNAME ACCOUNTING RESEARCH SALES OPERATIONS LOC NEW YORK DALLAS CHICAGO BOSTON 50 EDUCATION NEW YORK

  12. BEFORE statement trigger BEFORE row trigger AFTER row trigger BEFORE row trigger AFTER row trigger BEFORE row trigger AFTER row trigger AFTER statement trigger Firing Sequence Firing sequence of a trigger on a table, when many rows are manipulated: SQL> UPDATE emp 2 SET sal = sal * 1.1 3 WHERE deptno = 30; EMPNO 7839 7698 7788 ENAME KING BLAKE SMITH DEPTNO 30 30 30

  13. Syntax for Creating Statement Triggers CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name trigger_body

  14. Creating Statement Triggers Using SQL*Plus SQL> CREATE OR REPLACE TRIGGER secure_emp 2 BEFORE INSERT ON emp 3 BEGIN 4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR 5 (TO_CHAR(sysdate,'HH24') NOT BETWEEN 6 '08' AND '18') 7 THEN RAISE_APPLICATION_ERROR (-20500, 8 'You may only insert into EMP during business hours.'); 9 END IF; 10 END; 11 /

  15. Example1 Statement-Level Trigger: Fires once before (or after) the entire table is updated. Note: The following triggers writes to myLog file the date in which salaries have been last changed. CREATE OR REPLACE TRIGGER trg_remember_salary_change_date BEFORE UPDATE OF salary ON Employee BEGIN insert into myLog values(sysdate, 'Salaries raised '); END;

  16. Example2 Row-Level Trigger. The next example shows how to puts in myLog file the SSN and extra money given in a salary raise. CREATE OR REPLACE TRIGGER trg_remember_how_much_more_money AFTER UPDATE OF SLARY ON Employee DECLARE moneyDiff NUMBER; BEGIN moneyDiff := :old.SALARY - :new.SALARY; insert into myLog values ( :new.SSN, moneyDiff ); END;

  17. Creating Statement Triggers Using Procedure Builder

  18. Testing SECURE_EMP SQL> INSERT INTO emp (empno, ename, deptno) 2 VALUES (7777, 'BAUWENS', 40); INSERT INTO emp (empno, ename, deptno) * ERROR at line 1: ORA-20500: You may only insert into EMP during normal hours. ORA-06512: at "A_USER.SECURE_EMP", line 4 ORA-04088: error during execution of trigger 'A_USER.SECURE_EMP'

  19. Using Conditional Predicates CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT OR UPDATE OR DELETE ON emp BEGIN IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR (TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18') THEN IF DELETING THEN RAISE_APPLICATION_ERROR (-20502, 'You may only delete from EMP during normal hours.'); ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (-20500, 'You may only insert into EMP during normal hours.'); ELSIF UPDATING ('SAL') THEN RAISE_APPLICATION_ERROR (-20503, 'You may only update SAL during normal hours.'); ELSE RAISE_APPLICATION_ERROR (-20504, 'You may only update EMP during normal hours.'); END IF; END IF; END;

  20. example • Given the following monitoring table which determine the number of deleting,inserting and updating operations performed by different users on a table and The max. no. of operations performed is as follows Write a trigger which is raised when the sal is updated and don’t allow updating operations to be more than the max. number allowed.

  21. Creating a Row Trigger 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

  22. Creating Row Triggers Using SQL*Plus SQL> CREATE OR REPLACE TRIGGER RESTRICT_SALARY 2 BEFORE INSERT OR UPDATE OF sal ON emp 3 FOR EACH ROW 4 BEGIN 5 IF NOT (:NEW.JOB IN ('MANAGER' , 'PRESIDENT')) 6 AND :NEW.SAL > 5000 7 THEN 8 RAISE_APPLICATION_ERROR 9 (-20202, 'EMPLOYEE CANNOT EARN THIS AMOUNT'); 10 END IF; 11 END;

  23. Creating Row Triggers Using Procedure Builder

  24. SQL>CREATE OR REPLACE TRIGGER audit_emp_values 2 AFTER DELETE OR INSERT OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN 5 INSERT INTO audit_emp_table (user_name, 6 timestamp, id, old_last_name, new_last_name, 7 old_title, new_title, old_salary, new_salary) 8 VALUES (USER, SYSDATE, :OLD.empno, :OLD.ename, 9 :NEW.ename, :OLD.job, :NEW.job, 10 :OLD.sal, :NEW.sal ); 11 END; 12 / Using Old and New Qualifiers

  25. Audit_Emp_Table USER_NAME EGRAVINA NGREENBE TIMESTAMP 12-NOV-97 10-DEC-97 ID NULL 7844 OLD_LAST_NAME NULL MAGEE NEW_LAST_NAME HUTTON TURNER Continuation OLD_TITLE NULL CLERK NEW_TITLE ANALYST SALESMAN OLD_SALARY NULL 1100 NEW_SALARY 3500 1100

  26. Restricting a Row Trigger SQL>CREATE OR REPLACE TRIGGER derive_commission_pct 2 BEFORE INSERT OR UPDATE OF sal ON emp 3 FOR EACH ROW 4 WHEN (NEW.job = 'SALESMAN') 5 BEGIN 6 IF INSERTING 7 THEN :NEW.comm := 0; 8 ELSIF :OLD.comm IS NULL 9 THEN :NEW.comm := 0; 10 ELSE :NEW.comm := :OLD.comm * (:NEW.sal/:OLD.sal); 11 END IF; 12 END; 13 /

  27. Example Salaries can only be increased, never decreased. The present salary of employee 105 is 5000. Test your answer by decreasing the salary of employee 105 to 4500. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF salary ON employees FOR EACH ROW WHEN (NEW.salary < OLD.salary) BEGIN RAISE_APPLICATION_ERROR(-20002,'Salary may not be reduced'); END check_sal; / So when you issue the following statement you will get UPDATE employees SET salary = 4500 WHERE employee_id = 105;

  28. INSERT TABLE1 INSTEAD OF Trigger UPDATE TABLE2 MY_VIEW An INSTEAD OF Trigger Application SQL> INSERT INTO my_view 2 . . .;

  29. Creating an INSTEAD OF Trigger CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF event1 [OR event2 OR event3] ON view_name [REFERENCING OLD AS old | NEW AS new] [FOR EACH ROW] trigger_body

  30. EMPNO 7836 7782 7934 7566 ENAME KING CLARK MILLER JONES SAL 5000 2450 1300 2975 DEPTNO 10 10 10 20 DNAME NEW YORK NEW YORK NEW YORK DALLAS TOT_DEPT_SAL 8750 8750 8750 10875 INSERT into EMPLOYEES UPDATE DEPARTMENTS DEPTNO 10 20 30 DNAME ACCOUNTING RESEARCH SALES TOT_DEPT_SAL 9750 10875 9400 EMPNO 7939 7698 7782 9001 ENAME KING BLAKE CLARK ABBOTT SAL 5000 2850 2450 1000 Creating an INSTEAD OF Trigger INSERT INTO EMP_DETAILS (EMPNO, ENAME, SAL, DEPTNO) VALUES (9001,'ABBOTT',1000,10) INSTEAD OF INSERT into EMP_DETAILS

  31. Differentiating Between Triggers and Stored Procedures Triggers Defined with CREATE TRIGGER Data dictionary contains source and p-code Implicitly invoked COMMIT, SAVEPOINT, ROLLBACK not allowed Procedures Defined with CREATE PROCEDURE Data dictionary contains source and p-code Explicitly invoked COMMIT, SAVEPOINT, ROLLBACK allowed

  32. Differentiating Between Triggers and Form Builder Triggers SQL> INSERT INTO EMP 2 . . .; EMP table CHECK_SAL trigger EMPNO 7838 7698 7369 7788 ENAME KING BLAKE SMITH SCOTT JOB PRESIDENT MANAGER CLERK ANALYST SAL 5000 2850 800 3000 BEFOREINSERT row

  33. Managing Triggers Disable or reenable a database trigger: ALTER TRIGGER trigger_nameDISABLE | ENABLE Disable or reenable all triggers for a table: ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS Recompile a trigger for a table: ALTER TRIGGER trigger_name COMPILE

  34. DROP TRIGGER Syntax To remove a trigger from the database, use the DROP TRIGGER syntax: DROP TRIGGER trigger_name Example SQL> DROP TRIGGER secure_emp; Trigger dropped

  35. Trigger Test Cases • Test each triggering data operation, as well as nontriggering data operations • Test each case of the WHEN clause • Cause the trigger to fire directly from a basic data operation, as well as indirectly from a procedure • Test the effect of the trigger upon other triggers • Test the effect of other triggers upon the trigger

  36. Trigger Execution Model and Constraint Checking 1. Execute all BEFORE STATEMENT triggers 2. Loop for each row affected a. Execute all BEFORE ROW triggers b. Execute the DML statement and perform integrity constraint checking c. Execute all AFTER ROW triggers 3. Complete deferred integrity constraint checking 4. Execute all AFTER STATEMENT triggers

  37. AUDIT_EMP_TRIG FOR EACH ROW Increment Variables AUDIT_EMP_TAB AFTER STATEMENT Copy then Reset Variables AUDIT_TABLE A Sample Demonstration DML into EMP VAR_PACK Package

  38. Demonstration: Triggers CREATE OR REPLACE TRIGGER audit_emp_trig AFTER UPDATE or INSERT or DELETE on EMP FOR EACH ROW BEGIN IF DELETING THEN var_pack.set_g_del(1); ELSIF INSERTING THEN var_pack.set_g_ins(1); ELSIF UPDATING ('SAL') THEN var_pack.set_g_up_sal(1); ELSE var_pack.set_g_upd(1); END IF; END audit_emp_trig; CREATE OR REPLACE TRIGGER audit_emp_tab AFTER UPDATE or INSERT or DELETE on EMP BEGIN audit_emp; END audit_emp_tab;

  39. Demonstration: VAR_PACK Package Specification CREATE OR REPLACE PACKAGE var_pack IS -- these functions are used to return the -- values of package variables FUNCTION g_del RETURN NUMBER; FUNCTION g_ins RETURN NUMBER; FUNCTION g_upd RETURN NUMBER; FUNCTION g_up_sal RETURN NUMBER; -- these procedures are used to modify the -- values of the package variables PROCEDURE set_g_del (p_val IN NUMBER); PROCEDURE set_g_ins (p_val IN NUMBER); PROCEDURE set_g_upd (p_val IN NUMBER); PROCEDURE set_g_up_sal (p_val IN NUMBER); END var_pack;

  40. Demonstration: Procedure CREATE OR REPLACE PROCEDURE audit_emp IS v_del NUMBER := var_pack.g_del; v_ins NUMBER := var_pack.g_ins; v_upd NUMBER := var_pack.g_upd; v_up_sal NUMBER := var_pack.g_up_sal; BEGIN IF v_del + v_ins + v_upd != 0 THEN UPDATE audit_table SET del = del + v_del, ins = ins + v_ins, upd = upd + v_upd WHERE user_name = user AND tablename = 'EMP' AND column_name IS NULL; END IF; IF v_up_sal != 0 THEN UPDATE audit_table SET upd = upd + v_up_sal WHERE user_name = user AND tablename = 'EMP' AND column_name = 'SAL'; END IF; -- resetting global variables in package VAR_PACK var_pack.set_g_del (0); var_pack.set_g_ins (0); var_pack.set_g_upd (0); var_pack.set_g_up_sal (0); END audit_emp;

  41. EXAMPLE: • The following trigger is fired when a new dependent record is inserted. If the record corresponds to a new born child several gifts are ordered for the baby. If the new entry is not attached to any employee the insertion is rejected.

  42. create or replace TRIGGER New_Dependent BEFORE insert on Dependent FOR EACH ROW DECLARE mName varchar(20); BEGIN select Lname into mName from employee where ssn = :new.Essn; if (months_between(sysdate,:new.Bdate) < 12 ) then insert into Mylog values ( 'Personnel Dept-->', 'Send NEW_BABY card to '|| mName); insert into Mylog values ( 'Marketing Dept.-->', 'Mail a $100 stock certificate to '|| mName); insert into mylog values ( 'Purchasing Dpt.-->', 'Mail a one-year-order of diapers to '|| mName); end if; EXCEPTION when others then -- raise_application_error(..) rolls back all changes -- made so far. Therefore the following insert into... -- would not be done. “insert into -- mylog values ('ERROR ', :new.essn || ' not found');” raise_application_error (-20001, 'Invalid Insert - no ancestor'); END;

  43. Summary Procedure Package Trigger xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxx Procedure A declaration Procedure B definition Procedure A definition Local variable

  44. Practice Overview This practice covers the following topics: • Creating statement and row triggers • Creating advanced triggers to add to the capabilities of the Oracle database

More Related