At 10:26 AM 26/08/2005 -0500, you wrote:

>I have tables of data that have been pumped over from roughly corresponding
>Access tables.
>
>
>
>In one type of table, Notes regarding a patient's admissions are kept. One
>column holds the patient's id, and another the "admit ordinal."
>Unfortunately, the original Access tables did not have the admit ordinal
>column, but they did have an "Entry Date" column for when the note regarding
>the patient was entered.
>
>
>
>I want to be able to update the Firebird Notes tables by inserting the
>appropriate value in the Admit Ordinal column. IOW, this:
>
>
>
>CLIENT_ID ENTRY_DATE ADMIT_ORDINAL
>
>111 1/1/2001 <null>
>
>111 4/5/2001 <null>
>
>222 3/3/2002 <null>
>
>333 7/4/2003 <null>
>
>333 7/5/2004 <null>
>
>333 7/6/2005 <null>
>
>...
>
>
>
>should become this:
>
>
>
>CLIENT_ID ENTRY_DATE ADMIT_ORDINAL
>
>111 1/1/2001 1
>
>111 4/5/2001 2
>
>222 3/3/2002 1
>
>333 7/4/2003 1
>
>333 7/5/2004 2
>
>333 7/6/2005 3
>
>...
>
>
>
>Does anybody know how to accomplish this with a SQL Update st8ment?

create generator gen_admit_ordinal;

then ----

create trigger bi_patient_admissions
active before insert
as
begin
if (new.admit_ordinal is null) then
new.admit_ordinal = gen_id(gen_admit_ordinal,1);
end

Just make sure you exclude the field from the insert statement's input list
and also resist any temptation to let users enter this number
manually. Pulling the generator value into the app is OK, too, since it
won't interfere with the uniqueness of the value.

./hb