ORA-04091:触发器不能修改触发表的数据+利用DB Trigger 来创建总帐凭证编号

[原创整理,转载注明!!!]

ORA-04091:触发器不能修改触发表的数据:

解决方法

1. 建立一个pl/sql table

2.写一个table level触发,在insert/update/delete 前进行表的index 置0

3.写一个statement level 触发, 记录触发的行rowid 放到step 1里面的pl/sql table

4.写一个table level 触发, 从step1 里面的pl/sql table 来获得rowid ,再用loop 来update

table levle and statement level trigger difference is for each row .如果没有就是statement level trigger

If FOR EACH ROW is specified, then the trigger will activate for each row processed by a statement. If this clause is missing, the default behavior is to fire only once for the statement (a statement-level trigger).

[@more@]

/*********************REFERENCES:

这样的话就不能在触发器中访访问或修改引起触发的该表了.
这种写法的trigger，需要commit，你再试试

触发器不能修改触发表的数据，除非是用自治事务。
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION；

将行级触发器修改为语句级

http://www.chinaunix.net/jh/19/344222.html

*******************/

From Metalink

Problem Description -------------------
Oracle does not allow you to read a mutating table in a row trigger because if you can read it, the information may be incorrect (not read consistent). If you attempt this, the following error is returned:
ORA-04091
Table %s.%s is mutating, trigger/function may not see it
However, you can perform this operation in a statement trigger.
One way to handle this situation is to use a package PL/SQL table to store ROWIDs of updated records in a row trigger, and reprocess the updated
records in a statement trigger.
Below is an example.

Important Note --------------
Note that there are concurrency issues with this if more than one session tries to perform operations simultaneously.
This is not intended as a total solution, but as the framework to help show one option.
Example Workaround ------------------

create or replace package emp_pkg as

type

emp_tab_type is table of rowid index by binary_integer;

emp_tab

 emp_tab_type;

emp_index binary_integer;

 end emp_pkg;

 /

create or replace trigger emp_bef_stm_all

 before insert or update or delete on emp

 begin

/*

Remember to reset the pl/sql table before each statement

*/

emp_pkg.emp_index := 0;

 end;

 /

create or replace trigger emp_aft_row_all

 after insert or update or delete on emp

 for each row

 begin

/*

Store the rowid of updated record into global pl/sql table

*/

emp_pkg.emp_index := emp_pkg.emp_index + 1;

emp_pkg.emp_tab(emp_pkg.emp_index) := :new.rowid;

 end;

 /

我写的:

CREATE OR REPLACE PACKAGE gl_je_headers_num_pkg
AS
TYPE t_je_headers IS TABLE OF ROWID
INDEX BY BINARY_INTEGER;

t_je t_je_headers;
t_je_index BINARY_INTEGER;
END gl_je_headers_num_pkg;

CREATE OR REPLACE TRIGGER bfe_gl_je_headers_num
BEFORE UPDATE
ON gl_je_headers --
BEGIN
/*

Remember to reset the pl/sql table before each statement

*/
gl_je_headers_num_pkg.t_je_index := 0;
END;

CREATE OR REPLACE TRIGGER aft_gl_re_headers_num
AFTER UPDATE OF status
ON gl_je_headers
REFERENCING OLD AS h_old NEW AS h_new
FOR EACH ROW
WHEN (h_new.status = 'P') --- row id to table
BEGIN
/*

Store the rowid of updated record into global pl/sql table

*/
gl_je_headers_num_pkg.t_je_index := emp_pkg.emp_index + 1;
gl_je_headers_num_pkg.t_je (gl_je_headers_num_pkg.t_je_index) := :h_NEW.ROWID;
END;

/* Formatted on 2007/01/18 11:43 (Formatter Plus v4.8.7) */
CREATE OR REPLACE TRIGGER apps.alr_gl_je_headers_num
AFTER UPDATE OF status
ON gl_je_headers
-- REFERENCING OLD AS h_old NEW AS h_new
--FOR EACH ROW
--WHEN (h_new.status = 'P')
DECLARE
v_org_id NUMBER;
v_company VARCHAR2 (10);
v_month VARCHAR2 (10);
v_month_a VARCHAR2 (10);
v_month_b NUMBER;
v_je_header_id NUMBER;
v_year NUMBER;
v_max NUMBER;
v_max_y NUMBER;
v_period_name VARCHAR2 (200);
BEGIN
fnd_profile.get (NAME => 'ORG_ID', val => v_org_id);

FOR i IN 1 .. gl_je_headers_num_pkg.t_je_index
LOOP
SELECT period_name
INTO v_period_name
FROM gl_je_headers
WHERE ROWID = gl_je_headers_num_pkg.t_je (i);

-- get ¹«Ë¾¶ÎÖµ
SELECT attribute1
INTO v_company
FROM hr_organization_units
WHERE organization_id = v_org_id;

--get Ô·Ý
SELECT h.je_header_id
INTO v_je_header_id
FROM gl_je_headers h
WHERE rowid = gl_je_headers_num_pkg.t_je (i);

SELECT SUBSTR (h.period_name, 1, 2), p.adjustment_period_flag
INTO v_month_b, v_month_a
FROM gl_je_headers h, gl_periods p, gl_sets_of_books b
WHERE h.je_header_id = v_je_header_id
AND h.set_of_books_id = b.set_of_books_id
AND h.period_name = p.period_name
AND b.period_set_name = p.period_set_name;

/*AND h.je_header_id =
(SELECT je_header_id
FROM gl_je_headers
WHERE h.ROWID = gl_je_headers_num_pkg.t_je (i)); */
IF v_month_a = 'Y'
THEN
v_month := 'AD';
ELSE
v_month := TRIM (TO_CHAR (v_month_b, '00'));
END IF;

-- get max number of updated gl
SELECT COUNT (*)
INTO v_max
FROM gl_je_headers h
WHERE h.period_name = v_period_name
AND h.status = 'P'
AND h.external_reference IS NOT NULL;

--get year
SELECT SUBSTR (h.period_name, 4, 5)
INTO v_year
FROM gl_je_headers h
WHERE ROWID = gl_je_headers_num_pkg.t_je (i);

-- get year max number of updated gl
SELECT COUNT (*)
INTO v_max_y
FROM gl_je_headers h
WHERE h.period_name = v_period_name
AND h.status = 'P'
AND h.external_reference IS NOT NULL;

UPDATE gl_je_headers
SET external_reference =
TRIM (TO_CHAR (v_company, '00'))
|| v_month
|| TRIM (TO_CHAR ((v_max + 1), '000000'))
|| '|'
|| TRIM (TO_CHAR (v_year, '00'))
|| TRIM (TO_CHAR (v_max_y + 1, '000000'))
WHERE ROWID = gl_je_headers_num_pkg.t_je (i);
--gl_je_headers_num_pkg.t_je (i);
END LOOP;
--commit;
END;

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

转载于:http://blog.itpub.net/226700/viewspace-891189/