将 AutoID 克隆到 SQL Server 中的另一个字段中

2021-09-10 00:00:00 sql tsql sql-server

我在 SQL Server 中创建了下表

I have created the following table in SQL Server

CREATE TABLE [dbo].[SimpleTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [OtherField] [varchar](50) NOT NULL,
    [Position] [int] NULL,
 CONSTRAINT [PK_SimpleTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

当插入新记录时，我想用 IDENTITY 列中的值更新 Position 字段，主要思想是记录应该放在最后，但用户稍后可以更改该位置.

When a new record gets inserted I want to update the Position field with the value from the IDENTITY column, the main idea is that the record should be positioned at the end, but the user will be able to change that position later on.

我想过创建一个触发器并在插入后更新位置字段，但是我会有很多这样的表，如果可以的话，我真的想尽量远离触发器.

I thought about creating a Trigger and update the position field after insert, however I will have a lot of these tables and I really want to try to stay away from triggers if I can.

有人有更优雅的解决方案吗?

Does anyone have a more elegant solution?

推荐答案

这应该在存储过程中工作.

This should work inside a stored procedure. 

CREATE PROCEDURE [dbo].[InsertIntoSimpleTable]
    @Name varchar(50)
,   @OtherField varchar(50)
AS
BEGIN
    DECLARE @Id INT;

    --- Do your insert here.
    INSERT INTO SimpleTable (Name, OtherField)
    SELECT @Name, @OtherField
    ;

    SELECT @Id = SCOPE_IDENTITY();

    UPDATE SimpleTable SET Position = @Id WHERE Id = @Id;
END

相关文章