April 28, 2003 at 7:00 am
Hi,
I have an identity column in the table. I have another column with the division number. I need an automatic incremental number seperate for each division (only on inserts!)
For example: division increment
======== =========
1 1
1 2
1 3
2 1
I also have a unique index on division + increment (for updates).
I have tried a for-insert-trigger, and can not insert multiple records with same division and increment, since I don't know the increment number on insert.
I have tried an instead-of-insert trigger, but don't want to insert each row separately. My problem is that the identity value is not assigned yet in the inserted table while trigger runs.
Stored Procedure is out of the question...
Can anybody tell me what is the best way to apply it?
Thanks in advance!
May 1, 2003 at 8:00 am
This was removed by the editor as SPAM
May 1, 2003 at 9:39 am
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...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply