触发器 TRIGGER

TRIGGER

    触发器是一个特殊的存储过程,但并不是人为主动调用的,而是通过相关的事件触发的该过程,每当一个特定的(DML)数据库操作语句(insert,update,delete),在指定的表上发出时,oracle自动执行触发器中定义的语句序列.

触发事件：引起触发器被触发的事件。

例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

注:selete语句是没有触发器的

–第一个触发器

–每当成功插入新员工后自动打印"成功插入新员工"

CREATE TRIGGER saynewemp

AFTER  INSERT   --INSERT针对插入操作  after在插入执行之后调用PL/SQL

ON emp          --on指明那一张表

DECLARE         --PL/SQL程序以declare开始,该declare可省略

BEGIN

  dbms_output.put_line('成功插入新员工');

END;

通过如下insert事件触发:

INSERT INTO emp(empno,ename,sal,deptno) VALUES(1001,‘Tom’,3000,10);

触发器的语法:

CREATE OR REPLACE TRIGGER 触发器名

{BEFORE|AFTER}  --在之前还是之后执行

{DELETE|INSERT|UPDATE[OF 列名]}--更新操作可执行列名,比如列名是薪水,只有更新薪                  

                                 --水的时候才会被触发

ON 表名

[FOR EACH ROW [WHEN(条件)]]--指明触发器的类型:有是行级,没有则是语句级,WHEN

                              --条件,当WHEN old.deptno=10时只有部分是10号时

                              --才会触发PL/SQL块

触发器的类型:

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

        句触发器针对的是表.

**行级触发器:**触发语句执行的每一条记录都被触发,在行级触发器中使用:old和:new伪记录

       变量,识别值的状态.行级触发器针对的是行

比如:insert into emp10 select*from emp where deptno=10;

假设得到3条记录

语句级触发器: 如上语句,如果在emp10表上定义了语句级触发器,向emp10表中插入查询出来的3条记录只会触发一次触发器

行级触发器:如果在emp10上针对插入操作定义了行级触发器,会触发调用3次,行级的对象时每一行,插入三行会触发3次触发器.

案例:

准备工作,复制表不带数据(大家各自建一张自己的表,因为一张表只能12个触发器)

create table emp10 as select * from emp where 1=2;

查询一下是否复制成功:

select * from emp10;

--语句级触发器:不加for each row

create or replace trigger insertdatatrig1

after insert

on emp10

begin

dbms_output.put_line('成功插入一条数据');

end;

测试:

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

结果:以上插入语句查3条数据,仅提示了一次,说明语句级触发器针对的是语句和表本身.

–语句级触发器:加for each row

create or replace trigger insertdatatrig2

after insert

on emp10

for each row

begin

dbms_output.put_line('成功插入一条数据');

end;

测试:

insert into emp10 select * from emp where deptno=20;

结果:以上插入语句查5条数据,提示了5次,说明行级触发器针对的是每一行的数据.

–触发器触发次序

1. 执行 BEFORE语句级触发器;

2. 对与受语句影响的每一行：

   执行 BEFORE行级触发器

   执行 DML语句

   执行 AFTER行级触发器

3. 执行 AFTER语句级触发器

–行级触发器的伪记录变量:old,:new

当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、 后相关字段的值,可以交给:old和:new来记录

:old:用来记录操作前的字段值

🆕用来记录操作后的字段的值

案例:

计算涨薪前和涨薪后的薪水差是多少?

create or replace trigger subtract

after update of sal

on emp10

for each row

declare

   resu number;

begin

    resu:=:new.sal-:old.sal;

    dbms_output.put_line('涨薪前:'||:old.sal||', 涨薪后:'||:new.sal||',本次涨薪:'||resu);

end;

测试结果:

update emp10 set sal=sal+1000 where empno=7369;

控制台输出:

涨薪前:1500, 涨薪后:2500,本次涨薪:1000

–RAISE_APPLICATION_ERROR:

异常信息处理函数:是将异常信息返回给调用的客户端

之前我们一直使用的dbms_output.put_line()输出语句函数,该语句是在控制台上打印提示信息,并不能把信息返回给应用程序运行过程中的调用者.若想把相关信息返回给调用者并且中断执行操作,那么可以使用RAISE_APPLICATION_ERROR.

语法:

raise_application_error( error_numbe in number, error_msg in varchar2);

其中:

error_numbe: 用于定义不正确号，该不正确号必须在-20000到-20999之间的负整数；

error_msg: 用于指定不正确消息，并且该消息的长度无法超过2048字节；

案例: 不允许对ename是KING的员工进行任何操作

create or replace trigger executedisabled

before update or delete or insert

on emp10

for each row

when (old.ename='KING')

begin

  raise_application_error(-20001,'禁止操作该员工');

end;

测试:

update emp10 set sal=sal+100 where ename=‘KING’;

案例:禁止向数据库中插入年龄小于18岁的员工

–emp表中有hiredate字段,DATE类型

–ADD_MONTHS(时间参数,增加多少个月):在时间参数基础上增加多少个月

create or replace trigger illegalage

before insert

on emp10

for each row

begin

  if ADD_MONTHS(:new.hiredate,18*12)>sysdate then

      raise_application_error(-20002,'年龄不满18岁不能录用');

  end if;

end;

测试:

insert into emp10(empno,ename,hiredate)

values(1001,‘tom’,to_date(‘1999-10-10’,‘yyyy-mm-dd’));

–条件谓词

条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。

触发事件的条件谓语

inserting

触发事件是INSERT时，取值为true，否则为false

updating(‘字段’)

触发事件是UPDATE且修改的是指定字段时,取值为true,否则为false,指定字段可选

deleting

触发事件是DELETE时，取值为true，否则为false

案例:多重触发事件禁止10号部门员工的新增,删除和工资降薪和降奖金.

create or replace trigger test_iud_trig

before insert or update of sal,comm or delete

on emp10

for each row

begin

  case

    when  inserting then

      if :new.deptno=10 then

    raise_application_error(-20003,'10号部门不需要增加新员工');

      end if;

   when  updating('sal') then

        if :old.sal>:new.sal and :old.deptno=10 then

   raise_application_error(-20004,'10号部门的员工工资不能降');

        end if;

   when  updating('comm') then

        if nvl(:old.comm,0)>:new.comm and :old.deptno=10 then

   raise_application_error(-20005,'10号部门的员工奖金不能降');

        end if;     

   when  deleting then

        if :old.deptno=10 then

        raise_application_error(-20006,'10号部门的员工不能删除');

        end if;

   end case;

end;