Oracle 触发器语法及实例

柔情只为你懂 2022-08-12 00:25 215阅读 0赞

**[Oracle][]触发器语法(一)**

一 **Oracle触发器语法**

触发器是特定事件出现的时候，自动执行的代码块。类似于[**存储**][Link 1]过程，触发器与存储过程的区别在于:存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的。

**功能：**

1、 允许/限制对表的修改

2、 自动生成派生列，比如自增字段

3、 强制数据一致性

4、 提供审计和日志记录

5、 防止无效的事务处理

6、 启用复杂的业务逻辑

触发器触发时间有两种：after和before。

**1、触发器的语法：**

CREATE \[OR REPLACE\] TIGGER触发器名 触发时间 触发事件

ON表名

\[FOR EACH ROW\]

BEGIN

pl/sql语句

END

其中：

触发器名：触发器对象的名称。

由于触发器是自动执行的，因此该名称只是一个名称，没有实质的用途。

触发时间：指明触发器何时执行，该值可取：

before---表示在数据库动作之前触发器执行;

after---表示在数据库动作之后出发器执行。

触发事件：指明哪些数据库动作会触发此触发器：

insert：数据库插入会触发此触发器;

**[Oracle][]触发器语法(二)**

update：修改会触发此触发器;

delete：数据库删除会触发此触发器。

表 名：数据库触发器所在的表。

for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

**2、举例：**

下面的触发器在更新表auths之前触发，目的是不允许在周末修改表：

<table style="table-layout: fixed; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="word-wrap: break-word;"><span style="color: #000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;create&nbsp;triggerauth_secure&nbsp;before&nbsp;insert&nbsp;or&nbsp;update&nbsp;or&nbsp;delete&nbsp;//对整表更新前触发 <p>&nbsp;</p> <p>on&nbsp;auths</p> <p>begin</p> <p>if(to_char(sy<a href="http://wiki.ccw.com.cn/index.php/SD" rel="nofollow"><span style="color: #0000cc;"><strong>sd</strong></span></a>ate,'DY')='<a href="http://topic.ccw.com.cn/corpCenter/379.html" rel="nofollow"><span style="color: #0000cc;"><strong>SUN</strong></span></a>'</p> <p>RAISE_APPLI<a href="http://topic.ccw.com.cn/corpCenter/249.html" rel="nofollow"><span style="color: #0000cc;"><strong>CA</strong></span></a>TION_ERROR(-20600,'不能在周末修改表auths');</p> <p>end&nbsp;if;</p> <p>end</p> </span></td> 
  </tr> 
 </tbody> 
</table>

例子：

<table style="table-layout: fixed; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="word-wrap: break-word;"><span style="color: #000000;">&nbsp;&nbsp;CREATE&nbsp;OR&nbsp;REPLACE&nbsp;TRIGGER&nbsp;CRM.T_SUB_USERINFO_AUR_NAME&nbsp;AFTER&nbsp;UPDATE&nbsp;OF&nbsp;STAFF_NAME <p>&nbsp;</p> <p>ON&nbsp;CRM.T_SUB_USERINFO</p> <p>REFERENCING&nbsp;OLD&nbsp;AS&nbsp;OLD&nbsp;NEW&nbsp;AS&nbsp;NEW</p> <p>FOR&nbsp;EACH&nbsp;ROW</p> <p>declare</p> <p>begin</p> <p>if&nbsp;:NEW.STAFF_NAME!=:OLD.STAFF_NAME&nbsp;then</p> <p>begin</p> </span></td> 
  </tr> 
 </tbody> 
</table>

*  客户投诉

<table style="table-layout: fixed; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="word-wrap: break-word;"><span style="color: #000000;">&nbsp; update&nbsp;T_COMPLAINT_MANAGE&nbsp;set&nbsp;SERVE_NAME=:NEW.STAFF_NAME&nbsp;where&nbsp;SERVE_SEED=:OLD.SEED;</span></td> 
  </tr> 
 </tbody> 
</table>

*  客户关怀

<table style="table-layout: fixed; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="word-wrap: break-word;"><span style="color: #000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; update&nbsp;T_CUSTOMER_CARE&nbsp;set&nbsp;EXECUTOR_NAME=:NEW.STAFF_NAME <p>&nbsp;</p> <p>where&nbsp;EXECUTOR_SEED=:OLD.SEED;</p> </span></td> 
  </tr> 
 </tbody> 
