ORACLE 变异表解决方法

陈晓龙

对表进行DELETE，UPDATE，INSERT操作时，所操作的表就变成了变异表，对表的行级（FOR EACH ROW）触发器中不能对该表进行DML操作。这时可以使用两个方法解决。

[@more@]

1、采用自主事物pragma autonomous_transaction解决。

CREATE OR REPLACE TRIGGER tr_name
BEFORE insert ON tab_name
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
------------------------
COMMIT;
END;

2、采用两个触发器（一个表行级触发器FOR EACH ROW，一个表级触发器）和一个包来解决。

CREATE OR REPLACE PACKAGE adata AS
TYPE t_aid IS TABLE OF a.aid%TYPE INDEX BY BINARY_INTEGER;
TYPE t_ano IS TABLE OF a.ano%TYPE INDEX BY BINARY_INTEGER;
v_aid t_aid;
v_ano t_ano;
v_numEntries BINARY_INTEGER := 0;
END adata;

---------------------------------------
CREATE OR REPLACE TRIGGER tr_a
BEFORE INSERT OR UPDATE OF aid ON a
FOR EACH ROW
BEGIN
adata.v_numEntries := adata.v_numEntries + 1;
adata.v_aid(adata.v_numEntries) := :new.aid;
adata.v_ano(adata.v_numEntries) := :new.ano;
END tr_a;

----------------------------------------

CREATE OR REPLACE TRIGGER tr_b
after INSERT OR UPDATE OF aid ON a
BEGIN
v_Max CONSTANT NUMBER := 5;
v_Cur NUMBER;
v_aid a.aID%TYPE;
v_ano a.ano%TYPE;
BEGIN
FOR v_loopindex IN 1 .. adata.v_numEntries loop
v_aid := adata.v_aid(v_loopindex);
v_ano := adata.v_ano(v_loopindex);
SELECT count(*) INTO v_cur FROM a WHERE aid = v_aid;
IF v_cur > v_maxs THEN
RAISE_APPLICATION_ERROR(-20000,
'Too many students for major ' || v_aid||
'because of student ' || v_ano);
END IF;
END LOOP;
END tr_b;

陈晓龙

万望 发表于 2016-7-24 23:52
楼主是越来越牛了

万望

楼主是越来越牛了

万望