Re: Sql Loader + Trigger
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_dtfor 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