何处/如何创建参考号
我正在使用实体框架和 MSSQL...
插入记录时我需要插入自定义参考号。格式为YYYY-01、YYYY-02等，但新年开始时需要重置序号。
例如 2011-01、2011-02、2012-01
我很好奇我是否应该使用触发器或使用EF 或？
每年重置顺序编号让我有点困惑......
感谢您的建议！
更新：
抱歉，无法让 Code 标记与标记很好地配合使用
--变量
声明 @year INT,
@seqNum INT;
--尝试查找[ComplaintCount] 表是否已包含当前年份
SET@year=(SELECT[Count_Year]
来自[投诉计数]
哪里 [Count_Year] = YEAR( 获取日期()))
--如果在 [ComplaintCount] 表中找不到当前年份，则当前年份的新记录;需要;待制作
IF @year IS NULL
开始
--获取当前年份并设置初始序列数以开始计数对于新年
SET @year =YEAR(Getdate());
SET @seqNum = 1;
--将新的默认值插入到[ComplaintCount]表中
插入[投诉计数]
(count_year,
count_current)
值 (@year,
@seqNum);
END
其他
开始
--我们在当前年份的[投诉计数]表中已找到一条记录
--获取序列号，并将其增加一个
SET @seqNum = (SELECT [Count_Current]
来自 &n公共服务提供商;[投诉数量]
哪里 [ Count_Year]= @year)+ 1
--将新值插入到[ComplaintCount]表
更新[投诉计数]
SET [Count_Current] =@seqNum
哪里 [Count_Year] = @year;
END
--现在可以安全地将正确的参考号插入到[投诉]表中
更新 更新[投诉]
SET [Complaint_Reference] = CAST(@year AS VARCHAR)+ '-' + 演员表(
; @seqNum AS VARCHAR)
来自 [投诉]
INNER JOIN插入
开启[投诉].[PK_Complaint_Id]= 已插入。[PK_Complaint_Id]
I'm using Entity Framework and MSSQL...
I need to insert a custom reference number when a record is inserted. The format is YYYY-01, YYYY-02, etc but the sequential number needs to be reset when a new year begins.
For example 2011-01, 2011-02, 2012-01
I'm curious if I should just go with a trigger or manage this with EF or ?
Having the sequential numbering reset each year has me a little confused...
Thanks for any advice!
Update:
Sorry, couldn't get the Code tag to work well with the markup
--Variables
DECLARE @year INT,
@seqNum INT;
--Try to find if the [ComplaintCount] table already contains the current year
SET @year = (SELECT [Count_Year]
FROM [ComplaintCount]
WHERE [Count_Year] = YEAR(Getdate()))
--If the current year cannot be found in the [ComplaintCount] table, a new record for the current year needs to be made
IF @year IS NULL
BEGIN
--Get the Current Year and set the initial sequence number to start counting for the new year
SET @year = YEAR(Getdate());
SET @seqNum = 1;
--Insert the new default values into the [ComplaintCount] table
INSERT INTO [ComplaintCount]
(count_year,
count_current)
VALUES (@year,
@seqNum);
END
ELSE
BEGIN
--We found a record already in the [ComplaintCount] table for the current year
--Get the sequence number and increase it by one
SET @seqNum = (SELECT [Count_Current]
FROM [ComplaintCount]
WHERE [Count_Year] = @year) + 1
--Insert the new values into the [ComplaintCount] table
UPDATE [ComplaintCount]
SET [Count_Current] = @seqNum
WHERE [Count_Year] = @year;
END
--Its now safe to insert the correct reference number into the [Complaint] table
UPDATE
UPDATE [Complaint]
SET [Complaint_Reference] = CAST(@year AS VARCHAR) + '-' + CAST(
@seqNum AS VARCHAR)
FROM [Complaint]
INNER JOIN inserted
ON [Complaint].[PK_Complaint_Id] = inserted.[PK_Complaint_Id]
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我想说的是触发器。创建一个两列表来存储年份和当前记录编号，然后使用触发器查找当前年份，将计数列加一，然后将该计数返回给触发器。在触发器中构建逻辑，如果新年不存在，则插入新年记录。我知道大多数人都喜欢尽可能避免触发器，但这是触发器的一种相当合法的使用，并且比尝试对每个插入的记录进行计数要少得多的处理。
当您尝试审核过去的一年或回答 BI 问题时，每年使用一行及其相关计数也可能在将来很有用。
I'd say a trigger. Create a two column table that stores the year and the current record number and then uses a trigger to look up the current year, increment the count column by one, then return that count to the trigger. Build logic into the trigger that if the new year doesn't exist, insert the new year record. I know most people like to avoid triggers if possible but that's a pretty legit use of a trigger and way less processing than trying to count records on every insert.
Having a single row for every year and it's related count may also prove useful in the future when you're trying to audit a past year or answer BI questions.