触发器详细介绍

触发器详细介绍一
      触发器
      　　是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。
      　　功能：
      　　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 -用:old_value.列名代替:old.列名
      　　                       new as new_value -用:new_value.列名代替:new.列名
      　　       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触发器详细介绍二--语句触发器
      1、 语句触发器
      　　是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与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_r_r_r_r)]
      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_r_r_r_r)
              这是个可选语句，用来过滤触发触发器的条件。
              
              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);
      createtrigger tr_startup
      after startup ondatabase
      begin
      insertinto jax_event_table values(ora_sysevent,sysdate);
      end;
      createtrigger tr_shutdown
      beforeshutdownondatabase
      begin
      insertinto 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地址
      createtable jax_log_table(
      username varchar2(20), log_time date, onoff varchar(6),address
      varchar2(30));
      createtrigger tr_logon
      after logon ondatabase
      begin
      insertinto jax_log_table
      values(ora_login_user,sysdate,'logon',ora_client_ip_address);
      end;
      createtrigger tr_logoff
      before logoff ondatabase
      begin
      insertinto 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等）
      createtable jax_event_ddl_table(event varchar2(20),
      username varchar2(10),owner varchar2(10),objname varchar2(20),
      objtype varchar2(10),timedate);
      createtrigger tr_ddl
      afterddlondatabase
      begin
      insertinto 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 返回触发器的系统事件名。

 

********************************

触发器的事务

我在一个表A上定义了一个after
update,insert的触发器，根据新插入或修改的数据重新计算该记录的销售额（其中销售额也是这条记录的一个字段）。
问题：after触发器发生在update,insert语句之后，如果update,insert语句已经执行了，但触发器的内容还没执行完就停电了，这时新插入或修改的数据会被保存到表里吗？

问题2：如果触发器的事务执行了rollback操作，表A的数据会被修改吗？

这个问题第1个回答：
1.触发器中断电,表A的数据将维持修改之前的数据;
2.如果触发器的事务执行了rollback操作，表A的数据[不]会被修改!

这个问题第2个回答：
引用
问题：after触发器发生在update,insert语句之后，如果update,insert语句已经执行了，但触发器的内容还没执行完就停电了，这时新插入或修改的数据会被保存到表里吗？

 

不会保存.数据保持在update,insert语句执行前的状态

引用
问题2：如果触发器的事务执行了rollback操作，表A的数据会被修改吗？
 

不会被修改。

这个问题第3个回答：
引用 2 楼 Garnett_KG 的回复:
引用
问题：after触发器发生在update,insert语句之后，如果update,insert语句已经执行了，但触发器的内容还没执行完就停电了，这时新插入或修改的数据会被保存到表里吗？

 

不会保存.数据保持在update,insert语句执行前的状态

引用
问题2：如果触发器的事务执行了rollback操作，表A的数据会被修改吗？

不会被修改。
 
只有引用的内容不允许回复！！

这个问题第4个回答：
这样理解：
  触发器和引起触发器触发的语句位于同一个事务中，所以不管发生什么情况，触发器和引起触发器触发的语句具有原子性

所以
1、停电的情况，下次开机后会执行undo操作，这样都需要回滚
2、触发器中rollback操作，会回滚触发器和外面的语句

 

这个问题第5个回答：
1、不会保存
2、不会被修改