trigger mutating problem

[anuj]

Hi
My problem is like this
I have two tables called
1) Bis_Geol_leg and another is 2) Geol

The table BIS_Geol_leg is master table with two column called
geol_geo2 and Geol_leg this is Composite primary key
it means unique combination od data near about 100 records like

sh 100
ch 200
ms 238
tm 292
------
----
in another table geol_geo2 having lots of colum
likw hole_id,hole_depth Geol_geo2 and geol_leg
when we insert into geol table that time geol_leg value is null.

i have to create a trigger on isert or update on Geol to update geol_leg value
Geol_geo2 value may be repeted. if insert some value it should look geol_geo2 value in bis_geol_leg table and fetch it correspong geol_leg and update into geol_leg of Geol_leg

like
Geol
--
hole_id, hole_depth ,geol_geo2, geol_leg
no key is unique in geol.

i am getting mutating errror i insert or new row or update on geol_geo2 of Geol to get geol_leg value from BIS_Geol_leg table.

Can any tell me the solution

[Raminder]

You have to post the TRIGGER code for us to see where the problem is.

[Shestakov]

You try update/insert some table in one trigger (this is absolutly impossible).

We may use 3 triggers and 1 package, but -->
For solve this problem need little bit more information:
1) Has table geol_geo2 any PK (or UNIQUE) ?
2) Has table geol_geo2 FK to table Geol ?

[DevangP]

Originally posted by anuj
Hi
My problem is like this
I have two tables called
1) Bis_Geol_leg and another is 2) Geol

The table BIS_Geol_leg is master table with two column called
geol_geo2 and Geol_leg this is Composite primary key
it means unique combination od data near about 100 records like

sh 100
ch 200
ms 238
tm 292
------
----
in another table geol_geo2 having lots of colum
likw hole_id,hole_depth Geol_geo2 and geol_leg
when we insert into geol table that time geol_leg value is null.

i have to create a trigger on isert or update on Geol to update geol_leg value
Geol_geo2 value may be repeted. if insert some value it should look geol_geo2 value in bis_geol_leg table and fetch it correspong geol_leg and update into geol_leg of Geol_leg

like
Geol
--
hole_id, hole_depth ,geol_geo2, geol_leg
no key is unique in geol.

i am getting mutating errror i insert or new row or update on geol_geo2 of Geol to get geol_leg value from BIS_Geol_leg table.

Can any tell me the solution

If you are using oracle 8 i then you can solve this problem by writting a instead of trigger on the view. This will surely solve yr problem.