又是oracle触发器的问题:我要实现一个update触发器,更新当前表的同时,再查询当前表的数据,再根据查询的数据做一些处理,然后更新这条数据的另外一个字段...有好的解决方案没?CREATE OR REPLACE TRIGGER TR_BP_Account_Upd
BEFORE UPDATE
ON BP_Account
FOR EACH ROW
DECLARE
--PRAGMA AUTONOMOUS_TRANSACTION;
v_CurIdx VARCHAR2(32);
v_SuperIdx VARCHAR2(32);
v_Done NUMBER(10,0);
v_Level NUMBER(10,0);
tt NUMBER(10,0);BEGIN
IF NOT UPDATING('SuperIdx') THEN
RETURN;
END IF;
BEGIN
v_Done := 0 ;
v_Level := 1 ;
v_CurIdx := :NEW.AcntIdx ;
v_SuperIdx := :new.SuperIdx;
WHILE v_Done = 0
LOOP
DECLARE
v_temp NUMBER(1, 0) := 0;
BEGIN
SELECT count(*) into tt
FROM BP_Account
WHERE AcntIdx = v_SuperIdx; if tt = 0 then
v_temp := 1;
end if; IF v_temp = 1 THEN
v_Done := 1 ;
ELSE BEGIN
IF v_SuperIdx IS NOT NULL THEN
v_Level := v_Level + 1 ;
v_CurIdx := v_SuperIdx ;
ELSE
v_Done := 1 ;
END IF;
END;
END IF;
begin
SELECT SuperIdx INTO v_SuperIdx
FROM BP_Account
WHERE AcntIdx = v_CurIdx;
exception when no_data_found then
null;
end;
END;
END LOOP;
:new.IdxLevel := v_Level;
END;
--commit;
END;
BEFORE UPDATE
ON BP_Account
FOR EACH ROW
DECLARE
--PRAGMA AUTONOMOUS_TRANSACTION;
v_CurIdx VARCHAR2(32);
v_SuperIdx VARCHAR2(32);
v_Done NUMBER(10,0);
v_Level NUMBER(10,0);
tt NUMBER(10,0);BEGIN
IF NOT UPDATING('SuperIdx') THEN
RETURN;
END IF;
BEGIN
v_Done := 0 ;
v_Level := 1 ;
v_CurIdx := :NEW.AcntIdx ;
v_SuperIdx := :new.SuperIdx;
WHILE v_Done = 0
LOOP
DECLARE
v_temp NUMBER(1, 0) := 0;
BEGIN
SELECT count(*) into tt
FROM BP_Account
WHERE AcntIdx = v_SuperIdx; if tt = 0 then
v_temp := 1;
end if; IF v_temp = 1 THEN
v_Done := 1 ;
ELSE BEGIN
IF v_SuperIdx IS NOT NULL THEN
v_Level := v_Level + 1 ;
v_CurIdx := v_SuperIdx ;
ELSE
v_Done := 1 ;
END IF;
END;
END IF;
begin
SELECT SuperIdx INTO v_SuperIdx
FROM BP_Account
WHERE AcntIdx = v_CurIdx;
exception when no_data_found then
null;
end;
END;
END LOOP;
:new.IdxLevel := v_Level;
END;
--commit;
END;
Cause: A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it. Action: Rewrite the trigger (or function) so it does not read that table
http://blog.csdn.net/zhangmenghao1983/article/details/5182124
我之前试过 不行诶!!