Re: Trigger on the same table

Markfort (am_at_iaeb.kloeckner-hb.de) wrote:
: Hello everbody,
 

: another problem with ORACLE.
 

: We have a
 

: TABLE A ( column a1, a2, a3)
: and a
: TABLE B ( column b1, b2)
 

: On the table A are triggers for each row to fill b2.
 

: UPDATE B
: SET b2 = ( SELECT max(a3) FROM A WHERE a1 = new.a1 and a2 = new.a2);
 

: This isn't allowed in ORACLE 7.0xxxx, but we use a view on table A
: to do the select.
 

: SELECT max(a3) FROM VIEW_OF_A WHERE a1 = new.a1 and a2 = new.a2
 

: Now we have got ORACLE 7.1xxxx and the cheat doesn't work anymore.
: We can't use a mirrow table to solve this problem cause the table
: A is much too big.
 

: Do anybody know help?
 

: Thanks Andreas

: --
 

: --------------------------------------------------------------------------------
: Andreas Markfort am_at_iaeb.sw-hb.de
: --------------------------------------------------------------------------------
: Heldentum ist keine Lebenseinstellung sondern eine Todesursache.

Try using a before insert trigger. For some reason I don't know, the mutating error does not occur for before insert triggers defined at the row level. You could "cheat" this way.  

Hope this helps.

--
---------------------------------------------------------------------------
Kannan 
Email: skannan_at_fs.com
Mastech Systems Corporation

The above are my own comments and opinion. They do not purport that of 
anybody else.
       ** Different is Not Better. Better is not Different. **
---------------------------------------------------------------------------