SQL 插入两个有关系的表的存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5762942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored Procedure to Insert Two Tables with Relationship?
提问by Ye Myat Aung
I was trying to insert a new row into two tables which has a relationship between. I wrote the stored procedure as follows.
我试图将一个新行插入到两个有关系的表中。我写的存储过程如下。
ALTER PROCEDURE InsertUserProfile
(
@UserID varchar(10),
@Pass varchar(50),
@Enabled int,
@Permission int,
@Rank int,
@FName varchar(50),
@LName varchar(50),
@Phone varchar(50),
@Email1 varchar(50),
@Email2 varchar(50)
)
AS
BEGIN TRANSACTION
INSERT INTO tbl_user_login VALUES (@UserID, @Pass, @Enabled, @Permission, @Rank)
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
INSERT INTO tbl_user_profile VALUES (@FName, @LName, @Phone, @Email1, @Email2)
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
COMMIT
From this follow ASP.NET Code
从此遵循 ASP.NET 代码
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnString"].ConnectionString);
SqlCommand cmd = new SqlCommand("dbo.InsertUserProfile", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", DbType.String).Value = txtUserID.Text;
cmd.Parameters.Add("@Pass", DbType.String).Value = txtPass.Text;
cmd.Parameters.Add("@Enabled", DbType.Int32).Value = 1;
cmd.Parameters.Add("@Permission", DbType.Int32).Value = Convert.ToInt32(ddlPermission.SelectedValue);
cmd.Parameters.Add("@Rank", DbType.Int32).Value = Convert.ToInt32(ddlRank.SelectedValue);
cmd.Parameters.Add("@FName", DbType.String).Value = txtFName.Text;
cmd.Parameters.Add("@LName", DbType.String).Value = txtLName.Text;
cmd.Parameters.Add("@Phone", DbType.String).Value = txtPhone.Text;
cmd.Parameters.Add("@Email1", DbType.String).Value = txtEmail1.Text;
cmd.Parameters.Add("@Email2", DbType.String).Value = txtEmail2.Text;
sqlConn.Open();
int rows = cmd.ExecuteNonQuery();
sqlConn.Close();
But I'm getting the following error.
但我收到以下错误。
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tbl_user_profile_tbl_user_login". The conflict occurred in database "My DB Location", table "dbo.tbl_user_login", column 'ID'. The statement has been terminated.
INSERT 语句与 FOREIGN KEY 约束“FK_tbl_user_profile_tbl_user_login”冲突。冲突发生在数据库“My DB Location”、表“dbo.tbl_user_login”、“ID”列中。该语句已终止。
I'm new to Stored Procedures so any suggestions how should I fix it so that I could insert data into two tables?
我是存储过程的新手，所以有什么建议我应该如何修复它以便我可以将数据插入到两个表中？
TABLE SCHEMA
表架构
tbl_user_login
ID (int)
UserID (varchar10)
Pass (varchar50)
Enabled (int)
Permission (int)
Rank (int)
tbl_user_profile
ID (int)
FName (varchar50)
LName (varchar50)
Phone (varchar50)
Email1 (varchar50)
Email2 (varchar50)
回答by Marc Gravell
@Richard Its "ID" which is the Auto Increment in both tables.
@Richard 它的“ID”是两个表中的自动增量。
Having an auto-increment (IDENTITY) act as a primary key is fine, but using it as a foreign key is dangerous, since you can't really guarantee that they will always be in sync; any rollback could leave them broken (rollback does not undo identity increments, as this would affect other SPIDs). Also, any thread-race between two concurrent INSERTs will be in jeopardy.
将自动增量 ( IDENTITY) 用作主键很好，但将其用作外键很危险，因为您无法真正保证它们始终同步；任何回滚都可能使它们损坏（回滚不会撤消标识增量，因为这会影响其他 SPID）。此外，两个并发INSERTs之间的任何线程竞争都将处于危险之中。
The correct approach here is to query SCOPE_IDENTITY()after the first insert, and use that in the INSERTto the second table; i.e. in the second table you tellit the value. Note that since @@ERRORand SCOPE_IDENTITY()are floating values, you should query them both directly after the first INSERT:
这里正确的做法是SCOPE_IDENTITY()在第一次插入后查询，并INSERT在第二张表中使用；即在第二个表中你告诉它的价值。请注意，由于@@ERROR和SCOPE_IDENTITY()是浮动值，您应该在第一个之后直接查询它们INSERT：
SELECT @Error = @@ERROR, @NewId = SCOPE_IDENTITY()
回答by richard
You need to capture the auto-incremented value that you get when you insert into the first table, tbl_user_login. After you capture it, you need to use it to insert into the second table.
您需要捕获在插入第一个表 tbl_user_login 时获得的自动递增值。捕获后，您需要使用它插入到第二个表中。
DECLARE @ID int
BEGIN TRANSACTION
INSERT INTO tbl_user_login VALUES (@UserID, @Pass, @Enabled, @Permission, @Rank)
SET @ID = SCOPE_IDENTITY()
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
INSERT INTO tbl_user_profile VALUES (@ID, @FName, @LName, @Phone, @Email1, @Email2)
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
COMMIT
回答by TechGiant
Create Table tbl_user_login
(
**ID INT Identity(1,1),**
UserID varchar10,
Pass varchar50 ,
Enabled int,
Permission int ,
Rank int
);
Make ID column as Identity(1,1) that will be auto incremented column and that will solve your problem. Make it in both tables.
将 ID 列设为 Identity(1,1) ，这将是自动递增的列，这将解决您的问题。把它放在两个表中。
