oracle触发器学习笔记

hebixixi

 引用http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html很强大
触发器是指特定事件发生后，触发执行用户定义好的事件。
 
例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。
 
CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; CREATE OR REPLACE TRIGGER tr_del_emp    BEFORE DELETE --指定触发时机为删除操作前触发   ON scott.emp    FOR EACH ROW   --说明创建的是行级触发器 BEGIN   --将修改前数据插入到日志记录表 del_emp ,以供监督使用。   INSERT INTO emp_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;DELETE emp WHERE empno=7788;DROP TABLE emp_his;DROP TRIGGER del_emp; 
例2：限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。
CREATE OR REPLACE TRIGGER tr_dept_timeBEFORE INSERT OR DELETE OR UPDATE ON departmentsBEGIN IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN     RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表'); END IF;END; 
 例3：限定只对部门号为80的记录进行行触发器操作
CREATE OR REPLACE TRIGGER tr_emp_sal_commBEFORE UPDATE OF salary, commission_pct       OR DELETEON HR.employeesFOR EACH ROWWHEN (old.department_id = 80)BEGIN CASE     WHEN UPDATING ('salary') THEN        IF :NEW.salary < :old.salary THEN           RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降');        END IF;     WHEN UPDATING ('commission_pct') THEN        IF :NEW.commission_pct < :old.commission_pct THEN           RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降');        END IF;     WHEN DELETING THEN          RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录');     END CASE;END; /*实例：UPDATE employees SET salary = 8000 WHERE employee_id = 177;DELETE FROM employees WHERE employee_id in (177,170);*/ 
例4：利用行触发器实现级联更新。在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id。
 
CREATE OR REPLACE TRIGGER tr_reg_couAFTER update OF region_idON regionsFOR EACH ROWBEGIN DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id                  ||'、新的region_id值是'||:new.region_id); UPDATE countries SET region_id = :new.region_id WHERE region_id = :old.region_id;END; 
 例5：在触发器中调用过程。
CREATE OR REPLACE PROCEDURE add_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 INTO job_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);END add_job_history;--创建触发器调用存储过程...CREATE OR REPLACE TRIGGER update_job_history AFTER UPDATE OF job_id, department_id ON employees FOR EACH ROWBEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate,                  :old.job_id, :old.department_id);END;