Oracle触发器

1.定义：

 数据库触发器是一个与表相关联、存储PL/SQL语句的“东西”。每当一个特定的数据操作语句（insert、update、delete）在指定的表上发出时，Oracle自动执行触发器中定义的语句序列（当 '触发条件' 成立时，其语句就会 '自动执行'   ）

触发器分类：DML触发器、DDL触发器、Databse触发器、instead of 替代触发器（参考2有详解）

作用： 保护数据的安全，监视对数据的各种操作，如'日志记录': 对重要表的 '修改' 进行记录

例如：当员工信息插入后，自动输出“插入成功”的信息。

create or replace trigger empTrigger
after insert on emp 
for each row
declare
 -- 这里存放本地变量
begin
 dbms_output.put_line('插入成功！');
end empTrigger;

触发器和存储过程的区别：
   主要区别：'调用运行方式不同'
   (1) 存储过程: '用户'、'应用程序'、'触发器' 来调用
   (2) 触发器:   '自动执行'（满足 '触发条件'），与其它无关
 

2.语法：

create [or replace] trigger 触发器名（tr_）
   触发时间 {before | after}           -- view 中是 instead of
   触发事件 {insert | update | delete} -- dml、ddl、database
on 触发对象                            -- table、view、schema、database 
   触发频率 {for each row}            -- 行级触发器。默认：语句级触发器,指触发一次
   [follows 其它触发器名]              -- 多个触发器执行的 前后顺序
   [when 触发条件]                     --仅当 '触发条件' 为 True 时，才执行 pl/sql 语句
begin
   pl/sql 语句;
end;

/*
功能：after insert or update or delete 时，执行语句
命名：tr_aiud_student_info
*/
create or replace trigger scott.tr_aiud_student_info
   after insert or update or delete on scott.student_info
   for each row
begin
   case
      when inserting then
         dbms_output.put_line('插入成功！');
      when updating then
         dbms_output.put_line('更新成功！');
      when deleting then
         dbms_output.put_line('删除成功！');
      else
         dbms_output.put_line('无操作！');
   end case;
end;
/
--测试
update scott.student_info t
   set t.sex = '1'
 where t.sno <= 3;

3.一些关键字的使用：

follows：

前提：触发器的执行是否需要指定 '先后顺序'？
1. 若不需要，则无需 follows 关键字
2. 若需要
   (1) before 和 after 能否区分，若能，则无需 follows 关键字
   (2) 最后，才用 follows 区分

--触发器1
create or replace trigger scott.tr_ai_student_info_1
   after insert on scott.student_info
   for each row
begin
   if inserting then
      dbms_output.put_line('插入操作 1');
   end if;
end;
/
--（顺序：先触发器1，再触发器2）
create or replace trigger scott.tr_ai_student_info_2
   after insert on scott.student_info
   for each ROW
   FOLLOWS scott.tr_ai_student_info_1
begin
   if inserting then
      dbms_output.put_line('插入操作 2');
   end if;
end;
/

 when：

1. when：增加触发条件
2. when 中的 new、old 是不带 : 的哦（不是 :new、:old）

create or replace trigger scott.tr_ad_student_info
   after delete on scott.student_info
   for each row
   when (old.sno = 1) -- sno = 1 的记录禁止被删除！
begin
   if deleting then
      raise_application_error(-20001, '此条记录禁止删除，sno = ' || :old.sno);
   end if;
end;
/
--测试
delete from scott.student_info t where t.sno = 1;

inserting、updating、deleting：

1. 前提条件：无 
2. 表示含义
   inserting = insert 操作
   updating  = update 操作
   deleting  = delete 操作

 now、old：

1. 前提条件：for each row
2. 表示含义
   :new = 触发后的值
   :old = 触发前的值
3. 说明 
   (1) new、old 均为 '默认值', 常用, 一般无需更改
       referencing new as new old as old       
   (2) 若想要更改，如：new => new_new，old => old_old  
       referencing new as new_new old as old_old

:new ， :old值分布情况

	insert	updata	delete
：new	√	√	×
：old	×	√	√

create or replace trigger scott.tr_au_student_info
   after update on scott.student_info
   for each row
begin
   -- 旧值
   dbms_output.put_line('old.sno = ' || :old.sno);
   dbms_output.put_line('old.name = ' || :old.name);
   dbms_output.put_line('old.sex = ' || :old.sex);
   dbms_output.put_line('------');
   -- 新值
   dbms_output.put_line('new.sno = ' || :new.sno);
   dbms_output.put_line('new.name = ' || :new.name);
   dbms_output.put_line('new.sex = ' || :new.sex);
end;
/
--测试
update scott.student_info t
   set t.name = 'name',
       t.sex = '2'
 where t.sno = 1;

--输出窗口
old.sno = 1
old.name = 张三
old.sex = 女
------
new.sno = 1
new.name = name
new.sex = 2

4、DML触发器基本要点

       （1）触发时机：指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。

       （2）触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。

       （3）条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。
       1）INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。
       2）UPDATING [（column_1,column_2,…,column_x）]：当触发事件是UPDATE时，如果修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x是可选的。
       3）DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。
       （4）解发对象：指定触发器是创建在哪个表、视图上。
       （5）触发类型：是语句级还是行级触发器。
       （6）触发条件：由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表。

查询触发器

show triggers;

删除触发器

drop trigger 触发器名;

5.触发器实例：

1）禁止在非工作时间插入数据。

create or replace trigger addEmpInfoCheck
 before insert on emp_info 
declare
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 addEmpInfoCheck;

        raise_application_error用于在plsql使用程序中自定义不正确消息。该异常只在数据库端的子程序（流程、函数、包、触发器）中运用，而无法在匿名块和客户端的子程序中运用。语法为raise_application_error(error_number,message[,[truefalse]])。其中error_number用于定义不正确号，该不正确号必须在-20000到-20999之间的负整数；message用于指定不正确消息，并且该消息的长度无法超过2048字节。

2）涨薪后的工资应该大于涨薪前的工资。

create or replace trigger checkSalary
 before update 
 on salary_info 
 for each row
declare
 --没有变量声明的话，declare可以省略
begin 
 if :new.sal < :old.sal then
 raise_application_error(-20002,'涨后的薪水:'|| :new.sal ||'小于涨前的薪水：'||:old.sal);
 end if;
end checkSalary;

（3）创建基于值的触发器

create table xzw_test(info varchar2(256));
 
create or replace trigger addData
 after update
 on xzw_test 
 for each row
declare
begin
 
 if :new.sal > 6000 then 
 insert into xzw_test values(:new.sal ||'-'|| :new.username ||'-'|| :new.job);
 end if;
 
end addData;

参考：

1.Oracle中的触发器（trigger）_象在舞的博客-CSDN博客_oracle触发器触发条件

2.(1条消息) Oracle 触发器详解（trigger）_越努力越幸运再努力的博客-CSDN博客_oracle 触发器 

3.(1条消息) 触发器（Trigger）_yefufeng的博客-CSDN博客_触发器 

4.oracle 触发器管理（以及行级触发器中有两个伪变量 :new 与 :old 的使用方法）（三个谓词inserting、deleting、updating的使用方法）_爱睡觉的小馨的博客-CSDN博客_oracle触发器old和new的用法