Update trigger issue throwing errors on IF statements

Question

I have created a trigger, basically what I want it to do is update or insert a record to another table.

Two table I have got:

• MVF_Transport_Register: Main table
• MVF_Transport_Acc: table trigger need to update.

When a record is updated on the Main table, I want to the trigger to check if that records exists on the MVF_Transport_Acc.

• If it does only update the Record_status column.
• If the record is not in MVF_Transport_Acc then insert the new record.

This is what I have written but I am getting the following error when trying to deploy it:

Msg 156, Level 15, State 1, Procedure UpdateTransport_TEST1, Line 36

Incorrect syntax near the keyword 'END'.

Here's the trigger:

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

Answer 1

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 <> i.Record_Status
    WHERE i.Record_Status IN ('CONFIRMED', 'AMENDED')
      AND i.Price > 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 > 0;
  END
END
GO

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