0

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
3
  • Does IF (i.Order_No=c.Order_No ) even work here? And JOIN deleted as d ON i.Order_No=d.Order_No OR (i.Record_Status <> d.Record_Status) cannot be right, surely. Feb 21 '18 at 16:27
  • i and c do not mean anything in your IF statement. i and c are also not single values Feb 21 '18 at 16:52
  • 1
    You have too many END and IF keywords at the end. Change ... END IF END to ... END
    – Oreo
    Feb 21 '18 at 16:56
2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.