Clone AutoID to another field in SQL Server
I 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 is inserted, I want to update the Position field with the value from the IDENTITY column, the basic idea is that the record should be placed at the end, but the user can change this position later.
I was thinking about creating a trigger and updating the position field after insertion, however I will have a lot of these tables and I really want to try and stay away from triggers if I can.
Does anyone have a more elegant solution?
0
source to share
2 answers
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
+1
source to share
Triggers aren't all bad. Grecos' solution is good, but Im posted how the trigger solution would be in case you don't insert data into your tables using 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
0
source to share