Oracle触发器 慕课网学习笔记

Oracle触发器 慕课网学习笔记

1. 什么是触发器

  数据库触发器是一个与表相关联的、存储的PL/SQL程序。
  作用：每当一个特定的数据操作语句（insert、update、delete）在指定的表上发出时，Oracle自动的执行触发器中定义的语句序列。

2. 触发器的应用场景##

复杂的安全性检查
数据的确认
数据库审计
完成数据的备份和同步

3. 触发器语法##

CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE|AFTER}
{DELETE|INSERT|UPDATE[OF 列名]}
ON 表名
[FOR EACH ROW[WHEN(条件)]] //用来指明触发器的类型是什么 有就是行级
PLSQL块

4. 触发器类型

比如这条SQL：Insert into emp10 select * from emp where deptno=10;
像emp10表中插入了3条记录；
语句级触发器：针对是表 执行一次
在指定的操作语句操作之前或者之后执行一次，不管这条语句影响了多少行。
行级触发器：针对是行 执行三次 for each row
触发语句作用的每一条记录都被触发。在行级触发器中使用：old和：new伪记录变量，识别值的状态

5. 案例

第一个触发器创建

每当成功插入新员工后，自动打印“成功插入新员工”（触发器单词：trigger）

create trigger saynewemp
after insert
on emp
declare  //没有定义变量可删除不写
begin
    dbms_output.put_line('成功插入新员工');
end;
/

例1：复杂的安全性检查

---

禁止在非工作时间插入新员工

---

to_char(sysdate,'day') in ('星期六','星期日'）//周末
to_number(to_char(sysdate,'hh24')) not between 9 and 18//下班时间
select sysdate from dual//取出系统当前时间
select to_char(sysdate,'day') from dual  //取出当前星期几
select to_number(to_char(sysydate,'hh24')) from dual//取出当前时间

---

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
  raise_application_error(-20001,'禁止在非工作时间插入新的员工');// -20000 到 -20999 之间
  end if;
end;
/

例2：数据确认

---

涨工资不能越涨越少
1 :old和:new代表的是同一条记录
2 :old表示操作该行之前，这一行值
3 :new表示操作该行之后，这一行值

---

create or replace trigger checksalary
before update
on emp
begin
    for each row
    if :new.sal<:old.sal then
    raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水 涨后薪水:'||:new.sal||' 涨前薪水:'||:old.sal );
end;
/

例3：数据库的审计—>基于值的审计

---

给员工涨工资，当涨后的薪水超过6000块的时候，审计员工信息

---

//创建表，用户保存审计信息
create table audit_info
(
information varchar2(200)
)

create or replace trigger do_audit_emp_salary
after update
on emp
for each row
begin
  if :old.sal>6000 then
     insert into audit_info values(:new.empno||' '||:new.ename||' '||:new.sal);
  end if;
end;
/

例4：数据的备份和同步

//利用触发器实现数据的同步
//创建备份表
create table emp_back as select * from emp;

//当员工张完工资后，自动备份新的工资到备份中去
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;
/