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
- If it does only update the
- If the record is not in
MVF_Transport_Accthen 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