Re: INSTEAD OF INSERT trigger and SELECT

Galen Boyer <galenboyer_at_hotpop.com> writes:

> On Fri, 14 Mar 2003, gq437x_at_yahoo.de wrote:
>>
>> We insert into one table from many clients via mass inserts.
>
> How does the data get there? Do the clients insert it? Do you guys get
> file feeds and load into a staging table? (ie, what's the steps to
> performing a mass insert?)

Hello Galen,

Thank you for asking. No, we are not importing files.

The data is produced by the clients. It is a result of some calculation. The client inserts the rows at the end of each calculation cycle into the database using the OCI C-API using an executemany. The rows produced by one client can partly overlapp with rows produced by other clients on a similar, but slightly different calculation. Each client ensures it will send each row only once to the database.

The natural key data is so large, that we can't afford to include them into referring tables. So, rows in other tables referre to rows in this table through the artificial ID value for space reasons. Therefore the artificial primary key (ID) for each row is calculated from the natural key data on the client (kind of compression). This ID calculation is guaranteed to produce unique results.    

The 'select count(*)' part in my trigger is meant to tell me the difference between acceptable identical rows and invalid ID calculations on the client.

We thought about preloading the existing rows from the database, but the table with trigger will be larger than the local client memory. And preloading rows will not prevent collisions of rows produced by different clients in parallel after the calculation has started. So, the duplicate detection has toe be done by the database. Thats where it belongs anyway, IMHO.

Maybe the phrase 'mass insert' causes a misunderstanding.

Thank you,

Volker

-- 
Volker Apelt