oracle 觸發器 學習複慣用

標籤：

例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; 

 

oracle 觸發器 學習複慣用