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
_______________________________________________________