Problem with a Validation

Hi...

I was with a problem....

I have a table with this model:

Create Table Test
(
Id_Charge Int Not Null,
Description Varchar Not Null
)

For the rule, this column Id_Charge must be incremental and when the number arrive to 999 he must be reseted to 1, with this storage for exemple:

1 AAA
2 BBB
(...)
998 YYY
999 ZZZ
1 AAB
2 BBC
(...)

I would like to keep this control for the database system, but I don't know when I make this, somebody has a idea?

Tks...

Bruno,

Here's what I've come up with. It's not very pretty but works (for single inserts). If you want to handle mutlirow inserts you shoudl get a cursor on inserted and then handle them one by one the same way as done here.

You really should some unique colomn on the table though....

Hope this helps,

Regards John

Code Snippet

create Table Test

(

Id_Charge Int Not Null,

Description Varchar(50) Not Null

)

-- tavle to keep track of last issued ID

create table LastId

(

Id int not null

)

-- set first value

insert LastID (Id) values( 1)

go

-- trigger to handle inserts

create trigger Test_NextID on Test instead of insert

as

declare @.NextId int,

@.Desc varchar(50),

@.InsertedCount int

-- make sure we're dealng with just 1 row being inserted (otherwise get a cursor on Inserted and handle them one by one)

select @.InsertedCount = Count(*) from inserted

if @.InsertedCount > 1

begin

rollback tran

raiserror ('Can only handle single inserts', 16, 1)

return

end

-- update table to next Id

update LAstID

set Id = case Id when 999 then 1 else Id + 1 end

-- get last id

select @.NextId = Id

from LastID

-- find the value that was inserted

select @.Desc = Description from inserted

insert Test (Id_Charge, Description)

values (@.NextID, @.Desc)

go

-- insert some testdata

insert TEst (Description) values ('AAA')

insert TEst (Description) values ('BBB')

insert TEst (Description) values ('CCC')

insert TEst (Description) values ('DDD')

-- skip ahead some

update lastID set Id = 997

insert TEst (Description) values ('AAA')

insert TEst (Description) values ('BBB')

insert TEst (Description) values ('CCC')

insert TEst (Description) values ('DDD')

select * from test

-- throws error

insert TEst (Description)

select description from TEst

|||

John, thank you very much... your solution is perfect for my problem...

Bruno.