触发器：更新当前表的时候报错ora-04091

Question

CREATE OR REPLACE TRIGGER Tr_BmsRBill_Upd
   BEFORE INSERT OR UPDATE
   ON BmsRBill
   FOR EACH ROW
DECLARE
   --PRAGMA AUTONOMOUS_TRANSACTION;    
   v_StartDate DATE;
   v_EndDate DATE;
   v_Amount NUMBER(15,2);
   v_RId NUMBER(10,0);
   v_DayNum NUMBER(10,0);
   v_IntrVal NUMBER(15,2);
   v_Rate NUMBER(10,6);
   CURSOR cur_rbill
     IS SELECT :NEW.Rid ,
   :NEW.Amount ,
   :NEW.StartDate ,
   :NEW.EndDate
     FROM DUAL ;BEGIN
   IF NOT ( (:NEW.StartDate IS NOT NULL)
     OR (:NEW.EndDate IS NOT NULL)
     OR (:NEW.Amount IS NOT NULL) ) THEN
      RETURN;
   END IF;
   OPEN cur_rbill;
   FETCH cur_rbill INTO v_RId,v_Amount,v_StartDate,v_EndDate;
   WHILE sqlserver_utilities.fetch_status(cur_rbill%FOUND) <> -1
   LOOP      BEGIN
         v_IntrVal := 0 ;
         v_Rate := 0 ;
         v_DayNum := sqlserver_utilities.datediff('DAY', v_StartDate, v_EndDate) ;
         
         SELECT Rate
           INTO v_Rate
           FROM BmsRate
          WHERE RateType = 0
                  AND v_DayNum > MinDay
                  AND v_DayNum <= MaxDay;
          exception
          when no_data_found then
          v_Rate:=0;
                  
         v_Rate := NVL(v_Rate, 0) ;
 
         IF v_DayNum > 30 THEN
            v_DayNum := v_DayNum - 30 ;
         ELSE
            v_DayNum := 0 ;
         END IF;         v_IntrVal := v_Amount * v_DayNum * v_Rate / 10000 ;
         --insert into BmsRBill_tt values (v_Rate,v_IntrVal,v_RId);
         UPDATE BmsRBill
            SET CashRate = v_Rate,
                CashAmount = v_IntrVal
            WHERE RId = v_RId;
         FETCH cur_rbill INTO v_RId,v_Amount,v_StartDate,v_EndDate;
      END;
   END LOOP;
   CLOSE cur_rbill;
   --commit;
END;
当更新BmsRBill这个表时，报错....网上没找到实际解决方法，加自治事务会报死锁错误....求有效解决方案！！！ 

Answer 1

就是报错：  ora-04091：表BmsRBill发生变化，触发器/函数不能读他！

Answer 2

--错误太多给你改了改，不敢保证语法一定是对的，但是我指出了你的错误的地方
----for each row 触发器，每改变一条数据都会触发一次，触发器运行的时候只有一条数据不需要游标
----同时触发器的作用之一是可以进行数据的修改（从你的触发器看是想进行数据的修改）
------但是不是你想的样子，你只需要将 :new.xxxx 在触发器里按照一定规则改动后，原来的update语句会继续执行--比如：update tab1 set a = 1 where key = 2;
--触发器中只需要 :new.a := 2 就将a的新值改为了 2，然后 update语句继续执行
----update tab1 set a = 2 where key = 2;CREATE OR REPLACE TRIGGER TR_BMSRBILL_UPD
  BEFORE INSERT OR UPDATE ON BMSRBILL
  FOR EACH ROW
