Home » SQL & PL/SQL » SQL & PL/SQL » Mutation Error
Mutation Error [message #189276] Thu, 24 August 2006 00:10 Go to next message
maxboom123
Messages: 9
Registered: August 2006
Junior Member
I have table called 'XYZ' which contains columns A ,B ,C &D and A is the primary key column.

And the data in the table are as follows

A B C D
= = = =
11 5 X 1
22 5 X 2
33 5 X 3
44 6 X 1
55 6 X 2
66 6 X 3
77 6 X 4

I created a Before row insert trigger on XYZ and from the trigger i call a package which checks for C column of the same table and the query is

Select C
from XYZ
where B = :new.B
and D=2;

Since i'm trying to access the same table, it's throwing mutation error.
Can someone please help me how to overcome this problem?
Re: Mutation Error [message #189280 is a reply to message #189276] Thu, 24 August 2006 00:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you search the net you will find plenty of examples of the standard way to overcome the mutating table problem.
Maybe you could explain what exactly it is you are trying to do, because in a lot of cases there is no need to query the mutating table at all.
Re: Mutation Error [message #189293 is a reply to message #189280] Thu, 24 August 2006 01:36 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
I dont think that you would get mutating trigger error in this scenario, because when you use before insert row level trigger as long as you have single row in the table you will not get the mutation.

check the below table.
When Trigger on table refers the same table: 
----------------------------------------------------------------- 
OPERATION       TYPE                        MUTATING? 
----------------------------------------------------------------- 
insert          before/statement-level      No 
insert          after/statement-level       No 
update          before/statement-level      No 
update          after/statement-level       No 
delete          before/statement-level      No 
delete          after/statement-level       No 

insert          before/row-level            Single row   Multi-row 
                                            No           Yes 
insert          after/row-level             Yes 
update          before/row-level            Yes 
update          after/row-level             Yes 
delete          before/row-level            Yes 
delete          after/row-level             Yes 
-----------------------------------------------------------------


Further check the outcome here..

SQL> CREATE TABLE XYZ(A NUMBER PRIMARY KEY, B NUMBER, C VARCHAR2(10),D NUMBER);

Table created.

SQL> SELECT * FROM XYZ;

         A          B C                   D
---------- ---------- ---------- ----------
        11          5 x                   1
        22          5 X                   2
        33          5 X                   3
        44          6 X                   4
        55          6 X                   2
        66          6 X                   3
        77          6 X                   4

7 rows selected.

SQL> CREATE OR REPLACE TRIGGER XYZ_TRIG
  2  BEFORE INSERT ON XYZ
FOR EACH ROW
DECLARE
v_c VARCHAR2(10);
BEGIN
SELECT C INTO v_c FROM XYZ WHERE B=:NEW.B AND D=2;
DBMS_OUTPUT.PUT_LINE('VALUE OF C IS '||v_c);
END;
/  3    4    5    6    7    8    9   10  

Trigger created.

SQL> SET SERVEROUTPUT ON


SQL> INSERT INTO XYZ VALUES(88,6,'Y',5);
VALUE OF C IS X

1 row created.
SQL> SELECT * FROM XYZ;

         A          B C                   D
---------- ---------- ---------- ----------
        11          5 X                   1
        22          5 X                   2
        33          5 X                   3
        44          6 X                   1
        55          6 X                   2
        66          6 X                   3
        77          6 X                   4
        88          6 Y                   5

8 rows selected.



Here i dont get any issue. correct me if i'm wrong anyewhere

Thanks,
Thangam
Previous Topic: inbuilt oracle function
Next Topic: Need help joining tables and getting max value
Goto Forum:
  


Current Time: Thu Dec 08 19:57:47 CST 2016

Total time taken to generate the page: 0.05582 seconds