如何生成ID为每插入一次递增的列. [英] How to generate a column with id which increments on every insert.

问题描述

这是我的表，我希望将我的PNRNo生成为第一个条目的``PNRRES001''，以及要连续生成的带有``PNRRES002''，``PNRRES002''的条目，依此类推.
因此，当仅创建表时，我调用了该列以运行它将生成PNR no的列，用户只需要从前端输入CustomerNo，然后使用PNR&的数据即可.客户编号将更新到PNRDetails表.

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...

推荐答案

您将标识符设置为整数类型，然后将字段的Identity Specification属性(在sql服务器管理器中使用设计器)设置为是".

然后，每当您执行插入操作时，它将自动增加该字段.

You cave to set the identifier as an integer type, and then set the Identity Specification property of the field (use the designer in sql server manager) to "YES".

Then, whenever you do an insert, it will auto-increment the field.

在表定义中，将此列标记为isidentity = true并使用身份规范

while in table definition mark this column as isidentity = true and use identity specifications

这篇关于如何生成ID为每插入一次递增的列.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！