How to increment Row value(Running Number) in sql

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

How to increment Row value(Running Number) in sql

Author

Topic

rajkumar.r
Starting Member

1 Post

Posted - 2012-02-09 : 01:21:36

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER TRIGGER [PIP_ENTRYINSERT] ON dbo.PIP_Entry

AFTER INSERT AS

UPDATE PIP_ENTRY SET pipcode = (SELECT 'PIP'++CONVERT(VARCHAR,MAX(pipcode1)+1) FROM PIP_ENTRY) where pipcode =''
UPDATE PIP_ENTRY SET pipcode1 = (SELECT MAX(pipcode1)+1 FROM PIP_ENTRY) where pipcode1 = 0

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The Above trigger i have used to increment row value when ever data is inserted (ie., After insert trigger). Here i have a problem when i use this trigger in web application. The actual problem is number of user's inserting data at same time then it is not working it is not able to generate running number for all the users. Because this trigger works like row-identity.
So i need solution for this post.
Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-09 : 12:48:45

why should you use trigger for this? why not declare rowid as an identity column so that it does auto increment automatically on each insertion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-09 : 12:49:09

ok..so you need custom sequence also. in that case use

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources