Re: Instead_of_insert trigger

Okay - here is a new copy of the trigger - i am no longer trying to insert on the view.

create or replace trigger trg_vRF_adv_data   instead of insert on vrf_adv_data
declare
  rCount NUMBER;
begin

   SELECT COUNT(AdvertiserID) INTO rCount FROM vRF_adv_data WHERE AdvertiserID = :new.AdvertiserID;
  IF rCount>0 THEN

     Update tblRF_adv_data SET tblRF_adv_data.Impressions = :new.Impressions, tblRF_adv_data.Clicks = :new.Clicks WHERE tblRF_adv_data.Advertiserid = :new.advertiserid;   ELSE
     INSERT INTO tblRF_adv_data (tblRF_adv_data.AdvertiserID, tblRF_adv_data.Impressions, tblRF_adv_data.Clicks) values (:new.AdvertiserID, :new.Impressions, :new.Clicks);   END IF;
end trg_vRF_adv_data;

But I still get the same error message:

Error: ORA-04098: trigger 'CUSTREP1_APPS.TRG_VRF_ADV_DATA' is invalid and failed re-validation

I get this error message when i try and insert a record into the view (which is when i want the trigger to fire).

Thanks for the help.
Matt Karasick

In article <976181862.1971.0.nnrp-02.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Dec 09 2000 - 20:16:00 CST