Re: Sql Loader + Trigger

From: Asger Svane <asvane_at_post1.tele.dk>
Date: Wed, 5 Aug 1998 21:17:48 +0200
Message-ID: <MPG.1032cd6eeee4ba29989680_at_news.tele.dk>


In article <6q991i$7ep$1_at_nnrp1.dejanews.com>, drmix_at_hotmail.com says...
> Hi to all i'am a orale newbie;
>
> I have a problem, i must load a table by Loader whith append condition, but
> before i append a new record i must delete from table all the article that
> have the same date of the article that i must load, e.g:
>
> TABLE pippo
>
> id qta date
> --- ------ -------
> 001 11 03/06/98
> 002 12 03/06/98
> 004 0 07/06/98
> 005 1 07/06/98
> 006 2 07/06/98
> 007 3 07/06/98
> 008 4 07/06/98
>
> ok, in my infile i have all records that have date=07/06/98 and so before load
> i must delete all records that have date=07/06/98.
> Note that when i load i don't know the date of the records.
> I have made a before trigger that delete all records with date=07/06/98 but it
> don't works correctly it insert in table only half of record e.g. if in file
> has 60 rec. it insert 30!
> Can someone Help me!
> ****** PLEASE REPLAY MY BY E-MAIL BECAUSE I CAN'T OFTEN READ NEWS !!!!
> drmix_at_hotmail.com
> Thanks to all for attention, and excuse me for my bad english!!
>
> Bye :-) Domenico Ricci From italy.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
Hi Domenico Ricci .
I'm working with the sort of problem you have - as I understand it -every

day. What we do is to load new data in a dayly transaction table with truncate, let's call a table_dt and lets us call the main table table_hist.
We load the daily transaction table in tablez_dt. On the table tablez_dt we have an on-insert trigger that test if the transaction allready is on the table_hist (using primary key), if found then it update the tablez_hist, if not-found it insert the record into table_hist.
Let assume that table tablez has the structure field1, field2, field3 and

field4 (that goes for table_dt and table_hist). Primarys key in table_hist is (field1, field2).
The trigger looks like this:

create or replace trigger tablez_trigger                               
after insert on tablez_dt                                       
for each row
begin
update  tablez_hist                                             
          field3 = :new.field3,                                          
          field4 = :new.field4
    where field1 = :new.field1                                     
      and field2 = :new.field2                                           
  ;                                                                      
  if sql%notfound then                                                                  

   insert into tablez_hist (                                      
         field1,  
         field2,                                                         
         field3,                                                         
         field4,                                                                                   

)                                                                        
     values (                                                                                             

       :new.field1,                                                        

       :new.field2,                                                        

       :new.field3,                                                        

      :new.field4
      ) 
     );                                                                        


  end if;                                                               
end;                                                                    
/ Received on Wed Aug 05 1998 - 21:17:48 CEST

Original text of this message