ORA-04091:

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).

/*********************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/9182041/viewspace-676206/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9182041/viewspace-676206/