在更新 SQL Server 时创建自定义 ID（触发？）

Question

我有一个请求表，一旦请求被批准/拒绝，就需要为其分配一个自定义顺序 ID，例如：

MYCODE-11-0001
MYCODE-11-0002
MYCODE-11-0003 

其中 MYCODE 不会更改，但 11 是当前年份0001 是为年份分配的顺序生成的编号。所以每年都会从 0001 开始。

我从来没有在 SQL Sever 中处理过触发器或存储过程，所以我有通过 VB.net 中的 Linq-to-SQL 在代码中执行此操作的想法，并且我在 SQL Server 中有另一个表跟踪 Next ID

ID     NAME          NextID
1      Request2011    102
2      Request2012    1
3      Request2013    1

如果他们更改了网站上的状态，应用程序会根据当前年份选择名称并获取 NextID 并生成 RequestID。 LINQ 甚至会捕获 NextID 自获取该值、添加 1 并尝试保存该新值以来是否发生了更改。

很确定这会起作用，但我对此并不完全满意，因为它假设一切都来自我的应用程序。如果它是在数据库级别完成的，我会感觉好得多，如果它不依赖于我拥有的这个 NextID 表，我会感觉更好。

有没有什么触发器和存储过程不会太疯狂，可以让我生成这些自定义 ID？我有一个模糊的想法，当 StatusID 更新时我可以运行触发器来运行存储过程来完成一些繁重的工作（如果尚未设置，则生成 RequestID ），但如果我仍然必须依赖 NextID 表，那么我还必须担心锁定/解锁表，对吗？

只是希望能得到比我更了解 SQL Server 的人的一些建议。

注意： 自定义 ID 仅在批准/拒绝请求时分配，而不是在将请求插入数据库时​​分配。每个请求都有它自己唯一的自动生成的身份 PK。

---

更新：我可能必须坚持使用代码解决方案，因为就并发性而言，它为我提供了所有帮助，但我确实编写了一个触发器，以便我可以学习。

/* --------------------
        SETUP 
--------------------*/
CREATE TABLE [dbo].[TestData](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [data] [varchar](100) NULL,
    [RequestID] [varchar](25) NULL,
    [StatusID] [int] NULL
    )

    INSERT INTO TestData
           ([data],[StatusID])
     VALUES
           ('test',1)
GO


CREATE TABLE [dbo].[NextID](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](75) NULL,
    [NextID] [int] NULL,
    [DateModified] [date] NULL,
    [ModifiedBy] [varchar](75) NULL
    )
    INSERT INTO NextID
           ([Name],[NextID])          
     VALUES('RequestID2011',1)           
GO

/* --------------------
        TRIGGER
--------------------*/
IF EXISTS (SELECT name FROM sys.objects
      WHERE name = 'UpdateStatusID' AND type = 'TR')
   DROP TRIGGER dbo.UpdateStatusID;
GO

CREATE TRIGGER UpdateStatusID
ON dbo.TestData
AFTER UPDATE 
AS 
IF ( UPDATE (StatusID))
BEGIN
    DECLARE @nextId int
    DECLARE @nextIdName varchar(50)

    /* -- Get NextID based on year's count IE: RequestID2011 */
    SET @nextIdName = 'RequestID' + CONVERT(VARCHAR, YEAR(GetDate()), 50)
    SET @nextid = (Select NextID from dbo.NextID where Name = @nextIdName)

    /* -- Increment NextID */
    UPDATE dbo.NextID set NextID = @nextid + 1 WHERE Name=@nextIdName

    /* -- Set New RequestID */
    UPDATE dbo.TestData
    SET RequestID = 'MYCODE-' + RIGHT(@nextIdName,2) + '-' + CONVERT(VARCHAR, REPLICATE('0', (4- LEN(@nextid))) + @nextid, 50)
    FROM inserted i INNER JOIN dbo.TestData t
    ON i.id = t.id 

END;
GO
/* --------------------
        TEST
--------------------*/
UPDATE dbo.TestData
SET StatusID = 3
WHERE ID = 1;
GO

这样做的作用是防止有人多次更改 StatusID（他们可以这样做，但我只需要生成一次 ID）或防止人们获取 NextID > 同时存在竞争条件问题。

