sql-server - 如何在一个查询中更新 2 个表

Question

我有两张 table :

Login 有 4 个属性:

 1. NRIC
 2. NAME
 3. PASSWORD
 4. TYPE (USER / ADMIN)

DETAILS 有 4 个属性:

 1. NRIC
 2. NAME
 3. PASSWORD
 4. EMAIL

如果表 DETAILS 已更新，我希望表 LOGIN 被更新。

例如，如果表 DETAILS 中的 password 已更新，则假定表 LOGIN 也应更新。

SQLCmd.CommandText = "UPDATE DETAILS set PASSWORD='" & TextBoxPassword.Text & "',EMAIL='" & TextBoxEmail.Text & "' WHERE NRIC='" & myVar2 & "'"

如何同时更新它们？

Answer 1

为此使用SqlTransaction，以下是引用代码:

C#:

    SqlConnection conn = new SqlConnection("[Your connection string]");
    conn.Open();
    SqlTransaction Trans = conn.BeginTransaction();
    SqlCommand SQLCmd = new SqlCommand("",conn);
    SQLCmd.Transaction = Trans;
    try
    {
        SQLCmd.CommandText = "UPDATE DETAILS set PASSWORD='" & TextBoxPassword.Text & "',EMAIL='" & TextBoxEmail.Text & "' WHERE NRIC='" & myVar2 & "'";
        SQLCmd.ExecuteNonQuery();
        SQLCmd.CommandText = "UPDATE Login  set PASSWORD='" & TextBoxPassword.Text & "' WHERE NRIC='" & myVar2 & "'";
        SQLCmd.ExecuteNonQuery();
        Trans.Commit();

    }
    catch
    {
        Trans.Rollback();
    }
    finally {
        conn.Close();

    } 

VB:

Dim conn As New SqlConnection("[Your connection string]")
conn.Open()
Dim Trans As SqlTransaction = conn.BeginTransaction()
Dim SQLCmd As New SqlCommand("", conn)
SQLCmd.Transaction = Trans
Try
  SQLCmd.CommandText = "UPDATE DETAILS set PASSWORD='" And TextBoxPassword.Text And "',EMAIL='" And TextBoxEmail.Text And "' WHERE NRIC='" And myVar2 And "'"
  SQLCmd.ExecuteNonQuery()
  SQLCmd.CommandText = "UPDATE Login  set PASSWORD='" And TextBoxPassword.Text And "' WHERE NRIC='" And myVar2 And "'"
  SQLCmd.ExecuteNonQuery()

  Trans.Commit()
Catch
  Trans.Rollback()
Finally

  conn.Close()
End Try