Oracle触发器trigger

发表于 | 分类于 | 本文总阅读量 次

触发器语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.]table_name | [schema.]view_name

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ]

[WHEN condition]

PL/SQL_BLOCK | CALL procedure_name;

应用场景

复杂的安全性检查

禁止在非工作时间插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
非工作时间（星球六 星期日， 非9点~18点的区间）禁止写入数据
首先要搞清楚： 触发器的类型--语句级触发器。
不管插入了多少条数据，没有必要对每一行数据都进行校验，只要不在这个时间段内，都不让插入。
*/
create or replace trigger addStafffCheck
before insert on xzm_test
declare
  -- local variables here
begin
  if to_char(sysdate, 'day') in ('星期六', '星期日') or
  to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then
    --禁止insert
    raise_application_error(-20001,'非工作时间禁止插入数据');
  end if;
end addStafffCheck;

raise_application_error用于在 plsql 使用程序中自定义不正确消息，语法为：

1
raise_application_error(error_number,message[,[truefalse]]);
  • error_number用于定义错误代码， 必须是在-20000到-20999之间的负整数；
  • message用于指定错误消息，并且该消息的长度无法超过2048字节；

数据确认

工资不能越涨越少

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
涨后的薪水不能低于涨前的薪水
1 :old 和 :new 代表同一条记录
2 :old 代表操作该行之前，这一行的值
  :new 代表操作该行之后，这一行的值
*/
create or replace trigger checkSalary
  before update
  on xzm_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;

数据库审计

创建基于值的触发器

1
2
3
4
5
6
7
8
9
10
11
12
create table xzm_record(info varchar2(256)) ;
create or replace trigger addrecord
  after update
  on xzm_test
  for each row
declare
  -- local variables here
begin
  if :new.sal > 6000 then
    insert into xzm_record values(:new.sal ||'-'|| :new.username ||'-'|| :new.job);
  end if;
end addrecord;

数据的备份和同步

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

1
2
3
4
5
6
7
8
create table xzm_test_bak as select *　from xzm_test ;
create or replace trigger databack
  after update
  on xzm_test
  for each row
begin
  update xzm_test_bak set sal = :new.sal where username = :new.username ;
end databack;

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

其他

建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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表。

1
2
3
4
5
6
7
8
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的记录进行行触发器操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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。

1
2
3
4
5
6
7
8
9
10
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;

在触发器中调用过程。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
 )
AS
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;
0%