DECLARE
  --PRAGMA AUTONOMOUS_TRANSACTION;   
  --V_STARTDATE DATE;
  --V_ENDDATE   DATE;
  --V_AMOUNT    NUMBER(15, 2);
  V_RID       NUMBER(10, 0);
  V_DAYNUM    NUMBER(10, 0);
  V_INTRVAL   NUMBER(15, 2);
  V_RATE      NUMBER(10, 6);
  
  /*CURSOR CUR_RBILL IS  --上面使用的是for each row,这里怎么还能用到游标呢。
    SELECT :NEW.RID, :NEW.AMOUNT, :NEW.STARTDATE, :NEW.ENDDATE FROM DUAL;*/ BEGIN
  /*IF NOT ((:NEW.STARTDATE IS NOT NULL) OR (:NEW.ENDDATE IS NOT NULL) OR
      (:NEW.AMOUNT IS NOT NULL)) THEN*/ --写的太罗嗦
  IF :NEW.STARTDATE IS NULL AND :NEW.ENDDATE IS NULL AND :NEW.AMOUNT IS NULL THEN
    RETURN;                             --这里的return 只是起到返回不执行下面的代码的作用
                                        --不会起到将update 语句回滚的作用
  END IF;
  /*OPEN CUR_RBILL;   --上面使用的是for each row,这里怎么还能用到游标呢。
  FETCH CUR_RBILL
    INTO V_RID, V_AMOUNT, V_STARTDATE, V_ENDDATE;
  WHILE SQLSERVER_UTILITIES.FETCH_STATUS(CUR_RBILL%FOUND) <> -1 LOOP*/
  
    BEGIN
      V_INTRVAL := 0;
      V_RATE    := 0;
      V_DAYNUM  := SQLSERVER_UTILITIES.DATEDIFF('DAY',
                                                --V_STARTDATE,
                                                :NEW.STARTDATE
                                                --V_ENDDATE
                                                :NEW.ENDDATE);
      SELECT RATE
        INTO V_RATE
        FROM BMSRATE
       WHERE RATETYPE = 0
         AND V_DAYNUM > MINDAY
         AND V_DAYNUM <= MAXDAY;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        V_RATE := 0;
      
        V_RATE := NVL(V_RATE, 0);
      
        IF V_DAYNUM > 30 THEN
          V_DAYNUM := V_DAYNUM - 30;
        ELSE
          V_DAYNUM := 0;
        END IF;
      
        --V_INTRVAL := V_AMOUNT * V_DAYNUM * V_RATE / 10000;
        V_INTRVAL := :NEW.AMOUNT * V_DAYNUM * V_RATE / 10000;
        --insert into BmsRBill_tt values (v_Rate,v_IntrVal,v_RId);
        /*
        UPDATE BMSRBILL
           SET CASHRATE = V_RATE, CASHAMOUNT = V_INTRVAL
         WHERE RID = V_RID; 
        --你本身就是update 触发器，在触发器里再update，你想让oracle自己玩死自己么。
        --你只需要改变新的值就可以了 如： :NEW.CASHRATE := V_RATE;
        
        FETCH CUR_RBILL
          INTO V_RID, V_AMOUNT, V_STARTDATE, V_ENDDATE;
        --上面使用的是for each row,这里怎么还能用到游标呢。
        */
        :NEW.CASHRATE := V_RATE;
        :NEW.CASHAMOUNT := V_INTRVAL;
        
    END;
  
  /*END LOOP;
  CLOSE CUR_RBILL;*/
  --commit;
END;

Answer 3

不好意思，我是新手...这段代码是用工具将sqlserver代码转换成oracle的
原sqlserver代码：
CREATE TRIGGER Tr_BmsRBill_Upd ON dbo.BmsRBill 
FOR INSERT,UPDATE
AS
  declare @StartDate datetime,@EndDate datetime,@Amount numeric(15,2),
          @RId int,@DayNum int,@IntrVal numeric(15,2),@Rate numeric(10,6)  if not (UPDATE(StartDate) or UPDATE(EndDate) or UPDATE(Amount))
     return  declare cur_rbill insensitive cursor for
    select Rid,Amount,StartDate,EndDate from inserted  open cur_rbill
  fetch next from cur_rbill into @RId,@Amount,@StartDate,@EndDate
  while @@FETCH_STATUS <> -1
  begin
    select @IntrVal = 0,@Rate = 0
    select @DayNum = datediff(day,@StartDate,@EndDate)
    select @Rate = Rate from BmsRate 
       where RateType = 0 and @DayNum > MinDay and @DayNum <= MaxDay
    select @Rate = isnull(@Rate,0)    if @DayNum > 30
       select @DayNum=@DayNum-30
    else
       select @DayNum=0    select @IntrVal = @Amount*@DayNum*@Rate/10000
    update BmsRBill set CashRate=@Rate,CashAmount=@IntrVal 
       where RId=@RId
    fetch next from cur_rbill into @RId,@Amount,@StartDate,@EndDate
  end
  close cur_rbill
  deallocate cur_rbill
