Hi Uwe,

on Mon, 30 Jan 2006 12:05:21 +0000 (UTC), you wrote:

>Hello,
>i have many tables in the database and i use a trigger to get unique ids
> IF (NEW.ID IS NULL) THEN
> NEW.ID = GEN_ID(GEN_TABLE1_ID,1);
>for "normal" inserts like the following this works fine
>insert (ID, FIELD1) values (10,'Value');
>insert (FIELD1) values ('Value');
>
>often i must copy a record in the same table
>INSERT INTO TABLE1 SELECT * FROM TABLE1 WHERE ID = :RECORDTOCOPYID;
>
>in this case i need the trigger like this
>-- without IF (NEW.ID IS NULL) THEN
> NEW.ID = GEN_ID(GEN_TABLE1_ID,1);
>
>or is there a easy way to copy one record with all fields, except the
>primary key field ID, using a generic SP for all tables?

If it's feasible to modify all of the concerning insert triggers:

if (inserting)
then
if (exists( select id
from table1
where id = new.id))
then -- insert with duplicate id / record copy
new.id = gen_id( gen_table1_id, 1);
else -- insert with undefined / unique id
new.id = coalesce( new.id, gen_id( gen_table1_id, 1));

Regards

Christian