Oracle触发器trigger详解(2)-木庄网络博客

触发器应用二 数据确认

工资不能越涨越少

分析：首先确认下触发器的类型，因为需要对每一条数据进行确认，所以是个行级触发器。需要for each row

/**
涨后的薪水不能低于涨前的薪水

1 :old 和 :new 代表同一条记录
2 :old 代表操作该行之前，这一行的值
 :new 代表操作该行之后，这一行的值
*/

create or replace trigger checkSalary
 before update 
 on xgj_test 
 for each row
declare
 -- local variables here 没有变量声明的话，declare可以省略
begin

 --- if 涨后的薪水 < 涨前的薪水 then 如何表示呢 ？
 if :new.sal < :old.sal then

 raise_application_error(-20002,'涨后的薪水:'|| :new.sal ||'小于涨前的薪水：'||:old.sal);
 end if;
end checkSalary;

触发器应用三 数据库审计

创建基于值的触发器

create table xgj_record(info varchar2(256)) ;
create or replace trigger addrecord
 after update
 on xgj_test 
 for each row
declare
 -- local variables here
begin

 if :new.sal > 6000 then 
 insert into xgj_record values(:new.sal ||'-'|| :new.username ||'-'|| :new.job);
 end if;

end addrecord;

触发器应用四 数据的备份和同步

当给员工涨完工资后，自动备份到备份表中

create table xgj_test_bak as select *　from xgj_test ;

create or replace trigger databack
 after update
 on xgj_test
 for each row 

begin

 update xgj_test_bak set sal = :new.sal where username = :new.username ;

end databack;

oracle中还有个利用快照备份，是异步的。而利用触发器，是同步的。

其他

建立一个触发器, 当职工表 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;

限制对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;

限定只对部门号为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);
*/

利用行触发器实现级联更新。在修改了主表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;

在触发器中调用过程。

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

返回前面的内容