Re: Instead_of_insert trigger

It helps if you tell us what error messages you are getting. One important point to consider though - your 'pre insert trigger' tries to insert data into the view for which it is the pre-insert trigger, so if it gets that far, it will fire itself recursively.

Typically I would use a view-based trigger to insert into the underlying tables - and do selects from the tables, not from the view.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



mkarasick_at_my-deja.com wrote in message <90mklp$72r$1_at_nnrp1.deja.com>...


>I am trying to dynamically do an insert or an update on a table w/

>an "instead of insert" trigger on a base view over a table (am doing

>this because the records are getting loaded from sql loader).  My

>question is, how do i reference the values of the record that tried to

>get inserted.  Can I use :new ?  This is what i have so far, but it is

>wrong.  Is what i'm trying to do even possible?

>

>create or replace trigger trg_vRF_adv_data

>  instead of insert on vrf_adv_data

>declare

>  rCount NUMBER;

>begin

>   SELECT COUNT(myID) INTO rCount FROM vRF_adv_data WHERE myID

>= :new.myID;

>  IF rCount>0 THEN

>     UPDATE vRF_adv_data SET (Imp = :new.Imp, icks = :new.icks) WHERE

>myID = :new.myID;

>  ELSE

>     INSERT INTO vRF_adv_data (myID, Imp, icks) values

>(:new.myID, :new.Imp, :new.icks);

>  END IF;

>end trg_vRF_adv_data;

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.