At 01:13 AM 25/01/2004 +0000, you wrote:

> > You could pass the values of the new record to a stored procedure,
>the SP checks if the PK already
> > exists and if so, updates the record. Otherwise, it inserts the
>record.
> >
> >
> > Florian
>
>So, if I understand you correctly, you are saying that
>(1) it IS the case that I must know in advance whether a record I
>want to add is to be INSERTed or UPDATEd; and
>(2) a stored procedure would be a more efficient way of doing this
>than a query.
>
>Correct??

The contrary: if you don't know (in your application) whether the record
is to be added or updated, then (one of the) right way(s) to go about this
is to assign a stored procedure to your dataset's InsertSQL property. The
SP then performs the job of checking for existence and then either
inserting or updating accordingly.

create procedure InsertOrUpdateX (
Param1 sometype,
Param2 sometype, ..)

/* include all values to be inserted or updated */

/* the input params are a list of columns in the table, also available from
your dataset, which uniquely identify a row in a table - not the PK though
if you are using a surrogate PK */
as
declare variable PKValue integer;

begin
Select ThePKey from TableX
where UQKey1 = :Param1 and UQKey2 = :Param2 and...))
into :PKValue;
if (PKValue is not null) then
begin
UPDATE TABLEX SET <the changed columns>
where ThePKey = :PKValue;
end
else begin
PKValue = Gen_ID(Generator_X, 1); /* if needed - see NOTE */
INSERT INTO TABLEX (list of input columns)
VALUES (:PKValue, :Param1, :Param2, ....);
end
end

NOTE regarding generator values:
Don't set GeneratorLinks when using this approach.

But if you have a Before Insert trigger on TableX like the following, then
don't grab it via the statement above, simply omit the generated PK value
from both the list of input columns and the VALUES list -- it will present
as null and the trigger will take care of it.

CREATE TRIGGER BI_TABLEX
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.PKValue is null) then
new.PKValue = Gen_ID(Generator_X, 1);
END

Helen