By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,328 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

Update row -- autogenerate unique value without trigger??

P: 1
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?
Mar 9 '07 #1
Share this Question
Share on Google+
2 Replies


P: 24
KarenC,

See my replies to:

http://www.thescripts.com/forum/thread615216.html

and


http://www.thescripts.com/forum/thread615216.html

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.nnnnnnnnn

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
Mar 17 '07 #2

P: 24
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
Mar 19 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.