</table>

*  客户服务

<table style="table-layout: fixed; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="word-wrap: break-word;"><span style="color: #000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;update&nbsp;T_CUSTOMER_SERVICE&nbsp;set&nbsp;EXECUTOR_NAME=:NEW.STAFF_NAME <p>&nbsp;</p> <p>where&nbsp;EXECUTOR_SEED=:OLD.SEED;</p> <p>end;</p> <p>end&nbsp;if;</p> <p>end&nbsp;T_sub_userinfo_aur_name;</p> <p>/</p> </span></td> 
  </tr> 
 </tbody> 
</table>

**[Oracle][]触发器语法(三)**

**二** **Oracle****触发器详解**

开始：

<table style="table-layout: fixed; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="word-wrap: break-word;"><span style="color: #000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; create&nbsp;triggerbiufer_employees_department_id <p>&nbsp;</p> <p>beforeinsertorupdateofdepartment_idonemployees</p> <p>referencingol<a href="http://wiki.ccw.com.cn/index.php/DAS" rel="nofollow"><span style="color: #0000cc;"><strong>das</strong></span></a>old_value&nbsp;newasnew_value</p> <p>for&nbsp;each&nbsp;row</p> <p>when&nbsp;(new_value.department_id&lt;&gt;80&nbsp;)</p> <p>begin</p> <p>:new_value.commission_pct&nbsp;:=0;</p> <p>end;</p> <p>/</p> </span></td> 
  </tr> 
 </tbody> 
</table>

**1、触发器的组成部分：**

1、 触发器名称

2、 触发语句

3、 触发器限制

4、 触发操作

**1.1、触发器名称**

命名习惯：

employees表名

department\_id列名

**1.2、触发语句**

比如：

表或视图上的DML语句

DDL语句

**[Oracle][]触发器语法(四)**

关闭或启动,startup shutdown等等

<table style="table-layout: fixed; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="word-wrap: break-word;"><span style="color: #000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; before&nbsp;insert&nbsp;or&nbsp;update <p>&nbsp;</p> <p>of&nbsp;department_id</p> <p>on&nbsp;employees</p> <p>referencing&nbsp;old&nbsp;as&nbsp;old_value</p> <p>new&nbsp;as&nbsp;new_value</p> <p>for&nbsp;each&nbsp;row</p> </span></td> 
  </tr> 
 </tbody> 
</table>

说明：

1、 无论是否规定了department\_id，对employees表进行insert的时候

2、 对employees表的department\_id列进行update的时候

**1.3、触发器限制**

限制不是必须的。此例表示如果列department\_id不等于80的时候，触发器就会执行。

其中的new\_value是代表更新之后的值。

**1.4、触发操作**

是触发器的主体

<table style="table-layout: fixed; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="word-wrap: break-word;"><span style="color: #000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;begin <p>&nbsp;</p> <p>:new_value.commission_pct&nbsp;:=0;</p> <p>end;</p> </span></td> 
  </tr> 
 </tbody> 
</table>

主体很简单，就是将更新后的commission\_pct列置为0

触发：

<table style="table-layout: fixed; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="word-wrap: break-word;"><span style="color: #000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;insert&nbsp;into&nbsp;employees(employee_id,last_name,first_name,hire_date,job_id,email, <p>&nbsp;</p> <p>department_id,salary,commission_pct&nbsp;)</p> <p>values(&nbsp;12345,’Chen’,’Donny’,&nbsp;sy<a href="http://wiki.ccw.com.cn/index.php/SD" rel="nofollow"><span style="color: #0000cc;"><strong>sd</strong></span></a>ate,&nbsp;12,&nbsp;‘donny@hotmail.com’,60,10000,.25);</p> <p>select&nbsp;commission_pct&nbsp;from&nbsp;employees&nbsp;where&nbsp;employee_id=12345;</p> </span></td> 
  </tr> 
 </tbody> 
</table>

触发器不会通知用户，便改变了用户的输入值。

[Oracle]: http://topic.ccw.com.cn/corpCenter/407.html
[Link 1]: http://www.ccw.com.cn/server/topic/stor/