Trigger to update a linked server name record when specific values are enterred in status

Question

Database Sql server 2008R

I'm synchronizing two separate database applications. The databases are linked servers. One is a CRM and one is an ERP. When the user enters one of two code strings in the Names table Status column of the CRM, the Names table record of the ERP needs to be updated with the name record from the CRM.

VN=Vendor CO=Contact

CRM

+-------+-------+-----------+--------+-----------+------------+----------+--------+
| Names | Table | ContactID | TypeID | FirstName | MiddleName | LastName | Status |
+-------+-------+-----------+--------+-----------+------------+----------+--------+

User enters (1 of 2 codes) VN, CO in Status

ERP

+-------+-------+-----------+--------+-----------+------------+----------+
| Names | Table | ContactID | TypeID | FirstName | MiddleName | LastName |
+-------+-------+-----------+--------+-----------+------------+----------+

The name record from the CRM needs to update the name record of the ERP

It seems like it would be something like:

UPDATE n
    case i.Status
        when 'WO' then SET ERP.FirstName = i.FirstName, ERP.MiddleName = i.MiddleName, ERP.LastName = i.LastName
from inserted I
        when 'PN' then SET ERP.FirstName = i.FirstName, ERP.MiddleName = i.MiddleName, ERP.LastName = i.LastName
     end
from inserted i
INNER JOIN CRM.dbo.Names n on n.CompanyID = i.CompanyID

Answer 1

NO. This is not how a case expression works. A case expression is used to control the value of a single column. You need to update multiple columns. Taking the code I posted in your previous question on this topic you just needed to add the three new columns like this.

UPDATE p
SET Status = 
    case i.Status when 'CN' then'Converted'
        when 'WO' then 'Working'
        when 'PN' then 'Pending'
    end
    , FirstName = i.FirstName
    , MiddleName = i.MiddleName
    , LastName = i.LastName
from inserted i
INNER JOIN CRM.dbo.Projects p on p.ProjectID = i.QuoteID

--EDIT--

Based on your recent comments I thinking that maybe you want something along these lines. Notice the else predicate in the case expression has been added.

UPDATE p
SET Status = 
    case i.Status when 'CN' then'Converted'
        when 'WO' then 'Working'
        when 'PN' then 'Pending'
        else p.Status
    end
    , FirstName = i.FirstName
    , MiddleName = i.MiddleName
    , LastName = i.LastName
from inserted i
INNER JOIN CRM.dbo.Projects p on p.ProjectID = i.QuoteID