Oracle Trigger Learning Review

Example 1: Set up a trigger, when the Staff table EMP table is deleted a record, the deleted records are written to the Staff table delete log table. CREATE TABLEEmp_his as SELECT *  fromEmpWHERE 1=2; CREATE OR REPLACE TRIGGERTr_del_emp beforeDELETE --specifies that the trigger time is triggered before the delete operation    onscott.emp forEach ROW--description Creates a row-level triggerBEGIN   --inserts the pre-modified data into the Log record table del_emp for monitoring use.    INSERT  intoemp_his (Deptno, empno, ename, Job, Mgr, Sal, Comm, HireDate)VALUES(: Old.deptno,: Old.empno,: Old.ename,: Old.job,:old.mgr,: old.sal,: Old.comm,: old.hiredate);END;DELETEEmpWHEREEmpno=7788;DROP TABLEEmp_his;DROP TRIGGERDel_emp; Example 2: Limit changes to the Departments table (including INSERT,DELETE,UPDATE), that is, the departments table is not allowed to be modified during non-working hours. CREATE OR REPLACE TRIGGERTr_dept_timebeforeINSERT OR DELETE OR UPDATE  onDepartmentsBEGIN IF(To_char (Sysdate,' Day')inch('Saturday','Sunday'))OR(To_char (Sysdate,'Hh24:mi') not between '08:30'  and '18:00') ThenRaise_application_error (-20001,'not working hours, can't modify departments table'); END IF;END; Example 3: Qualifying a row trigger operation only for records with department number 80. CREATE OR REPLACE TRIGGERTr_emp_sal_commbeforeUPDATE  ofsalary, commission_pctOR DELETE onhr.employees forEach ROW when(old.department_id=  the)BEGIN  Case      whenUPDATING ('Salary') Then        IF: New.salary<: Old.salary ThenRaise_application_error (-20001,'80 of the staff of the Department can not reduce the wages'); END IF;  whenUPDATING ('commission_pct') Then        IF: new.commission_pct<: old.commission_pct ThenRaise_application_error (-20002,'80 of the staff of the department can not drop bonuses'); END IF;  whenDELETING ThenRaise_application_error (-20003,'Unable to delete department 80 personnel Records'); END  Case;END; /*Example: UPDATE employees SET salary = 8000 where employee_id = 177;delete from Employees where employee_id in (177,170); 
    */Example 4: Cascading updates are implemented with row triggers.  After modifying the region_id in the main table regions (after), cascade, automatically update the child table countries the region_id of the original country in the region. CREATE OR REPLACE TRIGGERTr_reg_couafterUpdate  ofregion_id onRegions forEach ROWBEGINDbms_output. Put_Line ('the old region_id value is'||: old.region_id||', the new region_id value is'||: new.region_id); UPDATECountriesSETregion_id=: new.region_idWHEREregion_id=: old.region_id;END; Example 5: Invoking a procedure in a trigger. CREATE OR REPLACE PROCEDUREadd_job_history (p_emp_id job_history.employee_id%type, p_start_date job_history.start_date%type, p_end_date job_history.end_date%type, p_job_id job_history.job_id%type, p_department_id job_history.department_id%type) isBEGIN INSERT  intojob_history (employee_id, start_date, end_date, job_id, department_id)VALUES(p_emp_id, P_start_date, P_end_date, p_job_id, p_department_id);ENDadd_job_history;--CREATE trigger call stored procedure ...CREATE OR REPLACE TRIGGERUpdate_job_history AfterUPDATE  ofjob_id, department_id onEmployees forEach ROWBEGINadd_job_history (: old.employee_id,: Old.hire_date, Sysdate,: old.job_id,: old.department_id);END; 

Oracle Trigger Learning Review