使用SCOPE_IDENTITY插入到多个表中

Question

我有以下SQL代码用于向我的数据库插入新记录。

DECLARE @CustomerID INT
DECLARE @PropertyID INT

BEGIN TRAN T1
    INSERT INTO c_customer (title, f_name, l_name, tel1, tel2, tel3, email, email2, 
                            type, primary_contact, tel1type, tel2type, tel3type) 
    VALUES(@title, @fname, @lname, @tel1, @tel2, @tel3, @email, @email2, 
           'Owner', 1, @teltype1, @teltype2, @teltype3)

    SET @CustomerID = SCOPE_IDENTITY()

    BEGIN TRAN T2
        INSERT INTO c_property (address1, address2, address3, post_code, city, county) 
        VALUES (@address1, @address2, @address3, @postcode, @city, @county)

        SET @PropertyID = SCOPE_IDENTITY()

        UPDATE c_property 
        SET invoice_flag = @PropertyID
        WHERE c_property = @PropertyID

        BEGIN TRAN T3

            INSERT INTO c_customer_assignment 
            VALUES (@PropertyID, @CustomerID)

            COMMIT TRAN T1
            COMMIT TRAN T2
            COMMIT TRAN T3

    SELECT @CustomerID, @PropertyID

这段代码按照我的期望运行，确保添加的详细信息使用表正确链接，但是看起来过于复杂，我想知道是否采用了正确的方法来解决问题（不确定是否需要任何嵌套事务）。

至少需要一个事务，因为确实需要确保记录不会由于其他用户同时插入而产生不匹配。

我还需要检查事务隔离吗？或者这样就足够了？

- Takarii

---

如果c_customer和c_property之间存在M-M关系，则需要这3个表。您不需要3个事务，只需要1个即可，因为任何回滚都将回滚所有3个嵌套事务，因此您不会从嵌套事务中获得任何好处。 - ughai

那我可以放弃多余的2个事务吗？这里并不涉及表计数的问题。正如你所说，由于存在多对多关系，需要使用所有3个表。 - Takarii

额外的交易并不会带来任何好处。请查看 http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/ 和 https://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx。 - ughai

@ughai 很棒，那解释得非常清楚。谢谢！ - Takarii

Answer 1

您只需要进行一次交易：

DECLARE @CustomerID INT
DECLARE @PropertyID INT

BEGIN TRAN T1

    INSERT INTO c_customer (title, f_name, l_name, tel1, tel2, tel3, email, email2, type, primary_contact, tel1type, tel2type, tel3type) 
    VALUES(@title, @fname, @lname, @tel1, @tel2, @tel3, @email, @email2, 'Owner', 1, @teltype1, @teltype2, @teltype3)

    SET @CustomerID = SCOPE_IDENTITY()

    INSERT INTO c_property (address1, address2, address3, post_code, city, county) 
    VALUES (@address1, @address2, @address3, @postcode, @city, @county)

    SET @PropertyID = SCOPE_IDENTITY()

    UPDATE c_property 
    SET invoice_flag = @PropertyID
    WHERE c_property = @PropertyID

    INSERT INTO c_customer_assignment 
    VALUES (@PropertyID, @CustomerID)

    COMMIT TRAN T1

    SELECT @CustomerID, @PropertyID

- navigator

Answer 2

使用try catch代替过多的事务。

BEGIN TRANSACTION; BEGIN TRY

---您的DML操作应在此处进行 ---

END TRY BEGIN CATCH ---在此处捕获错误并回滚操作

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH;

IF @@TRANCOUNT > 0 COMMIT TRANSACTION; GO

- Kamalakar

---

没有任何错误需要捕获。代码本身运行得很完美，但似乎有点复杂。 - Takarii

在 catch 块中使用回滚事务代替三个事务。 - Kamalakar