将 AutoID 克隆到 SQL Server 中的另一个字段中
- 作者: 油炸土包子
- 来源: 51数据库
- 2023-02-07
问题描述
我在 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
推荐阅读
热点文章
检查拆分键盘
0
带有“上一个"的工具栏和“下一个"用于键盘输入AccessoryView
0
Activity 启动时显示软键盘
0
UIWebView 键盘 - 摆脱“上一个/下一个/完成"酒吧
0
在 iOS7 中边缘滑动时,使键盘与 UIView 同步动画
0
我的 iOS 应用程序中的键盘在 iPhone 6 上太高了.如何在 XCode 中调整键盘的分辨率?
0
android:inputType="textEmailAddress";- '@' 键和 '.com' 键?
0
禁用 iPhone 中键盘的方向
0
Android 2.3 模拟器上的印地语键盘问题
0
keyDown 没有被调用
0