February 27, 2018 at 9:15 am
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
February 27, 2018 at 9:25 am
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.
February 27, 2018 at 9:38 am
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
February 27, 2018 at 9:39 am
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