How to generate a column with id which increments on every insert.

See more:

SQL-server-2000

SQL-Server-2005

, +

This is my table where i want my PNRNo to be generated as 'PNRRES001' for the first entry, and consecutive entries with 'PNRRES002','PNRRES002' so on.
So while creating table only i called that column to function which will generate the PNR no, User just has to enter the CustomerNo from the front end, and data wit PNR & Customer No will updated to the PNRDetails table.

CREATE TABLE PNRDetails(PNRNo AS (DBO.FuncIncPNR()) ,customerNo INT

--FUNCTION TO GENERATE THE PNR NUMBER
ALTER FUNCTION dbo.FuncIncPNR()
RETURNS VARCHAR(20)
AS 
BEGIN
DECLARE @RR VARCHAR(20) SET @RR='PNRRESA001'
--here i have checked if no value is there then return the first value as 'PNRRESA001'
	IF((SELECT COUNT(*)FROM PNRDetails)=0)
	BEGIN
	RETURN @RR
	END
	ELSE
-- if any value is there then take the last value and add 1 to it and update to the table
	BEGIN
	DECLARE @pnr VARCHAR(20),@S1 VARCHAR(20),@S2 INT
	DECLARE PNRCursor CURSOR Static
	FOR SELECT PNRNo FROM PNRDetails
	OPEN PNRCursor
	FETCH LAST FROM PNRNo INTO @pnr
	SET @S1=SUBSTRING(@pnr,1,7)
	SET @S2=RIGHT(@PNR,3)
	SET @S2=@S2+1;
	SET @pnr=@S1+@S2;
	
	END
	RETURN @pnr
END

--Here am inserting only customerNo as 5 and the PNR should be generated by my function
INSERT INTO PNRDetails VALUES(5)
--it shows 1 row updated  :)
SELECT * FROM PNRDetails
-- but when i run select command it shows
--Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).  :(

U can run this.And pls do help if u find anything that could help me. any help will be appreciated...
Waiting for your kind response...