Oracle 10g触发器学习一

可能不是很多人知道 RAISE_APPLICATION_ERROR 的用途是什么，虽然从字面上已经猜到这个函数是干什么用的。平时用来测试的异常处理
我们都是通过dbms_output.put_line来输出异常信息，但是在实际的应用中，需要把异常信息返回给调用的客户端。
其实 RAISE_APPLICATION_ERROR 是将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或者其他前台开发语言)
RAISE_APPLICATION_ERROR 的声明：

PROCEDURE RAISE_APPLICATION_ERROR( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);

里面的错误代码和内容，都是自定义的。说明是自定义，当然就不是系统中已经命名存在的错误类别，是属于一种自定义事务错误类型，才调用此函数。error_number_in 之容许从 -20000 到 -20999 之间，这样就不会与 ORACLE 的任何错误代码发生冲突。error_msg_in 的长度不能超过 2k，否则截取 2k。


触发器是指存放在数据库中，并被隐藏执行的存储过程。在Oracle8i之前，只允许基于表或视图的DML操作(insert,update,delete)建立触发器，在oracle8i之后，不仅支持DML操作，也允许基于系统事件(启动数据库，关闭数据库，登录)和DDL操作建立触发器。
一、触发器简介
   触发器是指隐含执行的存储过程，它可以使用PL/SQL，java和C进行开发，当发生特定事件(例如：修改表、建立对象、登录数据库)时，Oracle会自动执行触发器的相应代码。
触发器由触发事件、触发条件和触发操作三部分组成。
1、触发事件
触发事件是指触发器被触发的SQL、数据库事件和用户事件，在oracle8i之前，触发事件只能是DML操作，在oracle8i之后，不仅支持DML事件，而且还增加了其他事件，具体事件如下：

启动和关闭例程
Oracle错误信息
用户登陆和关闭会话
特定表和视图的DML操作
DDL语句

2、触发条件(可选)
触发条件是指使用when子句指定一个boolean表达式，当表达式返回true时，则执行触发器相应代码，如果表达式返回false或unknown，则不会执行触发器相应代码。
3、触发操作
触发操作是指包含SQL语句和其他执行代码的PL/SQL块，不仅可以使用PL/SQL开发，也可以使用java或c语言开发，当触发条件为true时，会自动执行触发操作的相应代码。但是在编写触发器执行代码时，需要注意一下限制：

触发代码大小不能超过32k，如果确实需要使用大量代码建立触发器，应该首先建立存储过程，然后在触发器中使用call语句调用存储过程。
触发器代码只能包括insert、update和delete语句，而不能包括DDL语句(create、drop、alert)和事务控制语句(commit,rollback和savepoint)。

二、建立DML触发器
    当建立DML触发器时，需要指定触发时机(before和after)、触发事件(insert、select、update、delete)、表名、触发类型、触发条件以及触发操作。
1、触发时机
  触发时机是指触发器的触发时间，当指定before关键字时，表示在执行DML操作之前触发触发器；当指定after关键字时，表示在执行DML操作之后触发触发器。
2、触发事件
触发条件是指被引起触发器执行的DML语句，即insert、update、delete操作。即可以使用单个触发事件，也可以组合多个触发事件。
3、表名
因为DML触发器是针对特定表执行的，所以必须指定DML操作所对应的表名。
4、触发类型
触发类型用于指定当触发事件发生后，需要执行几次触发操作，如果指定语句触发类型(默认)，则只会执行一次触发器代码，如果指定行触发器，则会在每个被作用行上执行一次触发器。
5、触发条件
触发条件用于指定执行触发器代码的条件，只有当条件为true时，才会执行触发器代码，当编写DML触发器时，只能在行触发器上执行触发条件。
6、触发操作
触发操作用于指定触发器执行的代码，如果使用PL/SQL存储过程、java存储过程或外部存储过程，则在触发操作中使用call语句调用相应过程。如果使用PL/SQL匿名块编写触发操作，则按照以下格式编写。
[declare]
  定义变量、常量等。
begin
  编写SQL语句或PL/SQL块。
exception
  编写例外处理语句。
end;
7、DML触发器触发顺序
(1)、DML触发器在单行数据上执行顺序
    对于单行数据而言，无论是语句触发器、还是行触发器，都执行一次，并且执行顺序是
    before语句触发器、before行触发器、DML操作，after行触发器、after语句触发器。
(2)、DML触发器在多行数据上执行顺序
   对于多行数据而言，语句触发器只执行一次，而行触发器则在每个作用行上都执行一次。
三、语句触发器
语句触发器是当执行DML语句时被隐含执行的触发器。注意语句触发器时，不能记录列数据的变化。建立语句触发器的语法如下：

Oracle代码

create or replace trigger trigger_name    
   timing event1[or event2 or event3]   
 on table_name   
pl/sql block；

 

如上所示：trigger_name用于指定触发器名称，timing用于指定触发时机(before和after)，event1用于指定指定触发事件(insert、update、delete)，table_name用于指定DML操作对应的表名。
1、建立before语句触发器
   为了禁止工作人员在休息日改变雇员信息，可以建立before语句触发器，以实现数据的安全保护，示例如下：

Oracle代码

