Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trigger to increment a column

Author  Topic 

InNomina
Starting Member

40 Posts

Posted - 2013-01-25 : 11:52:57
Table A has a PK that auto_increments which is great. But there is a 2nd column that contains Boxnumbers that ends in 50000. Is there a way to get this to increment +1 when a new record is created with out redueing the table to make it the PK?
So when the next new row is created the boxnumber would automatically be 50001?

I was thinking a trigger that would use MAX+1 but I am unsure how to write it.

-------------------------
"If you never fail, you're not trying hard enough"

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 12:06:01
If you can make it a computed column, that would do it.
CREATE TABLE YourTable (PK INT NOT NULL IDENTITY(1,1) PRIMARY KEY, boxnumber AS 50000+PK);
For an existing table, add it as a new column using alter table and if you are happy drop the existing column.

There is an article on SQLTeam blog about generating custom sequences in T-SQL; I have not read through it entirely, but it might be useful to you http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server
Go to Top of Page
   

- Advertisement -