时间戳

首页 > 解决方案 > Raise_application_error() 会停止执行吗？（停止在表中插入/删除/更新）

oracle - Raise_application_error() 会停止执行吗？（停止在表中插入/删除/更新）

问题描述

Raise_application_error() 使用“删除前”触发器可以防止和停止从表中删除？

标签： oracleexceptionplsqltriggers

解决方案

是的，raise_application_error可以防止和停止删除。考虑以下示例：

SQL> desc emp
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER(4)                              
ENAME    VARCHAR2(10) Y                         
JOB      VARCHAR2(9)  Y                         
MGR      NUMBER(4)    Y                         
HIREDATE DATE         Y                         
SAL      NUMBER(7,2)  Y                         
COMM     NUMBER(7,2)  Y                         
DEPTNO   NUMBER(2)    Y 

SQL> create or replace trigger trg_del_emp
  2    before delete on emp
  3    for each row
  4  declare
  5  begin
  6    if ( :old.deptno = 10 ) then
  7     raise_application_error(-20222,'Records with Deptno=10 can not be deleted!');
  8    end if;
  9  end;
 10  /

Trigger created

SQL> insert all
  2         into emp values(7782,'CLARK','MANAGER',7839,  date'1981-06-09',2450.00,null,10)
  3         into emp values(7788,'SCOTT','ANALYST',7566,  date'1987-04-19',3000.00,null,20)
  4  select * from dual;

2 rows inserted

SQL> delete emp where empno = 7782;

delete emp where empno = 7782

ORA-20222: Records with Deptno=10 can not be deleted
ORA-06512: at "HR.TRG_DEL_EMP", line 4
ORA-04088: error during execution of trigger 'HR.TRG_DEL_EMP'

SQL> delete emp where empno = 7788;

1 row deleted

SQL> rollback;

Rollback complete

推荐阅读