Oracle触发器详细介绍

Oracle触发器详细介绍一


  触发器 
　　是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。 
　　功能： 
　　1、 允许/限制对表的修改 
　　2、 自动生成派生列，比如自增字段 
　　3、 强制数据一致性 
　　4、 提供审计和日志记录 
　　5、 防止无效的事务处理 
　　6、 启用复杂的业务逻辑 
　开始 
　　create trigger biufer_employees_department_id 
　　	before insert or update 
　　	of department_id 
　　    on employees 
　　    referencing old as old_value 
　　	new as new_value 
　　       for each row 
　　       when (new_value.department_id<>80 ) 
　　begin 
　　       :new_value.commission_pct :=0; 
　　end; 
　　/ 
　触发器的组成部分： 
　　1、 触发器名称 
　　2、 触发语句 
　　3、 触发器限制 
    4、 触发操作 

　1、 触发器名称 
　　create trigger biufer_employees_department_id 
　　命名习惯： 
　　biufer（before insert update for each row） 
　　employees 表名 
　　department_id 列名 
　2、 触发语句 
　　比如： 
　　表或视图上的DML语句 
　　DDL语句 
　　数据库关闭或启动,startup shutdown 等等 
　　before insert or update 
　　       			of department_id 
　　              on employees 
　　       referencing old as old_value 
　　                       new as new_value 
　　       for each row 
　　说明： 
　 　1、 无论是否规定了department_id ，对employees表进行insert的时候 
　 　2、 对employees表的department_id列进行update的时候 
　3、 触发器限制 
　　when (new_value.department_id<>80 ) 
　　限制不是必须的。此例表示如果列department_id不等于80的时候，触发器就会执行。 
　　其中的new_value是代表更新之后的值。 
　4、 触发操作 
　　是触发器的主体 
　　begin 
　　       :new_value.commission_pct :=0; 
　　end; 
　　主体很简单，就是将更新后的commission_pct列置为0 
　　触发： 
　　insert into employees(employee_id, 
　　last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct ) 
　　values( 12345,’Chen’,’Donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25); 
　　select commission_pct from employees where employee_id=12345; 
　　触发器不会通知用户，便改变了用户的输入值。 
　触发器类型： 
　　1、 语句触发器 
　　2、 行触发器 
　　3、 INSTEAD OF 触发器 
　　4、 系统条件触发器 
　　5、 用户事件触发器 

注释： 
  before和after：指在事件发生之前或之后激活触发器。 
  instead of：如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。 
  insert、delete和update：指定构成触发器事件的数据操纵类型，update还可以制定列的列表。 
  referencing：指定新行（即将更新）和旧行（更新前）的其他名称，默认为new和old。 
  table_or_view_name：指要创建触发器的表或视图的名称。 
  for each row：指定是否对受影响的每行都执行触发器，即行级触发器，如果不使用此子句，则为语句级触发器。 
  when：限制执行触发器的条件，该条件可以包括新旧数据值得检查。 
  declare---end：是一个标准的PL/SQL块。


Oracle触发器详细介绍二--语句触发器


 语句触发器 
　　是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。 
　　例子： 
　　需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。 
　　Create table foo(a number); 
　　Create trigger biud_foo 
　　       Before insert or update or delete 
　　       On foo 
　　Begin 
　　       If user not in (‘DONNY’) then 
　　       Raise_application_error(-20001, ‘You don’t have access to modify this table.’); 
　　       End if; 
　　End; 
　　/ 
　　即使SYS，SYSTEM用户也不能修改foo表 
　　[试验] 
　　对修改表的时间、人物进行日志记录。 
　　1、 建立试验表 
　　create table employees_copy as select *from hr.employees 
　　2、 建立日志表 
　　	create table employees_log(who varchar2(30), when date); 
　　3、 在employees_copy表上建立语句触发器，在触发器中填充employees_log 表。 
　　Create or replace trigger biud_employee_copy 
　　              Before insert or update or delete 
　　                     On employees_copy 
　　       Begin 
　　              Insert into employees_log(Who,when) Values( user, sysdate); 
　　       End; 
　　       / 
　　4、 测试 
　　update employees_copy set salary= salary*1.1; 
　　 
　　select *from employess_log; 
　　 
　　5、 确定是哪个语句起作用？ 
　　	即是INSERT/UPDATE/DELETE中的哪一个触发了触发器？ 
　　	可以在触发器中使用INSERTING / UPDATING / DELETING 条件谓词，作判断： 
　　begin 
　　        if inserting then 
　　               ----- 
　　        elsif updating then 
　　               ----- 
　　        elsif deleting then 
　　               ------ 
　　        end if; 
　　end;   
　　if updating(‘COL1’) or updating(‘COL2’) then 
　　        ------ 
　　end if; 
　　[试验] 
　　1、 修改日志表 
　　alter table employees_log  add (action varchar2(20)); 
　　2、 修改触发器，以便记录语句类型。 
　　Create or replace trigger biud_employee_copy 
　　              Before insert or update or delete 
　　              On employees_copy 
　　       Declare 
　　              L_action employees_log.action%type; 
　　       Begin 
　　        if inserting then 
　　               l_action:=’Insert’; 
　　        elsif updating then 
　　               l_action:=’Update’; 
　　        elsif deleting then 
　　               l_action:=’Delete’; 
　　        else 
　　               raise_application_error(-20001,’You should never ever get this error.’); 
　　 
　　        Insert into employees_log( Who,action,when) Values( user, l_action,sysdate); 
　　       End; 
　　       / 
　　3、 测试 
　　insert into employees_copy( employee_id, last_name, email, hire_date, job_id) 
　　       values(12345,’Chen’,’Donny@hotmail’,sysdate,12); 
　　 
　　select *from employees_log 

