Oracle学习笔记(十三)

十四、触发器(监听数据操作的工具)
1、什么是触发器？
数据库触发器是一个与表相关联的、存储的PL/SQL程序
作用:
每当一个特定的数据操作语句(insert、update、delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

事例：
第一个触发器：每当成功插入新员工后，自动打印“成功插入新员工”。（触发器的单词：trigger）
如何创建触发器(命令窗口)
create trigger saynewemp
after insert
on emp
declare
begin
DBMS_OUTPUT.PUT_LINE('成功插入新员工');
end;
/

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

2、触发器的应用场景
（1）复杂的安全性检查
周末不能操作数据库
（2）数据确认
涨后工资大于涨前工资才能操作
（3）实现审计功能
跟踪表上的数据操作
（4）完成数据的备份和同步

3、触发器的语法
创建触发器的语法
create orreplace trigger 触发器名
{before|after}
{delete|insert}
on 表名
[for each row [when(条件)]]
PL/SQL块
4、触发器的类型
（1）语句级触发器
--在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行.
[for each row]]没有这句就是语句级触发器
（2）行级触发器
--触发语句作用的每一条记录都被触发.在行级触发器中使用old和:new伪记录变量,识别值的状态.
[for each row]]有这句就是行级触发器

事例:
insert into emp10 select* from emp where deptno=10;

语句级触发器:针对是表影响三张表的操作,只会操作一次
行级触发器:针对是行操作三次

5、案例
触发器案例一:复杂的安全性检查
(1)禁止在非工作时间插入数据
查询周六和周日
select to_char(sysdate,'day') from dual;
思路:
1、周末：to_char(sysdate,'day') in ('星期六','星期日')
2、上班前，下班后：to_number(to_char(sysdate,'hh24')) not between 9 and 18

PL/SQL语句
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;
/
执行插入语句:
insert into emp(empno,ename,sal,deptno) values(1001,'XiaoBaiTu',3000,10);
会报错:禁止在非工作时间插入新员工
就是成功了

触发器案例二:数据的确认
(1)涨工资不能越涨越少(行级触发器)
思维:
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;
/

涨工资测试:
正常测试:
update emp set sal=sal+1 where empno=7839;
触发测试:
update emp set sal=sal-1 where empno=7839;

触发器案例三:数据库审计
(1)创建基于值的触发器
--给员工涨工资,当涨后的薪水超过6000块钱时,审计该员工的信息

--创建表,用于保存审计信息
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;

触发器案例四:数据的备份和同步
(1)利用触发器实现数据的同步部分
--创建备份表
create table emp_bak as select * from emp;
--当给员工涨完工资后,自动备份新的工资到备份表中

查看备份表中信息
select sal from emp_bak where empno=7839;

PL/SQL开发
--创建触发器
create or replace trigger sync_salary
after update
on emp
for each row
begin
--当主表更新后,自动进行更新备份
update emp_bak set sal=:new.sal where empno=:new.empno;
end;
/

更新数据到主表中
update emp set sal=sal+10 where empno=7839;