SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[UpdateTransport_TEST1]
   ON  [dbo].[MVF_Transport_Register]
   AFTER UPDATE
AS
IF UPDATE (Record_Status)

BEGIN
IF (i.Order_No=c.Order_No )
UPDATE c
set Record_Status=i.Record_Status
FROM dbo.MVF_Transport_Acc AS c
JOIN inserted as i
ON i.Order_No = c.Order_No
JOIN deleted as d
ON i.Order_No=d.Order_No
OR (i.Record_Status <> d.Record_Status)
WHERE  (i.Record_Status = 'CONFIRMED' OR
       i.Record_Status = 'AMENDED') AND (i.Price > 0)
ELSE

INSERT into dbo.MVF_Transport_Acc
([Order_No], [Record_Status], [Notes], [Transport_Supplier], [Surcharge], [Vendor_No], [Pallets], [Amend_Pallets],[Price], [Input_Date], [Amend_Price], [Transport_Invoice_No],[Transport_Job_No], [Amend_Date], [Report_Price],
                         [Return_Price], [Report_Date])

SELECT  i.[Order_No], i.[Record_Status], i.[Notes], i.[Transport_Supplier], i.[Surcharge], i.[Vendor_No], i.[Pallets], i.[Amend_Pallets],i.[Price], i.[Input_Date], i.[Amend_Price], i.[Transport_Invoice_No],i.[Transport_Job_No], i.[Amend_Date], i.[Report_Price],
                         i.[Return_Price], i.[Report_Date]

        FROM   [dbo].[MVF_Transport_Register] c
        INNER JOIN inserted i on
       c.Order_No = i.Order_No
WHERE  (i.Record_Status = 'CONFIRMED' OR
       i.Record_Status = 'AMENDED') AND (i.Price > 0)

END IF


END

...
END IF

END

...
END