Re: INSTEAD OF INSERT trigger and SELECT

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 17 Mar 2003 12:07:50 -0600
Message-ID: <u65qh6fa7.fsf@standardandpoors.com>

On Mon, 17 Mar 2003, gq437x_at_yahoo.de wrote:
>
> Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> writes:
>

>> On Fri, 14 Mar 2003 15:25:55 +0100, Volker Apelt
>> <gq437x_at_yahoo.de> wrote:
>>
>>>Concept: 
>>>We insert into one table from many clients via mass inserts.
>>>Each row is unique and part of one unique constraint. 

> [a trigger throws re-INSERTs of already existing rows away]
>
> [I re-arranged parts of your answer.
> Please, see my message to Galen Boyer, too.]
>

>>- use deferred constraints

> I don't see how this could solve the problem. AFAIK this will
> just defer the problem until the end of the transaction.

This would actually allow you to get the exact rows that were duplicated into an error table. I forget the syntax, but enabling the constraints with some error table referenced gives you a clean table as well as identifies the problem rows.

This is a much better solution than what I proposed where you write a trigger for all rows affected.

Look up deferred constraints. This should lead you to your problem rows.

-- 
Galen Boyer

Received on Mon Mar 17 2003 - 12:07:50 CST