这也仍然需要我在未来几年的数据库中保留几条“RequestIDXXXX”记录。

警告：这不处理并发性，只是我理解触发器和 SQL Server 过程中的一个正在进行的工作。

Answer 1

是的，如果做得正确的话，拥有这样一个用于下一个 ID 的表是一个热点，而且通常也是一个瓶颈。如果没有，那么您将得到重复的 ID...

现在，SQL Server 2008 R2 并没有真正为您提供好的答案。这可能会随着代号为“Denali”的 SQL Server 2011（或可能：SQL Server 2012）而改变。 Denali 将引入序列，它允许您在数据库核心的控制下创建顺序 ID，并且保证这些 ID 是唯一的。您还可以在每年新年的 1 月 1 日将序列重置回 1。

查看 Aaron Bertrand 关于 SQL Server vNext 的博客文章(Denali)：使用 SEQUENCE 了解有关序列的更多信息。

就目前而言，是的，我想您使用 Linq-to-SQL 从客户端设置这些 ID 或在触发器内设置这些 ID 的方法可能是最合适的方法（我自己更喜欢触发器解决方案）。我认为该表上的一个 INSTEAD OF INSERT 触发器就足够了 - 如果您插入一个新行，获取您需要的新请求 ID 并将其设置为您刚刚插入的值。

Answer 2

您确实可以使用桌子上的触发器来完成此操作，请参见下文：

create table NextID (
    id  int,
    name    varchar(10),
    nextid  int
)

create table TestData (
    sequence    varchar(14) NOT NULL,
    data    varchar(10)
)

insert into NextID
values(1, 'Req2011', 1)


--Important stuff starts here
ALTER TRIGGER MySequence
ON TestData
INSTEAD OF INSERT
AS
DECLARE @nextid int

SET @nextid = (select nextid from NextID ) - 1

UPDATE NextID
SET nextid = nextid + (select COUNT(*) from inserted)
WHERE id = 1;

WITH cte AS (
    select ROW_NUMBER() OVER (ORDER BY (select 1)) + @nextid as sequence, i.data
    from inserted i
)
insert into TestData
select 'MYCODE-' + RIGHT(YEAR(GETDATE()), 2) + '-' + RIGHT('000' + CAST(sequence as varchar), 4), data
from cte;

这是一种非常可靠的方法。请注意，触发器被编写为允许一次插入多条记录。

参考：
http://msdn.microsoft.com/en-us/library/ms189799.aspx

http://www.sqlmag.com/article/sql-server/nondeterministic-row-numbers

Answer 3

您可以尝试这样的操作：

DECLARE @CustomID VARCHAR(255)
SELECT @CustomID = 'MYCODE-' + CAST((YEAR( GETDATE()) % 100) AS CHAR(2)) + '-' + REPLICATE('0', (4 - LEN((<Sequence> + 1)))) + CAST((<Sequence> + 1) AS VARCHAR(5))

编辑

我不知道您希望如何获取序列，但是如果您想从表上的行计数中提取它，您可以执行以下操作：

SELECT COUNT(SomethingID) --Optionally add +1
FROM   SomethingTable
WHERE  DATEDIFF(YEAR, SomeDateColumn, GETDATE()) = 0

Answer 4

我建议这不是主键思路中的“id”。

您可以改用持久计算列。其效果是：

ALTER TABLE myTable
ADD CustomID AS (

    'MYCODE-' + CAST(RIGHT(100 + year([date_field]),2) as varchar(2)) + CAST(RIGHT(10000 + [pk_field],4) as varchar(4))

) PERSISTED

这样，只要将新记录添加到表中，工作就会为您完成。

这里需要注意的是，如果您更新了 [pk_field] 列（这不太可能......？），您的计算列将重新计算。但除此之外，只要您使用“PERSISTED”，它将保持不变。

编辑
我用上面的 [date_field] 替换了 getdate() 。我仍然认为计算列对您来说是一个不错的选择，但需要注意的是您需要记录上的日期字段（date_inserted，或者您有什么），如 getdate() （或其他非-确定性值）不能使用。