Oracle中的触发器

以下演示使用拥有dba权限的用户，复用scott用户中的部分表;

一、概念介绍

触发器：数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的 增删改 数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列,无法开发者调用。

简单来说，触发器指指定一个规则，当做任何 增删改 操作的时候，只要满足该规则，自动触发，无需调用。

1). 触发器的功能

1. 数据确认
2. 实施复杂的安全性检查
3. 做审计，跟踪表上所做的数据操作等
4. 数据的备份和同步

2). 触发器的类型

1. 语句级触发器: 在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。

   • 特点：不包含有 for each row 语句的触发器

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

   • 特点：包含有 for each row 语句的触发器;加上 for each row 是为了使用 :old 或者 :new 对象 (表示一行记录，插入前的记录 插入后的记录)

3). 触发器完整语法

create [or replace] trigger 触发器名
    {before | after}
    {delete | insert | update [of 列名]}
    on 表名
    [for each row [when(条件)]]
[declare
    变量声明]
begin
    plsql 块
end [触发器名];

4). 知识点扩充

1. for each row [where()] 添加用于限制触发 触发器 对应行的规则

限制条件了解即可，很少使用！

2. 触发器中触发语句与伪记录变量的值

二、语句级触发器

1). 案例演示

1. 测试准备

----------------------

1. 描述表结构的伪代码
person{
   pid number(20),
   pname varchar(10),
   gerder number(1),
}

2. 需要使用的 序列【相当于MySQL中的主键自增】
create sequence s_person;

----------------------

2. 语句级触发器案例：插入一条记录，输出一个 ‘新员工入职’

create or replace trigger t1
    after -- 表示在操作之后触发 触发器
    insert
    on person
declare

begin
    dbms_output.put_line('一个新员工入职');
end;

3. 插入一条记录测试

insert into person (pid, pname, gender) values (s_person.nextval, '小红', 0);
commit;

三、行级触发器

1). 案例演示

1. 知识补充；抛异常语法规则

raise_application_error(-20001~20999之间的状态码, '错误提示消息');

2. 行级触发器案例演示：不能给员工降薪，如果降薪，抛异常

create or replace trigger t2
    before -- 在操作之前触发
    update
    on emp
    for each row
declare

begin
    if :old.sal > :new.sal then -- :old与new.代表插入前与插入后的一条记录对象
       raise_application_error(-20001, '不能给员工降薪!');
    end if;
end;

3. 触发t2,等待报错

update emp set sal = sal - 1 where empno = 7788;

4. 补充演示

-- 不能在星期五修改员工7788号的信息
select * from emp;

create or replace trigger vaildInsert
       before update on emp
       for each row
declare
       weekend varchar2(10);
begin
       select to_char(sysdate, 'day') into weekend from dual;
       dbms_output.put_line(weekend);
       if weekend in ('friday   ') and :old.empno = 7788 then
           raise_application_error(-20001, '周末还加班?');
       end if;
end;

-- 测试
update emp set ename = 'xxx' where emp.empno = 7788;
commit;

四、实战：触发器实现主键自增 【使用行级触发器】

在用户做插入操作之前，从序列s_person中拿到即将插入的数据，再给该数据的主键列赋值，要使用新行记录对象 :new

1. 创建触发器

create or replace trigger auid
    before
    insert
    on person
    for each row
declare

begin
   select s_person.nextval into :new.pid from dual;
end;

2. 使用触发器auid实现自增

insert into person (pname, gender) values ('小兰', 0);
commit;

3. 测试：强制为主键赋值，看触发器能否解决冲突

insert into person(pid, pname, gender) values(1, '紫琪', 0);

经过查询，发现被触发器覆盖了插入的主键数据！