If Exists Update Else Insert Trigger question

[bustercoder]

Hello,

However it happens, either by Update or Insert, when a Back Order is transferred to an Order, an SOPNUMBE of soptype = 2 (order) is being inserted into the SOP10106 (user defined table) as a new record, or the Back Order is updating to an Order retaining the comments, as does an Order when it is transferred to an Invoice. With this in mind, we should add a check to the trigger which checks to see if there is an existing sopnumbe of soptype = 2 which matches what is in the inserted table. If there is, do an update instead of an insert. However, I am not sure exactly how the syntax would go in a trigger for the if...else logic. Also, I wasn't sure about the "set PYMTRMID = (Select PYMTRMID from inserted)", code either. Here's what I have so far:

CREATE TRIGGER [dbo].[trig_updateTermsCopy] ON [dbo].[SOP10100]
FOR INSERT, UPDATE, DELETE
AS
begin
SET NOCOUNT ON

if exists (select * from TEST.dbo.SOP10106 m inner join inserted i on (m.sopnumbe = i.sopnumbe) where i.soptype = 2)
begin
update TEST.dbo.SOP10106 set PYMTRMID = (Select PYMTRMID from inserted), USERDEF1 = 'TEST SEND PENDING'
end

if exists (select * from deleted)
begin
delete TEST.dbo.SOP10106
from TEST.dbo.SOP10106 utc inner join
deleted on (utc.SOPNUMBE = deleted.SOPNUMBE) where deleted.soptype = 2
end

if exists (select * from inserted)
begin
insert TEST.dbo.SOP10106
select SOPTYPE, SOPNUMBE, '', '', PYMTRMID, '', '', 'TEST SEND PENDING', '', '', '', '', '', '', '', '', COMMNTID
from inserted where inserted.soptype = 2
end
end
Thanks,
Buster