April 10, 2014 at 2:10 pm
I am not a dba, more of a front end guy. My employer has made what I think is a mistaken decision to change a key value. In this case a driver number. The table being changed has a trigger on it that begins like this:
USE [LimoTSNew]
GO
/****** Object: Trigger [dbo].[Driver_summary_UTrig] Script Date: 04/10/2014 13:04:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Driver_summary_UTrig] ON [dbo].[Driver_summary] FOR UPDATE AS
SET NOCOUNT ON
/* * CASCADE UPDATES TO 'Daily_car_detail' */
IF UPDATE(Company) OR UPDATE(Dor_date) OR UPDATE(Driver_no)
BEGIN
UPDATE Daily_car_detail
SET Daily_car_detail.Company = inserted.Company , Daily_car_detail.Dor_date = inserted.Dor_date , Daily_car_detail.Driver_no = inserted.Driver_no
FROM Daily_car_detail, deleted, inserted
WHERE deleted.Company = Daily_car_detail.Company AND deleted.Dor_date = Daily_car_detail.Dor_date AND deleted.Driver_no = Daily_car_detail.Driver_no
END
If I change a single value in the table, the related table updates as expected. If I run an update query specifying a single record, the related table updates. If I run update query to update the entire table, I get:
Msg 2627, Level 14, State 1, Procedure Driver_summary_UTrig, Line 6
Violation of PRIMARY KEY constraint 'aaaaaDaily_car_detail_PK'. Cannot insert duplicate key in object 'dbo.Daily_car_detail'.
The statement has been terminated.
My gut is that the trigger isn't written correctly, but I don't know enough to be sure or change it. There are 4 other tables after the one above, but they all have the same structure.
April 10, 2014 at 3:33 pm
Not actually a big mistake.
In the where clause, the Daily_car_detail table is joined to the deleted table, but the inserted table isn't joined, so you get a cross join with that.
Probably what you need to do is instead:
UPDATE Daily_car_detail
SET Daily_car_detail.Company = inserted.Company , Daily_car_detail.Dor_date = inserted.Dor_date , Daily_car_detail.Driver_no = inserted.Driver_no
FROM Daily_car_detail
INNER JOIN deleted ON deleted.Company = Daily_car_detail.Company AND deleted.Dor_date = Daily_car_detail.Dor_date AND deleted.Driver_no = Daily_car_detail.Driver_no
INNER JOIN inserted ON inserted.Company = Daily_car_detail.Company AND inserted.Dor_date = Daily_car_detail.Dor_date AND inserted.Driver_no = Daily_car_detail.Driver_no
END
That's assuming that the three columns there are the unique key of the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2014 at 3:48 pm
Thanks for replying Gail (and greetings from an Access MVP). I thought it looked odd that there were no joins. The car table has an additional field as part of the key, a car number (a driver can take out more than one vehicle a day). Can I assume that would have to be added as a criteria, like:
WHERE deleted.Car_no = Daily_car_detail.Car_no
April 10, 2014 at 4:13 pm
I have to confess that I'm a bit confused because I can't see how this could possibly work.
The way I read it, you're cascading the changes to the key {Company, Dor_Date, Driver_No}, but you're joining both inserted and deleted tables on the {Company, Dor_Date, Driver_No}. If the PK are changed, then the record between the deleted and inserted couldn't be joined at all because the deleted table would have the old value and the inserted would have the new value but no way to match the same row between two tables.
Wouldn't it be more simpler to implement CASCADE UPDATE on the foreign key constraints and not worry about writing the triggers?
April 10, 2014 at 4:27 pm
What you say makes sense, but I'm not sure what you're suggesting as the alternative. The trigger has been in place a long time, but I could certainly disable it temporarily. I tried disabling the trigger and updating the child tables first, but of course ran into a referential integrity error. I wonder if I could do it in reverse, update the parent table then the children?
Is this Banana the Access MVP? If so, howdy my friend!
April 10, 2014 at 5:20 pm
As an update, and because I'm up against a time problem, I experimented with taking the trigger out temporarily, then running the update query against the parent table first and then against the child tables, and it seemed to work fine (I tested against a single date long past). I've updated the parent table and an update of the second child table is running as I type, so I think crisis averted.
I would still be interested in correcting the trigger though. Thanks to both of you for your help!
April 10, 2014 at 5:23 pm
pbaldy, yes the same banana. 🙂
Just for clarity, what was the query that you used to update manually the parent and then the child?
April 10, 2014 at 5:40 pm
Hi "B"! The same SQL for both other than the table name:
UPDATE Driver_summary
SET Driver_no = Replace(Driver_no,' ', ' 8')
WHERE substring(Driver_no,5,1) = 2
The driver number originates in a commercial HR/Payroll program that for some stupid reason pads the number with spaces. Basically the powers that be decided that they wanted to add an 8 in front of existing numbers, so driver 22222 becomes 822222. Our convention uses different ranges for different companies, and the company being changed started with 2, hence the criteria. Because they updated the payroll system, I had to update the data in this program or joins wouldn't work anymore.
April 10, 2014 at 6:12 pm
Okay.
So in for this specific situation, you are only updating only the Driver_No; the Company and Date_Dor fields hasn't changed.
Your query works with the trigger disabled because you could apply the same logic (e.g. "Replace(Driver_no,' ', ' 8')" and "substring(Driver_no,5,1) = 2".
On the trigger, it wouldn't be privy to this information. Deleted & Inserted tables would look like this:
deleted.Company = 'ABC'
deleted.Date_Dor = '2014-04-11'
deleted.Driver_No = ' 821'
inserted.Company = 'ABC'
inserted.Date_Dor = '2014-04-11'
inserted.Driver_No = ' 221'
When there's one row, there's no ambiguity, but if you have more than one row.. lets say the second row looks like:
deleted.Company = 'ABC'
deleted.Date_Dor = '2014-04-11'
deleted.Driver_No = ' 831'
inserted.Company = 'ABC'
inserted.Date_Dor = '2014-04-11'
inserted.Driver_No = ' 231'
Given that the {Company, Date_Dor, Driver_No} is the primary key of the parent table, and Driver_No has been changed, how do we know which row of deleted should match up to the row of inserted? The trigger doesn't know about the logic from the query -- it is only handed a bunch of rows in inserted and deleted but with no link between two tables; we can't be sure that ' 821' from deleted should be matched up to ' 221' in inserted and not to ' 231'.
If you are stuck with the schema and you can't change it but you can create view & trigger, it may make sense to use a view like so:
CREATE VIEW dbo.vwDriver_Summary AS
SELECT
ROW_NUMBER() OVER (ORDER BY Company, Date_Dor, Driver_No) AS TmpID,
Company,
Date_Dor,
Driver_No
FROM dbo.Driver_Summary;
You can then write a trigger on the view:
CREATE TRIGGER dbo.trg_vwDriver_Summary_Update
ON dbo.vwDriver_Summary INSTEAD OF UPDATE AS
UPDATE s
SET s.Company = i.Company
s.Date_Dor = i.Date_Dor
s.Driver_No = i.Driver_No
FROM dbo.Driver_Summary AS s
INNER JOIN deleted AS d
ON s.Company = d.Company
AND s.Date_Dor = d.Date_Dor
AND s.Driver_No = d.Driver_No
INNER JOIN inserted AS i
ON d.tmpID = i.tmpID;
But really, it's probably simpler to just add a surrogate key to the table, make it the PK of the table, then make your original compound PK a UNIQUE index and ensuring that each column in that new UNIQUE index are non-nullable to ensure that it's still equivalent to a PK even though it's no longer a PK.
I hope that helps?
April 10, 2014 at 6:33 pm
Thanks! I'll have to try and process that in the morning, as my brain is fried.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply