oralce触发器

蔚落 2021-11-23 13:02 337阅读 0赞

n **触发器的分类**

DML（insert，delete，update）触发器

DDL（create table ，create view...drop...)触发器

系统触发器（与系统相关的触发器，比如用户登录，退出，启动数据库，关闭数据库）

触发器有触发事件，触发条件，触发操作三个部分构成。

n **语法介绍**

create \[or replace\] tigger tigger\_name

\{before|after|

\{insert|delete|update\[of column\[,column...\]\]\}

on\[schema.\]table\_name

\[for each row\]

\[where condition\]

begin

grigger\_body;

end;

n **快速入门****1**

在某张表（my\_emnp)添加一条数据的时候，提示‘添加了一条数据’

create table my\_emp(id number,name varchar2(32));

create or replace trigger tri1

after insert on scott.my\_emp

begin

dbms\_output.put\_line('添加了一条记录');

end;

在某张表（my\_emp）修改多条数据的时候，提示多次’修改了数据‘

n **行级触发器和语句级触发器的区别**

在创建触发器的时候，带不带for each row

create or replace trigger tri2

after update on

scott.emp

for each row *---**表示这是一个行级触发器*

begin

dbms\_output.put\_line('修改了一条数据');

end;

n **快速入门****2**

为了禁止工作人员在休息日改变员工信息，开发人员可以建立before语句触发器，从而实现数据的安全。

create or replace trigger tri2

before delete on

scott.emp

begin

if to\_char(sysdate,'day')in('星期日','星期六') then

dbms\_output.put\_line('对不起，休息日不能删除员工');

RAISE\_APPLICATION\_ERROR(-20001,'对不起，休息日不能删除员工');

end if;

end;

特别说明：RAISE\_APPLICATION\_ERROR这个过程，是oracle提供的，可以传入两个参数，第一个是自定的错误号-20000~-20999之间；第二个参数是提示一个信息。

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <table> 
     <tbody> 
      <tr> 
       <td> 
        <div> 
         <p>PROCEDURE RAISE_APPLICATION_ERROR</p> 
         <p>(error_number_in in NUMBER,error_msg_in in VARCHAR2);</p> 
         <p>&nbsp;</p> 
         <p>error_number_in[自定义]从-20000~-20999之间，这样就不会与ORACLE的任何错误大门发生冲突；error_msg_in[自定义]的长度不能超过2K，否则被截取到2K。</p> 
        </div> </td> 
      </tr> 
     </tbody> 
    </table> &nbsp;</td> 
  </tr> 
 </tbody> 
</table>

n **使用条件谓词**

当触发器中同时包含多个触发事件（insert，update，delete）时，为了在触发器代码中区分具体的触发事件，可以使用三个条件

**insterting**

**updating**

**deleting**

为了禁止工作人员在休息日改变员工信息，开发人员可以建立before语句触发器，从而实现数据的安全，在给出提示时，明确提示用户是进行的insert，update还是delete操作。

create or replace trigger tri3

before

insert or update or delete on

scott.emp

begin

case

when inserting then

dbms\_output.put\_line('休息日不要添加数据');

raise\_application\_error(-20002,'休息日不要添加数据');

when updating then

dbms\_output.put\_line('休息日不要修改数据');

raise\_application\_error(-20003,'休息日不要修改数据');

when deleting then

dbms\_output.put\_line('休息日不要删除数据');

raise\_application\_error(-20004,'休息日不要删除数据');

end case;

end;

n **使用****OLD****和****NEW**

问题：当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值。

：new 修饰符访问操作完成后列的值

：old 修饰符访问操作完成前列的值

<table> 
 <tbody> 
  <tr> 
   <td> <p>特性</p> </td> 
   <td> <p>INSERT</p> </td> 
   <td> <p>UPDATE</p> </td> 
   <td> <p>DELETE</p> </td> 
  </tr> 
  <tr> 
   <td> <p>OLD</p> </td> 
   <td> <p>NULL</p> </td> 
   <td> <p>有效</p> </td> 
   <td> <p>有效</p> </td> 
  </tr> 
  <tr> 
   <td> <p>NEW</p> </td> 
   <td> <p>有效</p> </td> 
   <td> <p>有效</p> </td> 
   <td> <p>NULL</p> </td> 
  </tr> 
 </tbody> 
</table>

案例：①在修改emp表雇员的薪水时，显示雇员工资修改前和修改后的值

②如何确保在修改员工工资不能低于原有工资

create or replace trigger tri4

before update on

scott.emp

for each row

begin

if :new.sal<:old.sal then

dbms\_output.put\_line('工资不能低于原来工资');

raise\_application\_error(-20005,'工资不能低于原来工资');

else

dbms\_output.put\_line('原来的工资是:'||:old.sal||'现在的工资是：'||:new.sal);