---
title: "Oracle触发器中when语句的用法"
author: "java1573"
date: "2009-06-17T17:45:19Z"
tags: []
slug: "oracle-when"
draft: false
lastmod: "2009-06-17T18:44:43Z"
id: 410145
url: "https://java1573.iteye.com/blog/410145"
---

案例：scott.emp表的销售员工资只能增加，不能减少

代码：

CREATE OR REPLACE TRIGGER tr\_emp\_upd  
BEFORE UPDATE OF sal  
ON emp  
FOR EACH ROW

**--****当更新销售员时才触发**  
WHEN (OLD.job='SALESMAN')  
DECLARE  
BEGIN  
  IF(:NEW.sal&lt;:OLD.sal) THEN  
    raise\_application\_error(-20013,'销售员工资不能降低！');  
  END IF;  
END;

运行情况：  
SQL&gt; update emp set sal=sal-1000 where job='MANAGER';

3 rows updated

SQL&gt; update emp set sal=sal-1000 where job='SALESMAN';

ORA-20013: 销售员工资不能降低！  
ORA-06512: 在"SCOTT.TR\_EMP\_UPD", line 4  
ORA-04088: 触发器 'SCOTT.TR\_EMP\_UPD' 执行过程中出错