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

我正在尝试创建一个触发器，以在更改存储库中的联系人信息时对其进行更新。

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.

最佳答案

您在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语句中使用笛卡尔积)。