Oracle 触发器

1.触发器简介

(1) 概念

      每当一个特定的数据操作语句（insert、update、delete）在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

(2) 示例

 create trigger saynewemp
 after insert
 on emp
 declare
 begin
    dbms_output.put_line('成功插入新员工');
 end;
 /

insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10);   

(3) 触发器的引用场景

     ① 复杂的安全性检查

     ② 数据确认

     ③ 实现审计功能

     ④ 完成数据的备份和同步

(4) 触发器的语法

CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE︱AFTER}
{DELETE︱INSERT︱UPDATE[OF列名]}
ON 表名
[FOR EACH ROW [WHEN(条件)]]
PLSQL块

(5) 触发器的类型

     语句级触发器(针对的是表)： 在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。

     行级别触发器(针对的是行)： 触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量，识别值得状态。

2.触发器的应用

(1) 复杂的安全性检查

--触发器应用场景1:实施复杂的安全性检查
--禁止在非工作时间插入新员工
/*
1.周末:to_char(sysdate, 'day')in('星期六','星期日')
2.上班前,x下班后:to_number(to_char(sysdate,’hh24’))not between 9 and 18
*/
create or replace trigger securityemp
before insert
on emp
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;
/

(2) 数据确认

/*
触发器应用场景二:数据的确认
涨后的薪水不能少于涨前的薪水
1.:old和:new 代表同一条记录
2.:old 表示操作该行之前,这一行的值
  :new表示操作该行之后,这一行的值
*/
create or replace trigger checksalary
before update
on emp
for each row
begin
 --if涨后的薪水<涨前的薪水 then
 if:new.sal<:old.sal then
  raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水:'||:new.sal ||' 涨前的薪水: ' ‖:old.sal);
 end if;
end;
/

(3) 实现审计功能

/*
触发器应用场景三:数据库的审计--->基于值的审计功能
给员工涨工资,当涨后的薪水超过6000块钱时候,审计该员工的信息
*/
--创建表,a用于保存审计信息
create table audit_info
(
 information varchar2(200)
);

create or replace trigger do_audit_emp_salary
after update
on emp
for each row
begin
  --当涨后的薪水大于6000,插入审计信息
if:new.sal > 6000 then
  insert into audit_info values(:new.empno‖' '‖:new.ename‖' '‖:new.sal);
 end if;
end;
/

update emp set sal = sal+2000;

(4) 完成数据的备份和同步

/*
触发器应用场景四:数据的备份和同步
当给员工涨工资后，自动备份新的工资到备份表中
*/
create or replace trigger sync_salary
after update
on emp
for each row
begin
  --当主表更新后，自动更行备份表
  update emp_back set sal=:new.sal where empno=:new.empno;
end;
/

create table emp_back as select * from emp;