go

Answer 4

原来sqlserver的代码来看的话：
更新一条数据中某3个字段的同时会更新这条数据的其他2个字段

Answer 5

看到一堆堆的代码就脑壳痛。o，mygod。

Answer 6

[TEST@orcl] SQL>set serveroutput on
[TEST@orcl] SQL>CREATE TABLE t1(ID NUMBER NOT NULL PRIMARY KEY,
  2                  NAME VARCHAR2(10),
  3                  SCORE NUMBER(10));表已创建。[TEST@orcl] SQL>
[TEST@orcl] SQL>
[TEST@orcl] SQL>INSERT INTO t1 VALUES(1,'张三',100);已创建 1 行。[TEST@orcl] SQL>INSERT INTO t1 VALUES(2,'李四',50);已创建 1 行。[TEST@orcl] SQL>INSERT INTO t1 VALUES(3,'王五',60);已创建 1 行。[TEST@orcl] SQL>select * from t1;        ID NAME            SCORE
---------- ---------- ----------
         1 张三              100
         2 李四               50
         3 王五               60[TEST@orcl] SQL>CREATE OR REPLACE TRIGGER trigger_t1
  2  BEFORE UPDATE ON t1
  3  FOR EACH ROW
  4  BEGIN
  5
  6    dbms_output.put_line(':new.id='||:new.id);
  7    dbms_output.put_line(':new.NAME='||:new.NAME);
  8    dbms_output.put_line(':new.SCORE='||:new.SCORE);
  9
 10    IF :old.ID = '1' THEN
 11      :new.name := '赵六'; --注意这里。。
 12      :new.score := 30;
 13    END IF;
 14  END;
 15  /触发器已创建[TEST@orcl] SQL>update t1 set t1.name = '李七';  --这里实际上更新三条记录都为“李七”，但是触发器将结果发生了变化
:new.id=1
:new.NAME=李七
:new.SCORE=100
:new.id=2
:new.NAME=李七
:new.SCORE=50
:new.id=3
:new.NAME=李七
:new.SCORE=60已更新3行。[TEST@orcl] SQL>select * from t1;        ID NAME            SCORE
---------- ---------- ----------
         1 赵六               30
         2 李七               50
         3 李七               60[TEST@orcl] SQL>

Answer 7

你举得这个例子也没能反应我的问题，，，我的问题就是说：如果要在一个update触发器里面再update当前表怎么处理！！

Answer 8

你举得这个例子也没能反应我的问题，，，我的问题就是说：如果要在一个update触发器里面再update当前表怎么处理！！===>

没得处理。 update触发器里面再update本表，神仙帮不了。
1、你发出的update语句触发了你写的触发器
2、你写的触发器里面的update语句又触发了你的触发器  ---重复本条一直到报错。我说了，你想实现的逻辑无非就是想，把update的值改变了，我举的例子已经能说明这一点了，仔细看看。

Answer 9

谢谢你...又仔细看了下你的例子..改了改...成功了。
对了 update的时候可以更新本表...只是不能更新同一条数据吧...
你可以看看这个：http://topic.csdn.net/u/20110804/17/e9768529-0a76-4f66-a393-5d4722675615.html

Answer 10

好了....又有收获了...结贴！！！谢谢“BenChiM888”

Answer 11

哈哈，楼主成功了，可以给BenChiM888加分喽。