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.

need advices on database server Architecture!

i recently started working for a smaller company. they purchased a new
sql server for dev and qa purpose, a developer's box. there would be
sql2k on it for .net development. it's raid 10 hardware (very high
performance) with 2GB ram.
My question is, besides the sql server service, the network admins want
to use this physical server as a test/qa server for the followings: SQL
reporting server, OLAP (for some warehouse/ETL/cube development),
IIS/web server (for intranet), and application server for vb.net
applications.
as a DBA, should i ok that? what would be the strong reasons not to do
so (dump all the stuff on the same dev/qa box)? i've never encountered
situation like this before because i used to work for larger
organizations and the application, reporting, IIS/web servers were
always seperate physical servers.
also, where should Visual Source Safe program reside ideally? on a file
server? application server? or on a database server?
any good article links would be greatly appreicated as well.
thank you guys!!
SteveIt depends. <wink>
What does your production environment look like? Your QA server should
hopefully mimick the production environment as closely as possible.
If it just for development purposes and you are not doing any major load
testing on the system, then I don't really see a huge problem with putting
all of those programs on there. I would suggest you add another 2GB of RAM
though.
As far as SourceSafe is concerned, I would probably go with a different
source control program. There are several out there that integrate with
VS.NET and use SQL Server as the storage engine. This is especially useful
if you are doing development over a VPN from home as they use HTTP or .NET
remoting and are much faster than VSS and much more sturdy (IMO).
That's my .02 cents.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||generally your dev environments are going to be a punch of "poo"
I would be OK with that.
My opinion is that each individual developer should have entire app on their
OWN dev box. (DB Server, App Server, Web Server, the works).
The QA environment and/or staging environment needs to more closely mimic
Prod.
Greg Jackson
Portland, OR|||thank you for the quick response. yes, I do understand that QA server
should mimick the production environment as closely as possilbe. in the
case, most application development on this server will be for internal
use with limited users. in terms of ram size, i know more is better but
have to limit to 2BG because the version of our sql server we use.
in terms of VSS, what other source control programs you'd recommend?
and where should they be implemented?
thank you|||> in terms of VSS, what other source control programs you'd recommend?
> and where should they be implemented?
> thank you
>
http://www.sourcegear.com/vault
I would put it on a machine that's in your DMZ or that's somehow got HTTP
access to it so you can (if needed) access it over the internet w/ the
client (Vault client or vs.net)|||You might want to look into using vmware. You'll need to increase the RAM to
4 (or more) GB though.
Bob
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1111685730.536432.312550@.f14g2000cwb.googlegroups.com...
> i recently started working for a smaller company. they purchased a new
> sql server for dev and qa purpose, a developer's box. there would be
> sql2k on it for .net development. it's raid 10 hardware (very high
> performance) with 2GB ram.
> My question is, besides the sql server service, the network admins want
> to use this physical server as a test/qa server for the followings: SQL
> reporting server, OLAP (for some warehouse/ETL/cube development),
> IIS/web server (for intranet), and application server for vb.net
> applications.
> as a DBA, should i ok that? what would be the strong reasons not to do
> so (dump all the stuff on the same dev/qa box)? i've never encountered
> situation like this before because i used to work for larger
> organizations and the application, reporting, IIS/web servers were
> always seperate physical servers.
> also, where should Visual Source Safe program reside ideally? on a file
> server? application server? or on a database server?
> any good article links would be greatly appreicated as well.
> thank you guys!!
> Steve
>|||CVS
GAJ|||"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1111687330.998586.126860@.o13g2000cwo.googlegroups.com...
> thank you for the quick response. yes, I do understand that QA server
> should mimick the production environment as closely as possilbe. in the
> case, most application development on this server will be for internal
> use with limited users. in terms of ram size, i know more is better but
> have to limit to 2BG because the version of our sql server we use.
> in terms of VSS, what other source control programs you'd recommend?
> and where should they be implemented?
> thank you
>
I would still up the RAM. Just because it's there, doesn't mean SQL will
actually use it. I am assuming you are running standard edition of SQL.
The other 2GB that SQL isn't using would be used by the other applications.
Rick|||thanks guys, i know more is always better for the RAM. but the server
price between the 2GB ram and 4GB ram is significant. the server has
been purchased. a little late for that. but it's good to know for the
next purchase.