SQL Server trigger to update row with linked table

Question

I'm using SQL Server. I have three tables, Contacts, Companies and Links.

When a Contact is inserted, I want to update columns with information from the corresponding Company. There is a Link table with a ToID corresponding to the ContactID and a FromID corresponding to the CompanyID.

Here is the query I'm trying to use, but I'm not familiar enough with SQL to get it to work.

CREATE TRIGGER dbo.Contacts.AddressFromCompany
ON dbo.Contacts
AFTER INSERT AS
BEGIN
    SET NOCOUNT ON

    IF inserted.Address1 IS NULL AND 
       inserted.Address2 IS NULL AND 
       inserted.Address3 IS NULL AND 
       inserted.City IS NULL AND 
       inserted.State IS NULL AND 
       inserted.ZipCode IS NULL AND 
       inserted.Country IS NULL

        UPDATE cont 
        SET Address1 = comp.Address1,
            Address2 = comp.Address2,
            Address3 = comp.Address3,
            City     = comp.City,
            State    = comp.State,
            ZipCode  = comp.ZipCode,
            Country  = comp.Country
        FROM dbo.Companies comp
        WHERE 
            comp.CompanyID = dbo.Links.ToID 
            AND dbo.Links.FromID = inserted.ContactID;
 END

Thanks for any help!

Kyle

inserted can contain 0, 1, or multiple rows. So an IF check is misguided at best - what if some of the rows match your conditions and some of them do not? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 13, 2014 at 6:46
You might also want to reconsider your design. Why does the contact hold a copy of the company information? What happens if a contact is linked to multiple companies? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 13, 2014 at 7:04

adrianm · Accepted Answer · 2014-06-13 06:31:24Z

1

Don't understand how Links can contain a ContactId before the contact is created but this code does what you ask for. (assuming ContactID is unique).

UPDATE Contacts
SET Address1 = Companies.Address1
   ,Address2 = Companies.Address2
   ,Address3 = Companies.Address3
   ,City     = Companies.City
   ,State    = Companies.State
   ,ZipCode  = Companies.ZipCode
   ,Country  = Companies.Country
FROM Contacts
     INNER JOIN Links
        ON Links.FromID = Contacts.ContactsID
     INNER JOIN Companies
        ON Companies.CompanyID = Links.ToID
     INNER JOIN inserted
        ON inserted.ContactId = Contacts.ContactID
WHERE inserted.Address1 IS NULL 
      AND inserted.Address2 IS NULL 
      AND inserted.Address3 IS NULL 
      AND inserted.City IS NULL 
      AND inserted.State IS NULL 
      AND inserted.ZipCode IS NULL 
      AND inserted.Country IS NULL

answered Jun 13, 2014 at 6:31

adrianm

14.8k6 gold badges60 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MrGrinst Over a year ago

Yeah I still have to figure out how to make sure the Links table is updated first. Would you have any suggestions about how to do that? Maybe put the trigger on the Links table?

Collectives™ on Stack Overflow

SQL Server trigger to update row with linked table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related