At 10:44 PM 30/01/2006, 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?

Well, yes, there is ! I'll preface this with stating that using
SELECT * is not good behaviour *ESPECIALLY* for inserts....and
continue to say that all you need is

1) to use a well-behaved SELECT statement for this
and
2) have another column in the table that will prevent this operation
from going into an endless loop.

So, for 1), NAME the columns that you want in your SELECT
specification and exclude those don't want (at least the primary key
column[s])!

For 2) add an integer column (or, for future-proofing, a BigInt),
call it TransID or something similar, include it in your insert
column-list, include the context variable CURRENT_TRANSACTION as a
constant in your SELECT statement and add to your WHERE clause "AND
TransID <> CURRENT_TRANSACTION.

Note that, if the same user is likely to be doing this operation more
than once in the same transaction, and you want to avoid copying
uncommitted rows, then you will need another column as well, possibly
carrying CURRENT_TIMESTAMP, which you can use as an additional
criterion to avoid those rows.

./heLen