oracle 触发器 学习复习用
时间：2021-07-01 10:21:17
帮助过：19人阅读
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_time
BEFORE INSERT OR DELETE OR UPDATE
ON departments
BEGIN
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_comm
BEFORE UPDATE OF salary, commission_pct
OR DELETE
ON HR.employees
FOR EACH ROW
WHEN (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_cou
AFTER update OF region_id
ON regions
FOR EACH ROW
BEGIN
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
)
IS
BEGIN
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 ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;
oracle 触发器 学习复习用
标签：
