根据主键，当年和上一年的值自动生成字段的值 [英] Auto-generating value for a field based on the value of primary key, current year and previous year

问题描述

我有一个非常有趣的问题。我正在尝试根据 ID（主键字段）的值来为名称字段生成值。

I have a very interesting question. I am trying to generate value for the 'Name' field based on the value of the 'ID' (primary key field).

例如，

ID为142

名称为19142。 19表示当前年份。

The 'Name' will be 19142. '19' indicating the current year.

为此，我编写了以下触发器（效果很好）：

For this, I wrote the following trigger (which works fine):

CREATE TRIGGER [dbo].[GenerateTheName]
ON [dbo].[Table1]
AFTER INSERT
AS
BEGIN

UPDATE [dbo].[Table1]

SET [Name] = (SELECT FORMAT(GETDATE(),'yy')) +
             (SELECT CAST(FORMAT([ID],'000','en-US') AS VARCHAR(4)) FROM inserted)

WHERE ID = (Select [id] FROM inserted)

END

现在，当年份更改时，名称字段值中的id部分需要从零重新开始，而实际ID应该

Now, when the year changes, the id part in the value of the 'Name' field needs to be restarted from zero while the actual ID should follow the identity and be incremented by 1 as usual.

因此，如果2019年的最后一条记录的ID = 164，则第一条记录2020年应为：

So if the last record in the Year 2019 has ID=164, the first record in the year 2020 should be:

ID：165
名称：20001

ID: 165 Name: 20001

通过创建一个新表 table2并（通过触发器）在其中存储当年（2019年）的 ID的最后一个值来实现此目的。然后在整个2020年，此记录的数据可用作参考。

One way to achieve this is by creating a new table say 'table2' and storing (through a trigger) the last value of 'ID' for the current year (2019) in it. Then for the entire next year 2020, this record's data can be used as a reference.

因此，如果2020年的第一条记录具有ID：165
另一个触发器将从'2'的值中减去table2中的唯一值（164）。

So, if the first record in the year 2020 has ID: 165 Another trigger will subtract the only value (164) in table2 from the value of 'ID' after inserting a new record in 'table1'.

165-164=1
166-164=2
167-164=3
....

到2020年也结束了，这只来自 table2的记录将被删除，并再次存储2020年的最后一个id。

When the year 2020 also ends, this only record from 'table2' would be erased and again the last id for 2020 should be stored in it.

这是解决它的正确方法吗？请给我建议解决问题的可能方法。

Is this the right way of solving it? Kindly suggest me the possible ways to solve it.

预先感谢。

推荐答案

如果您愿意在表中添加另一列来记录插入记录时的当前日期时间，则可以使用1个表和1个触发器来完成。示例代码-

If you are willing to add another column to the table which records the current date time when the record was inserted it could all be done with 1 table and 1 trigger. Sample code -

CREATE TABLE Table1 (ID INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(20), CreatedDateTime DATETIMEOFFSET(7) DEFAULT SYSDATETIMEOFFSET())
GO

CREATE TRIGGER [dbo].[GenerateTheName]
ON [dbo].[Table1]
AFTER INSERT
AS
BEGIN


UPDATE [dbo].[Table1]
SET [Name] = (SELECT FORMAT(GETDATE(),'yy')) + (SELECT FORMAT((ISNULL(MAX(Id), 0) + 1), '000', 'en-US') FROM Table1 WHERE CreatedDateTime > DATEADD(yy, DATEDIFF(yy, 0, (SELECT CreatedDateTime FROM inserted)), 0)
AND CreatedDateTime < (SELECT CreatedDateTime FROM inserted))
WHERE ID = (Select [id] FROM inserted)

END

insert into table1 (createddatetime) values (sysdatetimeoffset())
insert into table1 (createddatetime) values (sysdatetimeoffset())

select * from table1 -- Gives 19001 and 19002 as name

这篇关于根据主键，当年和上一年的值自动生成字段的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！