Need assistance on conditional update Trigger

I need some help here in creating a conditional update trigger. The purpose of this trigger would check to see if a contact already exist in the database on an insert and update only the fields that are null.

So How would I compare each field from the CONTACTS Table against my INSERTED Table?

Inserted.FirstName (COMPARE) Contacts.Firstname

Inserted.LastName (COMPARE) Contacts.LastName

Inserted.Email (COMPARE) Contacts.Email

I will be using the email address as the check for the duplicate record and if a duplicate is found... Instead of not allowing the insert I want to compare the existing record and update any fields that are NULL in Contacts with Inserted.

I have no idea on how to compare all of the fields.

Any help appreciated.

sadler_david@.yahoo.com

You could do the following in an INSTEAD OF trigger:

create trigger merge_contact

instead of insert

on dbo.Contacts

as

begin

update dbo.Contacts

set FirstName = coalesce(FirstName, i.FirstName),

LastName = coalesce(LastName, i.LastName)

from inserted as i

where i.Email = dbo.Contacts.Email

insert into dbo.Contacts (FirstName, LastName, Email)

select i.FirstName, i.LastName, i.Email

from inserted as i

where not exists(select * from dbo.Contacts as c

where c.Email = i.Email)

end

Ideally, you need to perform the operations in serializable transaction isolation level to avoid duplicate inserts.