Oracle--触发器详解

╰+哭是因爲堅強的太久メ 2021-08-13 23:37 452阅读 0赞

#### 触发器 ####

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

##### 触发器的类型 #####

*  **语句级触发器**  
    在指定的操作语句操作之间或之后执行一次，不管这条语句影响了多少行。
 *  **行级触发器（FOR EACH ROW）**  
    触发语句作用的每一条记录都被触发。在行级触发器中使用  : o l d \\color\{red\}\{:old\} :old 和  : n e w \\color\{red\}\{:new\} :new 伪记录变量，识别值的状态。

**语法：**

create [or replace] trigger 触发器名
    {before | after}
    {
       delete | insert | update [列名]|
    on 表名
    [for each row [when(条件)]]
    PLSQL块

示例：在emp表创建触发器，插入数据时打印“成功插入新员工”

create trigger firsttrigger
    after insert
    on emp
    declare
    begin
      dbms_output.put_line('成功插入新员工');
    end;
    
    insert into emp(empno, ename, sal, deptno) values(1001, 'Tom', 3000,10);

![在这里插入图片描述][20190915165059220.png]

##### 触发器可用于 #####

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

示例：限制非工作时间向数据库插入数据

create or replace trigger securityEmp
    before insert on emp
    declare
    begin
      if to_char(sysdate,'day') in ('星期四','星期六','星期天')
        or to_number(to_char(sysdate, 'hh24')) not between 8 and 18 then
           raise_application_error(-200001,'不能在工作时间插入数据');
      end if;
    end;

#### 触发语句与伪记录变量的值 ####

<table> 
 <thead> 
  <tr> 
   <th align="center">触发语句</th> 
   <th align="center">:old</th> 
   <th align="center">:new</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">Insert</td> 
   <td align="center">所有字段都是空（null）</td> 
   <td align="center">将要插入的数据</td> 
  </tr> 
  <tr> 
   <td align="center">Update</td> 
   <td align="center">更新以前该行的值</td> 
   <td align="center">更新后的值</td> 
  </tr> 
  <tr> 
   <td align="center">delete</td> 
   <td align="center">删除以前该行的值</td> 
   <td align="center">所有字段都是空（null）</td> 
  </tr> 
 </tbody> 
</table>

示例：检查emp表中sal的修改值不低于原值

create or replace trigger checkSal
    before update of sal on emp
    for each row
    declare
    begin
      if :new.sal < :old.sal then
        raise_application_error(-20002,'更新后的薪水比更新前小');
      end if;
    end;  
    
    
    update emp set sal = 100 where empno = 1001;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]

#### 查询触发器、过程及函数 ####

*  select \* from user\_triggers;
 *  select \* from user\_source;

[20190915165059220.png]: /images/20210813/67c96c9ad99e4c1fa3ad5f8ec7031694.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/58002c1b780f4408ac9b1a22dc73a5b2.png