Avoid trigger inserting duplicate records

  • Hi
    I have created a trigger when a value is inserted to the main table dbo.MVF_Transport_Register, it updtes the dbo.MVF_Transport_Acc table with that record. Also when a value is changed on the main table dbo.MVF_Transport_Register it updates the particular field value accordingly on the other dbo.MVF_Transport_Acc.. Problem I have is my trigger inserts duplicte record on to the dbo.MVF_Transport_Acc.
    Just want to check how can I amend this trigger so it checks dbo.MVF_Transport_Acc to see if the record exsists before inserting the record. If the record exsists it shouldnt insert duplicate records. Can some one pelase check my trigger an advice how to sort this thanks in advance big help
    Primary key is Order_No for both tables

    ALTER TRIGGER [dbo].[UpdateTransport]
     ON [dbo].[MVF_Transport_Register]
     AFTER UPDATE
    AS
    BEGIN
    UPDATE c SET c.Record_Status = i.Record_status
      FROM MVF_SYSTEMS.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 MVF_SYSTEMS.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

  • What is the primary key on table MVF_Transport_Acc?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi ,
    I have  added the quotes as you suggested, I havent set a primary key on the second table (dbo.MVF_Transport_Acc) this would be the reason why it is inserting duplicate records, I will try this
    thanks for your reply

  • I'd be willing to bet this is the problem.

    AND c.Record_Status <> i.Record_Status 

    Anytime the record gets updated but the status doesn't change the update will not run since the status in the second table will already match, so that row count will be zero and it'll run the insert.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply