Update row -- autogenerate unique value without trigger??

[karenc]

I need to define a column to contain a unique value (across the table), with the value automatically generated on insert and on update without using a trigger and without explicitly specifying the column in the insert/update statement.

This would be similar to SqlServer "timestamp" datatype, which is always updated and does not require a trigger or explicit reference in the insert/update statement.

I have looked into identity columns, sequences, and generate_unique (), but in all cases, to get the value updated, a trigger is needed. (Inserts are fine.)

Any ideas on how to do this?

[Snib]

KarenC,

See my replies to:

http://www.thescripts. com/forum/thread615216.ht ml

and


http://www.thescripts. com/forum/thread615216.ht ml

I have always done this as a seperate unit of work in DB2 due to roll-back and duplications issues. If I was using MS Access I would use the "AutoNumber " field type but I have not yet figured out a way to replicate this in DB2. Stored procedures and triggers could possible be used, but as I have mentioned in one of the above replies, I tihkn this may have issues as the creation of the sequence number would be in the same unit of work as the insert.

I have seen the use of CURRENT TIMESTAMP but this is not completely reliable and can only be used against one row at a time. As DB2 is basically generating a number each time it would (a really be would here!) be possible that two users could produce the same timestamp value when adding or updating the table. A timestamp has the format:

yyyy-mm-dd-hh.mm.ss.nnnnnn nnn

so it is untlikely that this would occur - so this is another option you could consider but I would not vouch for how reliable it was!

Regards

Snib

[Snib]

I did a bit more digging, you can use IDENTITY and SEQUENCE columns. If you have Db2 V9 the SEQUENCE column would seem to be the best option but you do need to generate each value, but this can be done with a trigger fired by the INSERT.

Regards

Snib