The best way of dealing with null values is not to have them.

Try :
--following trigger will give you an idea how you can implement your
requirements using trigger.
alter trigger tr_transtype on TransactionsTable
for insert,update
as
if @@rowcount=0
return

if exists(select 1 from deleted)
begin
if update(value1)
update b
set iname=a.iname,
value1 = b.value1 + isnull(a.value1,0)
from mastertable b, inserted a where a.contractid = b.contractid
--updating existing row into mastertable if contractid is new.

if update(value2)
update b
set iname=a.iname,
value2 = b.value2 + isnull(a.value2,0)
from mastertable b, inserted a where a.contractid = b.contractid
--updating existing row into mastertable if contractid is new.

if update(value3)
update b
set iname=a.iname,
value2 = b.value3 + isnull(a.value3,0)
from mastertable b, inserted a where a.contractid = b.contractid
--updating existing row into mastertable if contractid is new.

return
end

if exists(select 1 from inserted)
begin
insert into [MasterTable] (contractid,iname,value1,value2,value3)
select contractid,iname,value1,value2,value3 from inserted
where contractid not in(select contractid from [dbo].[MasterTable] )
--inserting new row into mastertable if contractid is new.

end

--
-Vishal
unemotionalhumanoid <unemotionalhumanoid (AT) yahoo (DOT) com> wrote