Oracle学习笔记 -- day09 PL/SQL语法之触发器

一、触发器概念

1、定义

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

2、触发器的类型

（1）、语句级触发器

        在制定的操作语句执行之前或者之后执行一次，不管这条语句影响了多少行。

（2）、行级触发器

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

3、触发器的作用

（1）、数据确认

（2）、实施复杂的安全性检查

（3）、做审计，跟踪表上所做的数据操作

（4）、数据的备份和同步 

4、触发器的语法

create or replace trigger 触发器名称

after（before）  --  定义在sql执行前 / 后执行触发器

delete （update | insert）  --  指定针对哪种类型的操作执行触发器

on 表名

for each row     --  指定触发器的类型为行级触发器

[declare]

begin  

end;

二、触发器的使用

1、实施复杂的安全性检查

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

/*
禁止在非工作时间插入新员工
非工作时间：
      1、周末  to_char(sysdate,'day') in ('星期六','星期日')
      2、上班前，下班后  to_number(to_char(sysdate,'hh24')) not between 9 and 17
*/

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 17
    then
      raise_application_error(-20001,'禁止在非工作时间插入新员工');
  end if;
  
end securityemp;

select to_char(sysdate,'day') from dual;

select * from emp;

insert into emp(empno,ename) values (7434,'WINGZHE');

示例：当员工表中插入一条数据后，打印一句话 "新增一名员工"

create or replace trigger tri_emp_add
after
insert
on emp
--for each row
begin
  dbms_output.put_line('新增一名员工');
end;

insert into emp (empno) values (1);

示例：'2017-04-27'系统维护，不能修改员工表的数据

create or replace trigger tri_emp
before
delete or update or insert
on emp
--for each row
declare
 v_dateStr varchar2(30);
begin
  --1、得到今天的日期
  select to_char(sysdate,'yyyy-mm-dd')into  v_dateStr  from dual;
  --2、与'2017-04-27'比较
  if v_dateStr='2017-04-27' then
      --3、如果日期相等弹出错误窗口
      -- -20001  -29999
    raise_application_error(-20002,'2017-04-27系统维护，不能修改员工表的数据');  
  end if;
end;

2、数据的确认

示例：涨后的薪水不能少于涨前的薪水

/*
涨后的薪水不能少于涨前的薪水
*/
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, '涨后的薪水不能少于涨前的薪水');
  end if;
end checksalary;


update emp set sal=799 where sal=800;

示例：在数据插入之前对tid赋值

create table t_test(
tid number primary key,
tname varchar2(40)
)

create sequence seq_test

insert into t_test(tname) values('aaa');

select * from t_test
--在数据插入之前对tid赋值
create or replace trigger tri_test
before
insert
on t_test
for each row
declare
--:new  :old
begin
  select seq_test.nextval into :new.tid from dual;
end;