Instead_of_insert trigger

From: <mkarasick_at_my-deja.com>
Date: Thu, 07 Dec 2000 00:12:46 GMT
Message-ID: <90mklp$72r$1@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. Received on Wed Dec 06 2000 - 18:12:46 CST