每年重置自增（identity）字段的方法

Question

我正在使用 SQL Server 2008 R2，想要创建一个触发器。

对于每个添加（仅限添加），一个列将被更新如下：

ABC-CurrentYear-AutoIncrementCode

例子:

ABC-2013-00001 

或者

ABC-2013-1

ABC永远不会改变。

当前年份可以与YEAR(GetDate())相加，但对于最后一部分，我有困难：如何获取最后一个条目编号以使最后一个条目+1？

第二个问题：

在2014年，自动递增计数器应重置为1，2015年也是如此...

- Chris

---

我认为我会使用日期时间和视图来完成这个任务。 - paparazzo

你有一个日期时间列吗？ABC来自哪里？它是静态的吗？ - gbn

另外，请问您是否计划升级到 SQL Server 2012：这个版本较之前的版本要容易得多。 - gbn

Answer 1

你可以像这样计算新值：

SELECT 'ABC-' + 
CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + 
RIGHT('00000' + CAST(ISNULL(MAX(CAST(REPLACE(YourColumnName, 'ABC-' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-', '') AS INTEGER)), 0) + 1 AS VARCHAR(5)), 5)
FROM YourTable
WHERE YourColumnName LIKE 'ABC-' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) +'%'

字符串拼接的最后一部分特别复杂：

• 使用RIGHT添加前导零
• 使用ISNULL每年重置计数器

- tezzo

Answer 2

使用一个类似于这样的示例表：

CREATE TABLE MyTable (id INT IDENTITY(1,1), someCol NVARCHAR(20), codeCol NVARCHAR(20))

有一个id列，一些随机值列和第三列存储您的代码。

您的触发器可能看起来像这样：

CREATE TRIGGER tgInsertMyTable ON myTable
AFTER INSERT
AS
BEGIN

    DECLARE @code NVARCHAR(20)
    SELECT @code = 'ABC-' + CAST(YEAR(GETDATE()) AS NVARCHAR(4)) + '-'

    DECLARE @max NVARCHAR(20)
    SELECT @max = codeCol FROM dbo.myTable mts WHERE mts.codeCol LIKE @code + '%'

    ;WITH CTE_UPD AS 
    (
        SELECT REPLICATE('0', 5-LEN(CAST(COALESCE(CAST(RIGHT(@max,5) AS INT),0) + ROW_NUMBER() OVER (ORDER BY ins.ID) AS NVARCHAR(5)))) + CAST(COALESCE(CAST(RIGHT(@max,5) AS INT),0) + ROW_NUMBER() OVER (ORDER BY ins.ID) AS NVARCHAR(5)) AS nextNo, id
        FROM INSERTED ins

    )
    UPDATE mt
    SET mt.CodeCol = @code + nextNo 
    FROM dbo.MyTable mt
    INNER JOIN CTE_UPD ins ON ins.ID = mt.ID

END

我知道有些地方看起来很复杂，所以我会尽量解释一下，但首先 - 这里有一个演示，它可以正常工作：

SQLFiddle DEMO

所以，首先我在@code变量中设置了所需的代码前缀 - 这是没有增量的部分。

第二步 - 我在表中现有行中搜索具有相同前缀的最大值 - 并将其存储在变量@max中。

第三部分是CTE - 它用于查找下一个增量值。

• 从最大值中仅获取最后5个字符（使用RIGHT函数）
• 将它们转换为整数（CAST）- 从起始零开始去除
• 如果@max为NULL- 现在声明为0（COALESCE）
• 将插入的ROW_NUMBER添加到其中 - 这很重要 - 您不能仅使用+1，因为您可能同时添加多行（例如演示）。这就是首先需要CTE的整个原因。
• 再次转换为nvarchar
• REPLICATE用于返回起始零，数量为5-LEN（新LEN被计算以在数字变为10,100,1000时添加较少的零）

最后，只需将CTE连接到表并使用@code + 计算值进行更新即可

我不确定是否可能存在更简单的解决方案，但这个解决方案可以工作-并且我认为涵盖了所有情况。

- Nenad Zivkovic

---

这对于并发操作来说是不安全的。在繁忙的系统中重叠的调用将生成重复的数字，同时由于表访问和触发器中的后续写入而导致死锁问题。 - gbn

Answer 3

但是在哪里使用这些函数呢？例如，如果学生表中有一个RegNo.需要这个自动年增属性。应该在哪里编写此代码？是在存储过程中还是其他地方？

- Muhammad Suleman