Assign Count to Variable

[Eddiemonster]

I am creating a trigger in SQL to update a table under certain circumstances. The table I am updating has a record ID number. I want to count the number of records in the table and assign that count to a variable so that I can then later add one to get the next record ID. How do I do this?

 

[bperry]

declare @myCount int
set @myCount = 0
SELECT @TotalCost = COUNT(*) from MyTable

....
set @myCount = @myCount + 1

---------------------
bperry

 

[adam0101]

EddieMonster, what if someone deletes a record? The count of records would not match the max id. I'd modify the code to this:

declare @myCount int
set @myCount = 0
SELECT @myCount = MAX(ID) from MyTable

....
set @myCount = @myCount + 1