Trigger to update a linked server table when specific values are entered

Question

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 three code strings in the quates table status column of the CRM, the project table status of the ERP needs to be updated when one of three text strings.

CN=Converted
WO=Working
PN=Pending

CRM Quotes Table

CompanyID QuoteID Status

User enters (1 of three codes)

CN
WO
PN

ERP Project Table

CompanyID ProjectID Status

Status updated to (1 of three strings)

Converted
Working
Pending

It seems like it would be something like

UPDATE ERP.dbo.Projects
SET ERP.dbo.Projects.Status = 'Converted'
FROM Inserted.CRM.Projects
WHERE Inserted.CRM.Projects Status = 'CN'
INNER JOIN ERP.dbo.Project.ProjectID = CRM.dbo.Quotes.QuoteID

UPDATE ERP.dbo.Projects
SET ERP.dbo.Projects.Status = 'Working'
FROM Inserted.CRM.Projects
WHERE Inserted.CRM.Projects Status = 'WO'
INNER JOIN ERP.dbo.Project.ProjectID = CRM.dbo.Quotes.QuoteID

UPDATE ERP.dbo.Projects
SET ERP.dbo.Projects.Status = 'Pending'
FROM Inserted.CRM.Projects
WHERE Inserted.CRM.Projects Status = PN
INNER JOIN ERP.dbo.Project.ProjectID = CRM.dbo.Quotes.QuoteID

Would it be best to use a case or if statement or use 3 separate triggers for each code?

Database: SQL Server 2008 R2

Answer 1

Not a lot of detail here but you have some logical flaws in your updates as posted. I suspect you want something more like this.

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