Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

2 errors: The multi-part identifier "inserted.name" could not be bound

I'm trying to create a trigger to update a contact's info in a repository when it's been changed.

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

I'm quite new at this, and I got this errors:

The multi-part identifier "INSERTED.name" could not be bound.
The multi-part identifier "INSERTED.mail" could not be bound.
like image 668
Luis M. Condor Avatar asked Jan 07 '13 19:01

Luis M. Condor


1 Answers

You're missing the FROM Inserted in your UPDATE statement - try this:

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

Also - once you include that pseudo table, you should somehow reference it / join it to something else....

Update: well, you might want to add some extra WHERE clause if you add the Inserted pseudo table into the equation - what that is exactly is wholly dependent on your requirements, which I don't know - but it could be something like

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

or something like that (to avoid a cartesian product in your UPDATE statement).

like image 178
marc_s Avatar answered Sep 23 '22 11:09

marc_s