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 TrivediPosted Feb 9, 2012, 11:00 PM
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.