December 5, 2011 at 9:47 am
We use cascading updates for changes to the Primary Key of an important table in our system and they work very well. However, there are two generic tables that are not specific to that table that we also need to keep up to date when we change the Primary Key. I would like to write a trigger that will see the change of the Primary Key in the main table and update the other two tables. I have it working for one at a time changes, but not for multiple row changes:
CREATE TRIGGER dbo.trgCustomer_Upd
ON dbo.Customer
AFTER UPDATE
AS
BEGIN
IF UPDATE(CustomerID)
BEGIN
DECLARE @OldCustomerID AS VARCHAR(6) = (SELECT CustomerID FROM Deleted);
DECLARE @NewCustomerID AS VARCHAR(6) = (SELECT CustomerID FROM Inserted);
IF @OldCustomerID != @NewCustomerID
BEGIN
UPDATE Contact
SET TableKey = @NewCustomerID
WHERE TableName = 'Customer'
AND TableKey = @OldCustomerID;
UPDATE Address
SET TableKey = @NewCustomerID
WHERE TableName = 'Customer'
AND TableKey = @OldCustomerID;
END;
END;
END;
GO
My concern is this: How do I link the two pseudo tables Inserted and Deleted when checking for changes? If I make changes to two or more records in one UPDATE statement, then Inserted and Deleted will have that number of rows. Normally, I would link the two tables with the Primary Key (CustomerID) except, this time, the Primary Key is one of the fields being changed. Can I count on row 1 in Inserted being the same record as row 1 in Deleted? If not, what do I do?
Before everyone points out that we should not be changing the Primary Key of a table, I know that but I work on a team and I was not in charge of that decision.
December 5, 2011 at 10:45 am
I think the issue you are running into is one of the main reasons that changing key values is a horrible idea. I understand it is something outside of your control but this is nearly impossible. In order to pull off what you are trying to do there has to be a way to tie a given row in inserted to a given row in deleted. This is usually done with the primary key but that is the very value that you are changing. Essentially you need a way to tie the two records together. Is there another way you can uniquely identify rows?
Can you change the table a little and add an OldCustID column? Then update that to the current values. This would let you tie them back together during a trigger by joining on the new column. Then after you update the primary key you would just set the OldCustID = CustID column again. This is pretty kludgey but might get you around the issue at hand.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2011 at 10:54 am
ted_oconnor (12/5/2011)
My concern is this: How do I link the two pseudo tables Inserted and Deleted when checking for changes?
You can't. This is why you need to make an alternate key and keep a permanent surrogate key when these are changing. Might I recommend a local identity column instead of using a business attribute as your PK, or at least as a secondary identifier for the rows for internal use only?
Can I count on row 1 in Inserted being the same record as row 1 in Deleted?
No.
If not, what do I do?
Suffer volumes of pain? Sorry, bad joke. You must generate a workaround. A workaround would be a permanent way to identify any particular row. This is one of the reasons non-user associated, seen, or used surrogate keys have become so popular.
Before everyone points out that we should not be changing the Primary Key of a table
Well, that's iffy. You can, but you need an alternate key, particularly if you do auditing. You need to know how and when someone mucked with the 'key'.
I know that but I work on a team and I was not in charge of that decision.
It happens, no worries, we've all been there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 5, 2011 at 1:20 pm
Thank you for everyone's very quick response. I'm going to go with adding a new column and call it OldCustomerID.
As a side note, it that a Sporting KC logo I see? How did they do this year?
December 5, 2011 at 1:26 pm
ted_oconnor (12/5/2011)
Thank you for everyone's very quick response. I'm going to go with adding a new column and call it OldCustomerID.As a side note, it that a Sporting KC logo I see? How did they do this year?
Yes in fact it is. 😛 Top of the table in the Eastern conference. Made to the quarterfinals in the playoffs. Pretty good start to the new stadium!!! Should be interesting to see what happens next year, lots of personnel changes during the draft.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2011 at 1:27 pm
I'm still wondering how to implement this myself...if the Pk is a foreign key in the child table, won't the FK prevent you from changing the key to a new value?
Lowell
December 5, 2011 at 1:31 pm
Lowell (12/5/2011)
I'm still wondering how to implement this myself...if the Pk is a foreign key in the child table, won't the FK prevent you from changing the key to a new value?
I think they are only logical foreign keys, not enforced in the db. So you just have to have both columns so you can get them linked up.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2011 at 1:36 pm
Lowell (12/5/2011)
I'm still wondering how to implement this myself...if the Pk is a foreign key in the child table, won't the FK prevent you from changing the key to a new value?
Nah... but it depends on how you architect. I use RI only when absolutely required. Under most circumstances you can't trace the hierarchies in my DBs via FKs. I'm quite used to something like this, but then I really only ever use surrogate keys, too, even when a business key is perfectly viable. Anything the user can change I never want as my PK. Too much hassle.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 5, 2011 at 2:09 pm
Lowell (12/5/2011)
I'm still wondering how to implement this myself...if the Pk is a foreign key in the child table, won't the FK prevent you from changing the key to a new value?
In our case, we do use referential integrity, but the two tables I am referring to are generic tables that cannot have a foreign key relationship with the main table
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply