插入两个有关系的表的存储过程? [英] Stored Procedure to Insert Two Tables with Relationship?

问题描述

我试图将一个新行插入到两个有关系的表中.我写的存储过程如下.

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

从此遵循 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();

但我收到以下错误.

INSERT 语句与外键约束FK_tbl_user_profile_tbl_user_login".数据库My数据库位置"，表dbo.tbl_user_login"，ID"列.这声明已终止.

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.

我是存储过程的新手，所以有什么建议我应该如何修复它以便我可以将数据插入到两个表中?

I'm new to Stored Procedures so any suggestions how should I fix it so that I could insert data into two tables?

表架构

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)

推荐答案

@Richard 它的ID"是两个表中的自动增量.

@Richard Its "ID" which is the Auto Increment in both tables.

将自动增量 (IDENTITY) 用作主键很好，但将其用作外键很危险，因为您无法真正保证它们始终同步;任何回滚都可能使它们损坏(回滚不会撤消标识增量，因为这会影响其他 SPID).此外，两个并发 INSERT 之间的任何线程竞争都将处于危险之中.

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.

这里正确的做法是在第一次插入后查询SCOPE_IDENTITY()，并在INSERT中使用它到第二个表；即在第二个表中，您告诉它的值.请注意，由于 @@ERROR 和 SCOPE_IDENTITY() 是浮动值，您应该在第一个 INSERT 之后直接查询它们:

The correct approach here is to query SCOPE_IDENTITY() after the first insert, and use that in the INSERT to the second table; i.e. in the second table you tell it the value. Note that since @@ERROR and SCOPE_IDENTITY() are floating values, you should query them both directly after the first INSERT:

SELECT @Error = @@ERROR, @NewId = SCOPE_IDENTITY()

这篇关于插入两个有关系的表的存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！