Hi @ all,
i have a problem with a db2 trigger hopefully someone can help me.
I will create a insert in a row, sometimes I have at one time two inserts, but
i need to create a unique row column with a unique nr.
To create a trigger which inserts this unique nr. is not really a problem, but
take care that the other rows which I create near at the same time became
the same number is for me a challenge :-)
Now to the trigger:

Maybe I have to insert "set" with a "select" statement ???
But I am not sure.
Maybe I am also on the complete wrong way??
Hope someone can help me!!

thanks in advance
mfgjens

what about trying out something like a sequence or an identity column.... I hear those work well.

But than I have also only one unique row. but sometimes I need the unique ID on two different rows.
And than????

No, you wouldn't have that. Your trigger is executed for each row, and it would request NEXTVAL from the sequence for each row. Sequences are synchronized, so you would never get the same sequence value for different rows being inserted.

Ok sounds not good for me
But can someone give me a hint for a way arround??

Why is this "not good"? Just use sequences and be done with it. It will be much easier than trying to use something home-grown.

Ok, maybe I have now a wrong understanding.
Right now I use a trigger like this.

I am right that this are a sequence or?
This make every row unique, but my problem is
that I sometimes have two rows from the same measurement
and in this case, this two rows needs to have the same "unique" ID.
The reason behind is, I will use this as a reference for a different table with detail information from this measurement.
I will have a clear relation between this two different tables.

Sorry if I have a wrong understanding

1. No, this is not a sequence.
2. I think you have confused everyone here by using the words "unique ID"; what you are looking for is the way of generating "just IDs"
3. A sequence may still help: when you need a new ID, fetch NEXTVAL() from the sequence, otherwise fetch PREVVAL(). You just need to make sure that no other process uses that sequence at the same time to guarantee that PREVVAL() does not change between the calls. If you cannot do that, create a function that will generate an ID according to your requirements.

Do I understand you correctly that you want to give the (say) two rows inserted with the same INSERT statement the same ID nr.?
All earlier suggestions (sequence object or identity column) will give those two rows different ID nrs.

Replace for each row in your trigger by for each statement; that trigger will be called just once per INSERT statement instead of once per inserted row, i.e., generate a common value n.row_id, e.g. retrieved from a sequence object.
Doing it this way, there is even no need to guarantee that others wouldn't use that sequence object.
Using generate_unique() is also fine, of course. Main difference being that the latter generates a CHAR(13), while a sequence object returns an integer.