Ruben,

You can construct a trigger that will get the results you desire, but the
Key column must be GENERATED BY DEFAULT AS IDENTITY so that it can be
changed with an update statement. The trigger will look something like
this:

CREATE TRIGGER RUBEN.MYTRIG
AFTER DELETE ON RUBEN.MYTABLE
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (1=1)
UPDATE RUBEN.MYTABLE SET KEY = KEY - 1 WHERE KEY > O.KEY;

The number of updates that will occur on the table will depend on what row
gets deleted. In a million row table, if you would delete row 1, you can
guess how many rows will eventually get updated.

Regards,
Gordon

-----Original Message-----
From: Ruben Prakash D
[mailto:db2udbdba-ezmlmshield-x86770929.[Email address protected]
Sent: Sunday, May 29, 2005 8:54 AM
To: LazyDBA Discussion
Subject: RE: Auto Generated Keys

Rick,

Since it is a surrogate key I thought of having in an order. I accept it has
adverse effect if I delete a row it will cause cascade update to every key
row coming after the deleted row in the table. But still I want know is
there any way to do that?

Will it be possible using triggers?

Regards,
Ruben

-----Original Message-----
From: McClendon Rick
[mailto:db2udbdba-ezmlmshield-x53248595.[Email address protected]
Sent: Saturday, May 28, 2005 9:57 PM
To: LazyDBA Discussion
Subject: RE: Auto Generated Keys

Even if you invent a way to do this I don't think you would like the effect
because the delete of one row, KEY # 1 for example, would cause a cascade
update to every key row in the table.

My question - can't the data remain "Key 1,3,4,5..." when #2 is deleted?

It is still unique and still in order.

-----Original Message-----
From: Ruben Prakash D
[mailto:db2udbdba-ezmlmshield-x69088801.[Email address protected]
Sent: Saturday, May 28, 2005 8:47 AM
To: LazyDBA Discussion
Subject: Auto Generated Keys

I want to create a table with a surrogate key (auto incremented) as a
primary key. Is there any function that is available in DB2 so that when I
delete a row from the table it will adjust the key? For example if my table
contains rows in a order



Key

---

1

2

3



If the row #2 is deleted then order should be



Key

---

1

2



Will it be possible in DB2?



Regards
Ruben






---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




DB2 & UDB email list listserv db2-l LazyDBA home page