总结：语句级触发器.(语句级触发器对每个DML语句执行一次）是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。 

 实例: 
  create or replace trigger tri_test 
  	after insert or update or delete on test 
 	 begin 
    if updating then 
      	dbms_output.put_line('修改'); 
    elsif deleting then 
     	 dbms_output.put_line('删除'); 
    elsif inserting then 
     	 dbms_output.put_line('插入'); 
    end if; 
  end;


Oracle触发器详细介绍三--行级触发器


 行级触发器 
  本章介绍行级触发器机制。大部分例子以INSERT出发器给出，行级触发器可从insert update delete语句触发。 
  1、介绍 
   触发器是存储在数据库已编译的存储过程，使用的语言是PL/SQL，用编写存储过程一样的方式编写和编译触发器。下面在SQL*PLUS会话中创建和示例一个简单的Insert行级触发器。这个触发器调用DBMS_OUTPUT在每插入一行数据时打印 “executing temp_air” 
SQL> set feedback off 
SQL> CREATE TABLE temp (N NUMBER); 
SQL> CREATE OR REPLACE TRIGGER temp_air 
  2  AFTER INSERT ON TEMP 
  3  FOR EACH ROW 
  4  BEGIN 
  5  dbms_output.put_line('executing temp_air'); 
  6  END; 
  7   / 
  8   SQL> INSERT INTO temp VALUES (1);     -- insert 1 row 
  executing temp_air 
  SQL> INSERT INTO temp SELECT * FROM temp; -- insert 1 row 
  executing temp_air 
  SQL> INSERT INTO temp SELECT * FROM temp; -- inserts 2 rows 
  executing temp_air 
  executing temp_air 
  SQL> 
  	尽管第三个Insert语句是一条SQL语句，但插入TEMP表中两条记录。许多insert语句插入一条记录，但可以用一条语句插入许多行。 
2、行级触发器语法： 
   CREATE OR REPLACE TRIGGER trigger_name 
   		AFTER|BEFORE INSERT|UPDATE|DELETE ON table_name 
   		FOR EACH ROW 
   		[WHEN (Boolean expression)] 
   		DECLARE 
       	Local declarations 
   BEGIN 
     Trigger Body written PL/SQL 
   END; 
  •Trigger_name 
      用触发器名来确定表名和触发器类型。PL/SQL运行时错误将产生一个PL/SQL错误信息，涉及触发器名和行数。下面Oracle错误显示了在students表上的AFTER-INSERT行触发器的第5行有一个被0除错误。 
        ORA-01476: divisor is equal to zero 
        ORA-06512: at "SCOTT.STUDENTS_AIR", line 5 
        ORA-04088: error during execution of trigger 
    'SCOTT.STUDENTS_AIR' 
      行记数从关键字DECLARE行开始，如果没有DECLARE部分，BEGIN语句是第一行。触发器名称存储在USER_TRIGGERS表的TRIGGER_NAME。触发器名一般由表名、触发器类型、触发事件，语法如下： 
      trigger_name = table_name_[A|B] [I|U|D] [R|S] 

      trigger_name 最长30个字符，所以有时不得不使用表名缩写。常表名一般要有一个规则的缩写。这样可以减少故障分析处理时间。 
      [A|B]        表示是AFTER 或 BEFORE 触发器类型 
      [I|U|D]      表示触发事件，可能是 insert ,update 或者delete 
      [R|S]        表示行级(row)或语句级(statement)触发器类型。 
    •BEFORE|AFTER insert on table_name 
      这条语句告诉Oracle什么时候执行触发器.它可能在ORACLE 完整性约束检查前或后执行，可以指定一个Before或after触发器在多语句操作类型上触发，如： 
  BEFORE INSERT OR UPDATE on table_name 
  BEFORE INSERT OR UPDATE OR DELETE on table_name 
  AFTER INSERT OR DELETE on table_name 
      DBMS_STANDARD 包提供了四个boolean函数来区分SQL语句类型。 
        PACKAGE DBMS_STANDARD IS 
      FUNCTION inserting RETURN BOOLEAN; 
      FUNCTION updating RETURN BOOLEAN; 
      FUNCTION updating (colnam VARCHAR2) RETURN BOOLEAN; 
      FUNCTION deleting RETURN BOOLEAN; 
      etc, 
  END DBMS_STANDARD; 
   在触发器中可以直接使用函数名称，不需要指定包名： 
   CREATE OR REPLACE TRIGGER temp_aiur 
  AFTER INSERT OR UPDATE ON TEMP 
  FOR EACH ROW 
  BEGIN 
      CASE 
      WHEN inserting THEN 
          dbms_output.put_line 
              ('executing temp_aiur - insert'); 
 	     WHEN updating THEN 
  	        dbms_output.put_line 
 	             ('executing temp_aiur - update'); 
 	     END CASE; 
  END; 
      对于Update行级触发器，可以指定被更新的列作为触发器触发条件。 
     CREATE OR REPLACE TRIGGER temp_aur 
   AFTER INSERT OR UPDATE OF M, P ON TEMP 
   FOR EACH ROW 
   BEGIN 
       dbms_output.put_line 
          ('after insert or update of m, p'); 
   END; 
    •WHEN(BOOLEAN EXPRESSION) 
        这是个可选语句，用来过滤触发触发器的条件。 

        CREATE OR REPLACE TRIGGER temp_air 
   AFTER INSERT ON TEMP 
   FOR EACH ROW 
 			 WHEN (NEW.N = 0) 
  		 BEGIN 
   		    dbms_output.put_line('executing temp_air'); 
 		 END; 
  上例中表示AFTER INSERT行触发器触发的条件是：N字段的值等于0. 
  NEW.COLUMN_NAME : INSERT或UPDATE触发器中WHEN语句中引用字段的语法。 
  OLD.COLUMN_NAME : 用于UPDATE或DELETE行级触发器中WHEN语句中。在INSERT语句中为Null。


Oracle触发器详细介绍四--INSTEAD OF触发器


   在简单视图上往往可以执行INSERT、UPDATE和DELETE操作，但是在复杂视图上执行INSERT、UPDATE和DELETE操作是有限的。如果视图子查询包含有集合操作符、分组函数、DISTINCT关键字或者连接查询，那么将禁止在该视图上执行DML操作。为了在这些复杂视图上执行操作，需要建立INSTEAD-OF触发器。

INSTEAD-OF触发器具有以下限制： 
INSTEAD OF触发器只适用于视图。 
INSTEAD OF触发器不能指定BEFORE和AFTER选项。 
不能在具有WITH CHECK OPTION选项的视图上建立INSTEAD OF触发器。 
INSTEAD OF触发器必须包含有FOR EACH ROW选项。 

 复杂视图DEPT_EMP用于显示部门号、部门名、雇员号以及雇员名，并且在该复杂视图上不能执行任何DML操作。为了在该视图上执行DML操作，必须建立INSTEAD OF触发器。下面以完成该认务为例，说明建立INSTEAD OF触发器的方法。在建立INSTEAD OF触发器之前，首先建立视图DEPT_ENP。 

  create or replace view dept_emp as 
  select a.deptno,a.dname,b.empno,b.ename from department a,employee b 
  where a.deptno=b.deptno; 
create or replace trigger tr_instead_of_dept_emp 
   instead of insert on dept_emp for each row 
   declare 
     v_temp int; 
   begin 
     select count(*) into v_temp from department where deptno=:new.deptno; 
     if v_temp=0 then 
       insert into department(deptno,dname) values(:new.deptno,:new.dname); 
     end if; 
     select count(*) into v_temp from employee where empno=:new.empno; 
     if v_temp=0 then 
       insert into employee(empno,ename,deptno) values(:new.emptno,:new.deptno); 
     end if; 
   end; 
   /


Oracle触发器详细介绍五--系统事件触发器


     oracle的系统事件触发器：系统事件触发器是指基于oracle系统事件（如logon和startup）所建立的触发器。通过这种触发器可以跟踪系统或数据库的变化。 

  create table jax_event_table(eventname varchar2(30),time date); 

 create trigger  tr_startup 
  after  startup  on database 
  begin 
 	 insertinto jax_event_table values(ora_sysevent,sysdate); 
  end; 

  create trigger  tr_shutdown 
 	before shutdown on database 
  begin 
   insert into  jax_event_table values(ora_sysevent,sysdate); 
  end; 

      在建立如上所示的两个触发器后，使用shutdown和startup关闭开启数据库会往表jax_event_table中记录一条记录，但 shutdown abort则不会触发该触发器，而startup nomount后使用alter database将数据库更改为mount或者open都只会触发一次。 

1          SHUTDOWN     2008-3-20 14:29:47 
2          STARTUP         2008-3-20 14:42:52 
3          SHUTDOWN     2008-3-20 14:43:06 
4          STARTUP         2008-3-20 14:45:34 

  登录和退出触发器用来记载登录用户名称、时间和ip地址 

  Create table  jax_log_table(   username varchar2(20), log_time date, onoff varchar(6), address  varchar2(30)); 

  create  trigger  tr_logon 
  after  logon  ondatabase 
  	begin 
  insert into jax_log_table values(ora_login_user,sysdate,'logon',ora_client_ip_address); 
 	 end; 

  create  trigger  tr_logoff 
  	before  logoff  ondatabase 
  	begin 
   	 insert into jax_log_table values(ora_login_user,sysdate,'logoff',ora_client_ip_address); 
    end; 

  select * from jax_log_table; 

1          SYS       2008-3-20 14:55:17       logon     
2          SYSMAN           2008-3-20 14:55:21       logon     
3          SYS       2008-3-20 14:55:45       logon    127.0.0.1 
4          SYS       2008-3-20 14:56:07       logoff   
5          SYSMAN           2008-3-20 14:56:26       logon     
6          SYSMAN           2008-3-20 14:56:27       logoff   
7          ZHANGLEI       2008-3-20 14:56:35       logon    127.0.0.1 
8          ZHANGLEI       2008-3-20 14:57:01       logoff   
9          SYS       2008-3-20 14:57:12       logon    127.0.0.1 
10         SYSMAN           2008-3-20 14:57:31       logon     
11         SYSMAN           2008-3-20 14:57:32       logoff   


   DDL触发器记录系统所发生的DDL事件（create，alter，drop等） 

create  table  jax_event_ddl_table(event varchar2(20), username varchar2(10),owner varchar2(10), objname varchar2(20), objtype varchar2(10),timedate); 

create  trigger  tr_ddl 
after  ddl  on  database 
begin 
 	insert into jax_event_ddl_table values(ora_sysevent,ora_login_user, 
 		ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate) 
end; 

1          CREATE           SYS       SYS       T2        TABLE 2008-3-20 15:05:41 
2          CREATE           SYS       SYS       VIEW_T2          VIEW    2008-3-20 15:06:53 


由上面的描述看到，在编写系统事件触发器时，应用开发人员经常需要使用事件属性函数。常用的事件属性函数如下： 

Ora_client_ip_address 返回客户端的ip地址 
Ora_database_name 返回当前数据库名 
Ora_des_encrypted_password 返回des加密后的用户口令 
Ora_dict_obj_name 返回ddl操作所对应的数据库对象名 
Ora_dict_obj_name_list(name_list out ora_name_list_t) 返回在事件中被修改的对象名列表 
Ora_dict_obj_owner 返回ddl操作所对应的对象的所有者名 
Ora_dict_obj_owner_list(owner_list out ora_name_list_t) 返回在事件中被修改的对象的所有者列表 
Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型 
Ora_grantee(user_list out ora_name_list_t) 返回授权事件的授权者 
Ora_instance_num 返回例程号 
Ora_is_alter_column(column_name in varchar2) 检测特定列是否被修改 
Ora_is_creating_nested_table 检测是否正在建立嵌套表 
Ora_is_drop_column(column_name in varchar2) 检测特定列是否被删除 
Ora_is_servererror(error_number) 检测是否返回了特定oracle错误 
Ora_login_user 返回登录用户名 
Ora_sysevent 返回触发器的系统事件名。