How to increment Row value(Running Number) in sql server

Question

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

Jignesh Trivedi · Answer

Hi AFTER triggers are stored procedures that occur after a data manipulation statement has occurred in the database such as a delete statement. DDL triggers are new to SQL Server 2005 and allow you to respond to object definition level events that occur in the database engine such as a DROP TABLE statement. INSTEAD-OF triggers are objects that will execute instead of data manipulation statements in the database engine. For example attaching an INSTEAD-OF INSERT trigger to a table will tell the database engine to execute that trigger instead of executing the statement that would insert values into that table. for creating please refer. http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx In your Instead of trigger user insert into statement and @@RowIdentity. hope this help.