Cloning AutoID into another field in 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]

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?

回答1:

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

回答2:

Triggers are not that bad. Greco’s solution is good but I’m posting how a trigger solution would be in case you are not inserting data to your tables through procedures

create trigger TR_SetDefaultValue
on [SimpleTable]
after insert 
as
begin
   UPDATE [SimpleTable] 
   SET [Position] = I.[ID]
   FROM INSERTED AS I
   WHERE [SimpleTable].ID=I.ID
end