Thanks Helen,
Your response answers my question (which I didn't actually ask :(( ). That
being....Will the Insert error occur in the Before Insert Trigger? Your
solution would indicate that it does.

My Unique Constraint and the issue of non atomic key is a bit tricky, since
the application is part of a replication system.
The data is being fed to 2 servers via 2 feeds. I have PK in each (atomic
via Generator), however I cannot guarantee that for a given record, the PK
will have the same value on each server (100 things could go wrong to get
them out of step).
What I am left with is using a few columns to generate a Unique key.
Fortunately, users are not entering data into these, only the data feed.

Enough of ramble.
Thanks again for your invaluable input
John

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <ib-support@yahoogroups.com>
Sent: Monday, February 11, 2002 1:08 PM
Subject: Re: [ib-support] Re: Changing Insert to Update.


> John,
>
> At 12:38 PM 11-02-02 +1030, you wrote:
> >thanks Marco,
> >That is pretty much the response I expected. Maybe I am trying to push
too
> >much back to the server.
>
> Why so? a SP is a server-side process...
>
> create trigger bi_aTable for aTable
> active before insert position 0 as
> begin
> WHEN SQLCODE -803 DO
> EXECUTE PROCEDURE UpdateExisting(new.PriKey, new.AColumn, new.BColumn,
new.CColumn....);
> end
>
> create procedure UpdateExisting(PriKey sometype, AColumn sometype, BColumn
sometype.....)
> as
> begin
> update ATable
> set AColumn=:AColumn,
> set BColumn=:BColumn,
> set CColumn=:CColumn,
> set.......
> where APriKey = :PriKey;
> end
>
> Ideally, something like this is possible if your uniqueness decision/key
violation revolve around the primary key. From your description it's
impossible to tell whether the decision has to be made based on a value that
has a unique constraint but is not the primary key. If that's the case then
these steps aren't sufficient to replace the existing record and you'll need
to earmark the existing record for deletion. Messy if you have cascade
triggers around the place and especially messy if users are allowed to enter
or modify the value that's crucial to the logic. (Toot-toot - don't let
users modify keys...don't use non-atomic data for keys....)
>
> regards,
> Helen
>
> All for Open and Open for All
> Firebird Open SQL Database · http://firebirdsql.org
> _______________________________________________________
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>