Update trigger issue throwing errors on IF statements
This logic seems wrong to me, but I think you have it way more complicated than it needs to be:

ALTER TRIGGER [dbo].[UpdateTransport_TEST1]
   ON  [dbo].[MVF_Transport_Register]
   AFTER UPDATE
AS
BEGIN
  UPDATE c 
    SET c.Record_Status = i.Record_status
    FROM dbo.MVF_Transport_Acc AS c
    INNER JOIN inserted AS i
    ON c.Order_No = i.Order_No
    AND c.Record_Status &lt;&gt; i.Record_Status
    WHERE i.Record_Status IN ('CONFIRMED', 'AMENDED')
      AND i.Price &gt; 0;

  IF @@ROWCOUNT = 0
  BEGIN
    INSERT 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 inserted AS i
    WHERE i.Record_Status IN ('CONFIRMED', 'AMENDED')
      AND i.Price &gt; 0;
  END
END
GO


I still feel like that logic is incomplete for some reason.