Hi mgutzait,
The way I see it - you need to have a increment value that starts from 1 for each division value...
When the table is created just give the default value for increment as 0(zero)...
And then in the Insert trigger for the table try out the following :
Declare @Cur Int
-- current division value being inserted
Declare @max-2 Int
-- max. existing division value
Declare @Increment Int
-- Incrementer Value
SET @Incrementer = 0
SELECT @Cur = Division from Inserted
SELECT @max-2 = Max(Division) from TableName
IF @Cur > @max-2 -- new division entered
BEGIN
SET @Incrementer = 1
END
ELSE
BEGIN
SET @Incrementer = @Incrementer + 1
END
Update TableName set Increment = @Incrementer where Increment = 0
The default value of 0 is used for the last update...as you said we don't know which value is being inserted so by giving a default value that we know will not exist in the existing data we can update that from the trigger...
I hope all this makes sense...and I hope this addresses the problem being faced...