sql - 2 个错误 : The multi-part identifier "inserted.name" could not be bound

Question

我正在尝试创建一个触发器来在更改存储库中的联系人信息时更新它。

CREATE TRIGGER contacts_f_tr
ON contacts_f
AFTER UPDATE
AS
BEGIN
  ---
  ---Update repository data
  ---
  IF UPDATE (mail)
    BEGIN
      UPDATE mails
      SET contact = inserted.name, mail = inserted.mail
      WHERE mails.idcontact IN (SELECT mail FROM deleted) AND mails.tablecontact = 2
    END
END

我对此很陌生，并且遇到了以下错误:

The multi-part identifier "INSERTED.name" could not be bound.
The multi-part identifier "INSERTED.mail" could not be bound.

Answer 1

您错过了 FROM Inserted在您的 UPDATE声明 - 试试这个:

CREATE TRIGGER contacts_f_tr
ON contacts_f
AFTER UPDATE
AS
BEGIN
  ---
  ---Update repository data
  ---
  IF UPDATE (mail)
    BEGIN
      UPDATE mails
      SET contact = inserted.name, mail = inserted.mail
      FROM Inserted     <<==== add this line here!
      WHERE mails.idcontact IN (SELECT mail FROM deleted) AND mails.tablecontact = 2
    END
END

另外 - 一旦你包含了那个伪表，你应该以某种方式引用它/将它加入其他东西......

更新:好吧，您可能想添加一些额外的 WHERE条款，如果您添加 Inserted等式中的伪表 - 究竟是什么完全取决于您的要求，我不知道 - 但它可能类似于

WHERE mails.idcontact IN (SELECT mail FROM deleted) 
  AND mails.tablecontact = 2
  AND mails.MailId = Inserted.MailId

或类似的东西(避免在您的 UPDATE 语句中使用笛卡尔积)。