对基表做操作的trigger的应用(autonomous_transaction)

      

今天开发人员要写一个trigger，其中需要select触发trigger的表中的数据，成功创建trigger后，去update原来的表，系统提示出错，错误如下：

ORA-04091: table WTADMIN.MOLD_TB is mutating, trigger/function may not see it.

       想起之前看的trigger数据上有讲当编写DML触发器时，触发器代码不能从触发器所对应的基表中读取数据。尽管在建立trigger时不会出现任何错误，但在执行相应的触发操作时会显示错误信息，查询网络及oracle官方文档，找到一个解决方法，就是使用自治事务-----autonomous_transaction。

        autonomous_transaction是指在function,procedure,trigger等subprograms中对事务进行自治管理，当在别的pl/sql block里去调用这些subprograms的时候这些subprograms并不随着你pl/sql block的失败而回滚，而是自己管理自己的commit，这样有可能会造成数据不一致。

Autonomous Versus Nested Transactions
Although an autonomous transaction is started by another transaction, it is not a nested transaction for the following reasons:
  1. It does not share transactional resources (such as locks) with the main transaction.
  2.It does not depend on the main transaction. For example, if the main transaction rolls back, nested transactions roll back, but autonomous transactions do not.  
  3.Its committed changes are visible to other transactions immediately. (A nested transaction's committed changes are not visible to other transactions until the main transaction commits.)
  4.Exceptions raised in an autonomous transaction cause a transaction-level rollback, not a statement-level rollback.

建立的trigger如下：

CREATE OR REPLACE TRIGGER WTADMIN.MOLD_TB_TRG1
BEFORE UPDATE
OF T1_DATE
ON WTADMIN.MOLD_TB
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_mold_version NUMBER;

PRAGMA autonomous_transaction;
BEGIN
IF DBMS_REPUTIL.FROM_REMOTE=FALSE THEN   
 SELECT MAX(TO_NUMBER(mold_version)) INTO v_mold_version FROM wtadmin.MOLD_TB WHERE  mold_no=:OLD.mold_no GROUP BY mold_no;
 IF (:OLD.mold_version = TO_CHAR(v_mold_version)) THEN
 UPDATE mesv3.sch_task_info SET DELIVERY_DATE=:NEW.t1_date WHERE mold_no=:OLD.mold_no;
   COMMIT;
   END IF;
   END IF;

END ;

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9902302/viewspace-665683/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9902302/viewspace-665683/