問題描述

我在 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

這篇關于將 AutoID 克隆到 SQL Server 中的另一個字段中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！