如何在触发器之后调用使用相同表的过程 [英] How to call a Procedure which uses the same table in after trigger

问题描述

在以下方法中删除失败的数据后，我想使用相同的表数据.

I want to use the same table data after deleting the data which fails in following method.

我遇到的问题是，最新的更改在后触发器完成之前尚未落实.

The issue I faced is the latest change is not getting committed before the after trigger is completed.

create table test_tbl(id_ number, type_ varchar2(100) , count_ number);

---

create table test_count_tbl(type varchar2(100), count_ number) ; 

---

 begin 
    insert into test_tbl(id_ , type_  , count_ ) values (1,'type1', 10 );
    insert into test_tbl(id_ , type_  , count_ )  values (2,'type1', 20 );
    insert into test_tbl(id_ , type_  , count_ )  values (3,'type2', 10 );
    insert into test_tbl(id_ , type_  , count_ )  values (4,'type2', 40 );
    insert into test_tbl(id_ , type_  , count_ )  values (5,'type3', 10 );
    insert into test_tbl(id_ , type_  , count_ )  values (6,'type3', 60 );
    commit;
    end;

---

create or replace procedure test_count_update_p( p_type_ in varchar2) 
is 
begin 

  MERGE INTO test_count_tbl D
   USING (select type_, sum(count_) count_sum_
          from test_tbl
          where type_ = p_type_
          group by type_ ) S   ON (D.type = S.count_sum_)
   WHEN MATCHED THEN UPDATE SET D.count_ = S.count_sum_ 
   --  DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.type, D.count_)
     VALUES (S.type_, S.count_sum_);
  commit;
end ;

---

 CREATE OR REPLACE TRIGGER test_tbl_trigger
    AFTER INSERT  OR DELETE OR UPDATE ON test_tbl 
    FOR EACH ROW

    DECLARE    
       PRAGMA AUTONOMOUS_TRANSACTION;    
       L_TYPE VARCHAR2(100);
    BEGIN    
        if DELETING THEN 
           L_TYPE  := :OLD.TYPE_;
           end if;

        IF UPDATING OR INSERTING THEN 
          L_TYPE  := :NEW.TYPE_;
        end if; 

       test_count_update_p(L_TYPE);    
       COMMIT;    
    END;

---

执行以下操作以查看确切的问题.

---

Do the following to see the exact issue..

begin 
insert into test_tbl(id_ , type_  , count_ )  values (7,'type4', 60 );
commit;
end;

select * from test_tbl ;

记录插入到表中.

select * from test_count_tbl ; 

记录不在此表中.

begin 
delete test_tbl where id_ = 7;
commit ;
end;

select * from test_tbl ;

删除记录.

select * from test_count_tbl ; 

计算了表test_tbl中不可用的记录；

Counted the record which is not available in the table test_tbl;

推荐答案

您不能.

普通的行级触发器无法查询定义触发器的表，因为这会引发变异表异常.我假设这就是为什么您声明要使用自主事务的触发器的原因(除了持久日志记录之外的其他任何事情，自主事务几乎肯定是一个错误).但是，如果执行此操作，则触发器将看不到触发事务所做的未提交的更改.那就是您现在遇到的问题.

A normal row-level trigger cannot query the table the trigger is defined on because that would raise a mutating table exception. I'm assuming that's why you have declared your trigger to use an autonomous transaction (an autonomous transaction for anything other than persistent logging is almost certainly an error). If you do that, however, your trigger cannot see the uncommitted changes made by the triggering transaction. That's the problem you're encountering now.

一种替代方法是使用复合触发器.您将声明一个test_table.type_%type的集合，您将在触发器的行级部分中将要更改的值添加到该集合中，然后在该元素的后声明部分中迭代该集合中的元素.您的触发器.允许使用语句级触发器查询定义触发器的表，以便您可以从复合触发器的后声明部分调用过程.

An alternative would be to use a compound trigger. You'd declare a collection of test_table.type_%type, you would add the values that are changing to this collection in the row-level portion of your trigger, and then you would iterate over the elements in the collection in the after-statement portion of your trigger. A statement-level trigger is allowed to query the table on which the trigger is defined so you can call your procedure from the after-statement portion of your compound trigger.

这篇关于如何在触发器之后调用使用相同表的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！