• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4171
  • Last Modified:

[ORACLE] table %s.%s is mutating, trigger/function may not see it

Hello,

for a while now i am trying to get a clean answer to this anoying problem i have in oracle.

Some "old" triggers on tables are failing on my when ever i try to make an insert with error message:

ORA-04088: error during execution of trigger

04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql 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.

Open in new window


I have already read lots of stuff on this subject but i still do not understand how to solve this problem.

I would really apreciate it if someone could explain it to me in simple way and how to fix my trigger (code posted below).

Trigger:

create or replace
TRIGGER "update_date" BEFORE INSERT ON "scott"."data_list" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW 
declare
   numbr  number;
begin
   begin
      select count(*)
	 into numbr
         from t_values s
         where   s.val = :new.val
         and   s.Date_from = TO_DATE('31.12.2100','DD.MM.YYYY');
   end;
   if numbr > 0 then
      begin
         update t_values  s set Date_from = :new.date_to -1
          where   s.val = :new.val
         and   s.Date_from = TO_DATE('31.12.2100','DD.MM.YYYY');
      end;
   end if;
end;

Open in new window


And insert statment:

INSERT INTOdata_list
(
val,
date_from,
date_to,
mid_val,
user,
date
)
(
SELECT
a.VAL,
A.date_from,
A.date_to,
A.mid_val,
'test_usr',
a.date
FROM temp_table A);

Open in new window



Thank you all for your input!
0
Caruso_eu
Asked:
Caruso_eu
  • 2
  • 2
1 Solution
 
lwadwellCommented:
A mutating trigger is one that is trying to access, via SQL, the table that trigger is on.

Your example had a different table in the trigger body to the trigger definition ... this has me confused.
0
 
Caruso_euAuthor Commented:
uhh sorry my bad >.<

here is the correct example of a mutating trigger:

create or replace
TRIGGER "update_date" BEFORE INSERT ON "scott"."data_list" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW 
declare
   numbr  number;
begin
   begin
      select count(*)
	 into numbr
         from data_list s
         where   s.val = :new.val
         and   s.Date_from = TO_DATE('31.12.2100','DD.MM.YYYY');
   end;
   if numbr > 0 then
      begin
         update data_list  s set Date_from = :new.date_to -1
          where   s.val = :new.val
         and   s.Date_from = TO_DATE('31.12.2100','DD.MM.YYYY');
      end;
   end if;
end;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Mentioned above, you cannot select from the same table the trigger is on.

There is a way around it but it is dangerous and cna produce undesired results if you don't fully understand it.  You can delare the procedure as AUTONOMOUS.  This means it runs on it's own outside the current transaction.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS00609

What is the business rule you are trying to solve?  It looks like you are checking new rows to see if a specific date exists in the table and if it does subtract 1 from the new row's date.  

I'm not sure what the rest of the where clause in the update is doing:  "and   s.Date_from = TO_DATE('31.12.2100','DD.MM.YYYY')".
0
 
lwadwellCommented:
What it look like to me is that you are performing trying to end date the previously 'open' (normally I would use null for open, not '31.12.2100' ... but hey) record on the insert of a new one.
How I have overcome that in the past is by using a statement level trigger plus a row level trigger.
- The row trigger adds into a work-list temporary table the id of the row key to end date later.
- The statement trigger processes the work-list and performs the end date work.

A example implementation is below.  The statement trigger uses a cursor ... which may not be efficient in high volume transactions, a single update statement should be used then.
CREATE GLOBAL TEMPORARY TABLE AUTO_ENDDATE_TMP (
	ID_KEY NUMBER(8,0)
) ON COMMIT DELETE ROWS;

CREATE TABLE TEST_AUTO_ENDDATE (
	ID_KEY NUMBER(8,0) NOT NULL ENABLE, 
	DESCR VARCHAR2(50), 
	FROM_DATE DATE, 
	TO_DATE DATE
);

CREATE OR REPLACE TRIGGER TEST_AUTO_ENDDATE_RTRIG 
BEFORE INSERT
ON TEST_AUTO_ENDDATE 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
  INSERT INTO AUTO_ENDDATE_TMP (id_key) VALUES (:new.id_key);
END;
ALTER TRIGGER TEST_AUTO_ENDDATE_RTRIG ENABLE;

CREATE OR REPLACE TRIGGER TEST_AUTO_ENDDATE_STRIG 
AFTER INSERT
ON TEST_AUTO_ENDDATE 
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
  v_date  DATE;
  CURSOR v_csr IS
    SELECT id_key, from_date, row_number() OVER(PARTITION BY id_key ORDER BY from_date desc) rn 
      FROM test_auto_enddate 
     WHERE to_date IS NULL
       AND id_key IN (SELECT id_key FROM auto_enddate_tmp) 
     ORDER BY 1,3;
BEGIN
  FOR v_rec IN v_csr LOOP
    IF v_rec.rn = 1 THEN
      v_date := v_rec.from_date;
    ELSE
      UPDATE test_auto_enddate SET to_date = v_date - 1 WHERE id_key = v_rec.id_key AND from_date = v_rec.from_date;
    END IF;
  END LOOP;
END;
ALTER TRIGGER TEST_AUTO_ENDDATE_STRIG ENABLE;

DELETE test_auto_enddate;
INSERT INTO test_auto_enddate (id_key, descr, from_date) VALUES (1,'first row',trunc(sysdate-12));
INSERT INTO test_auto_enddate (id_key, descr, from_date) VALUES (2,'second row',trunc(sysdate-12));
COMMIT;
INSERT INTO test_auto_enddate (id_key, descr, from_date) VALUES (1,'first new from',trunc(sysdate-11));
COMMIT;
INSERT INTO test_auto_enddate (id_key, descr, from_date) VALUES (1,'compound from 1',trunc(sysdate-10));
INSERT INTO test_auto_enddate (id_key, descr, from_date) VALUES (1,'compound from 2',trunc(sysdate-9));
COMMIT;

Open in new window

0
 
Caruso_euAuthor Commented:
Awesome!  This is what i was hoping for!

Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now