RED ALLARM: SEQUENCE ON COMPOSED PRIMARY KEY

[WalterIT]

I have a primary key composed by 2 fields:
IDFirst
IDSecond

I would like to create a sequence of IDSecond for each VALUE of IDFirst.
i.e.

IDFirst IDSecond
------- --------
12 1
12 2
12 3
13 1
13 2

SQL Server 7.0 Is able to automatically manage this kind of sequence?

Sorry for the bad english!

 

[tlbroadbent]

I'm not sure that I understand the question but hopefully this info will help. You can only create one identity column on a table. That identity column increments for the table not for sets of data. If you want a sequence within a set of data, you must write the code yourself.

You could add an insert trigger to the table. The trigger would set the value of IDSecond. Here is one method.

1) Add the following code to an insert trigger in the table. You wil need to change it to fit you needs, including table and column names.

/* Only update record if inserted IDSecond = 0 */
IF EXISTS (Select SetID From inserted Where IDSecond=0)
BEGIN
DECLARE @seq int
SELECT @seq=MAX(IDSecond)+1
FROM table1 t
INNER JOIN inserted i
ON t.setID = i.setID

UPDATE table1
SET t.IDSecond = @seq
FROM table1 t
INNER JOIN inserted i
ON t.setID = i.setID
AND t.IDSecond=i.IDSecond
END

2) Insert the record with IDSecond = 0 (zero). You could set the default of IDSecond to zero.
3) The trigger will update IDSecond to the next higher value. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.