create or replace trigger trigger_before   
before insert or update or delete on cip_tmps   
begin   
     if to_char(sysdate,'DY','nls_date_language=AMERICAN')   
      IN ('STA','SUN','THU','WED') then   
      raise_application_error(-20001,'不能在周末修改数据');   
      end if;   
end; 

  2、使用条件谓词
当在触发器中同时包含多个触发时间(insert、update、delete),为了在触发器代码中区分具体的触发事件、可以使用以下三个条件谓词：

INSERTING：当触发条件是insert操作时，该条件谓词返回值为true，否则返回值为false。
UPDATING：当触发条件是update操作时，该条件谓词返回值为true，否则返回值为false。
DELETING：当触发条件是delete操作时，该条件谓词返回值为true，否则返回值为false。

下面示例说明在触发器中使用这三个条件谓词的方法，示例如下：

Oracle代码

create or replace trigger forbidden_update_comtype
before insert or update or delete on communitytype
begin
case
when inserting then
raise_application_error(-20091,'不能执行增操作');
when deleting then
raise_application_error(-20092,'不能执行删操作');
when updating then
raise_application_error(-20093,'不能执行改操作');
end case;
end;

update communitytype com
set com.english_name = 'test'
where com.community_type_id = 'ebook' 

3、建立after语句触发器
在往cip_temp表中插入数据时，同时往cip_temps表中插入数据,必备条件是，cip_temp表中必须有一个字段标示是最后插入的那条记录。

Oracle代码

create or replace trigger trigger_after   
after insert on cip_temp   
begin   
insert into cip_temps (select * from(select * from cip_temp order by id) where rownum=1);   
end;  

四、建立行触发器
行触发器是指执行DML操作时，每作用一行就触发一次触发器。建立行触发器语法如下：

Oracle代码

create or replace trigger trigger_name    
   timing event1[or event2 or event3]   
 on table_name   
[REFERENCING NEW AS NEW | OLD AS OLD]   
for each row   
pl/sql block；

 

如上所示：trigger_name用于指定触发器名称，timing用于指定触发时机(before和after)，event1用于指定指定触发事件(insert、update、delete)，table_name用于指定DML操作对应的表名。REFERENCING 子句用于指定引用新、旧、数据方式，默认情况下使用old修饰符引用旧数据，使用new修饰符是引用新数据。for each row表示建立行触发器。
1、建立before行触发器
确保员工工资不能涨价，示例如下：

Oracle代码

create or replace trigger trigger_before_row   
before update of english_name on communitytype   
for each row   
begin   
  if(:new.english_name =:old.english_name) then   
  raise_application_error(-20001,'资源库名称没有变化');   
  end if;   
end;

update communitytype com
set com.english_name = com.english_name
where com.community_type_id = 'ebook'

 

2、建立after行触发器
做到更新、删除、添加一个表中的数据，则另一个表也相应的更新、删除、添加。

Oracle代码

create or replace trigger trigger_after_row   
after insert or update or delete on cip_test   
for each row   
declare   
v_update int;   
v_delete int;   
begin   
case    
  when inserting then   
    insert into cip_temps values(:new.name,:new.age,:new.address,:new.id);   
  when updating then   
      select count(1) into v_update from cip_temps where id=:old.id;   
      if(v_update=0) then   
      insert into cip_temps values(:new.name,:new.age,:new.address,:new.id);   
      else   
            update cip_temps set name=:new.name,age=:new.age,address=:new.address    
            where id=:old.id;   
      end if;   
  when deleting then   
    select count(*) into v_delete from cip_temps where id=:old.id;   
    if(v_delete<>0)then   
     delete from cip_temps where id=:old.id;   
    end if;   
   end case;      
end;

 

3、限制行触发器
当使用行触发器时，默认情况下会作用在每行执行一次触发器，为了在特定条件下执行行触发器代码，就需要使用when子句对触发条件加以限制。
示例如下：

Oracle代码

create or replace trigger trigger_after_row_when   
after  update or delete on cip_test   
for each row   
when (old.name='aa8')   
declare   
v_update int;   
begin   
case    
  when updating then   
      select count(*) into v_update from cip_temps where id=:old.id;   
    if(v_update=0) then   
     insert into cip_temps values(:new.name,:new.age,:new.address,:new.id);   
    else   
update cip_temps set name=:new.name,age=:new.age,address=:new.address where id=:old.id;   
    end if;   
   end case;      
end; 

4、DML触发器使用注意事项
当编写DML触发器时，触发器代码不能从触发器所对应的表中读取数据。
5、实现参照完整性
参照完整性是指两个表具有主从关系(主外健关系)，当删除主表数据时，必须确保相应的从表的数据也被删除，可以在定义外键约束时指定on delete cascade。
五、行触发器和语句触发器区别
1、行触发器有 for each row子句。语句触发器没有for each row 子句。
2、行触发器，可以有 when 作为触发限制，可以使用new/old。语句触发器不能有when 作为触发限制。
3、行触发器：对应DML语句所影响到的表中的每一行，触发器都要执行一遍。
4、语句触发：对应DML语句所影响到的表中的所有行，触发器只执行一遍。
对表进行行级触发的,则该表暂时不能操作(即该表已经成为变异表)表 级触发器与此不同