Can I do a Partial Cascade Update on a Foreign Key Field Using INSTEAD OF UPDATE Trigger?

Question

Here Is a photo description Of My Example and down the Photo is My Question:

The Challenge is that, when I change the name of One of My Retail Shop I want for the Sales Table:

1. RetailName to stay the Deleted name (I mean the Changed Name) for records with previous months Sales Date and RetailName to be the New Inserted Name for Records from this Month Onward. The reason for doing this is that when I will be searching Sales Table in the future I will be doing so by the Name of the Retail at that time not the new name and also for some other reasons.

I thought of Using an INSTEAD OF UPDATE trigger on table MyRetails to Duplicate the Record and change RetailName value on the duplicated record to the New RetailName instead of Updating when a user tries to Update it then using the same trigger to Loop through Sales table so as to change the foreign Key RetailName to the newly created One for those records whose Sale Date = This Month, But unfortunately I was Unable to write the T-SQL for that . . . Also to relate the 2 Names for the One Retail and not to be confused as two Retail Shops I thought of adding a field which will contain values that will categorize the 2 Records together.

Can Somebody help to tackle this problem?

NB: Don't wonder why I Used RetailName As PK instead of RetailID, it was meant to be like that for some reasons.

Answer 1

No, you cannot do that with the data model you've chosen / been asked to use.

Let's say that you have a row in MyRetail with the name Jack's Parts. In April, this company changes the to Jill's parts. You want the Sales up to March to keep the name Jim's Parts, while the Sales for April should be updated.

This is not possible, since you have set up a foreign-key constraint from Sales to MyRetail. The gist of the FK constraint is that the value in the child table must exist in the parent table. But since Jack's Parts is changed to Jill's Parts, the constraint is violated if you keep the old values.

So if you want to have it that way - and it is certainly a requirement that makes perfect sense - you will have to sacrifice the FK constraint. If you do that, you can easily write this trigger:

   CREATE TRIGGER MyRetailTri On MyRetail AFTER UPDATE AS  
     
   IF (SELECT COUNT(*) FROM inserted) > 1  
   BEGIN  
      ROLLBACK TRANSACTION  
      RAISERROR('You can only update on retail at time!', 16, 1)  
      RETURN  
   END  
     
   UPDATE Sales  
   SET     RetailName = (SELECT i.RetailName FROM inserted i)  
   FROM  Sales S  
   JOIN deleted d ON S.RetailName = d.RetailName  
   WHERE S.Date => convert(char6), sysdatetime(), 112) + '01'  

The check on single-row update is needed, since it is impossible to correlated the inserted and deleted tables when you change the PK value.

Answer 2

You will need to redesign your table MyRetail. The simplest thing would be to add "effective date" start/end to the name. However, that will make all your queries much slower.

In this case, I would normally create a new table MyRetailHistory which contains the previous names with the effective date ranges. Then when you want the current name, you query MyRetail. When you want the previous name, you query MyRetailHistory with the appropriate date range. You can populate MyRetailHistory with an update after trigger (not update instead).

Answer 3

How do you tie up original name with the new name in the MyRetails table in the absense of the any other surrogate PK or column to use? E.g. I don't see a way to connect Deleted with Inserted table for your MyRetails table. The trigger will only work assuming you change one row at a time, so we may code with that limitation in mind, e.g.

CREATE OR ALTER TRIGGER trUpdateRetails ON MyRetails AFTER UPDATE
AS

IF (select count(*) from inserted) <> 1
begin
-- here you may want to raise an error as we can only allow single row updates
return;
end
declare @oldName varchar(30) = (select RetailName from Deleted);
declare @NewName varchar(30) = (select RetailName from Inserted);
declare @StartOfTheMonth date = dateadd(day,1,dateadd(month,-1,EOMONTH(CURRENT_TIMESTAMP)));
update Sales set RetailName = @NewName where [Date] > = @StartOfTheMonth and RetailName = @oldName;

--------------------------------------------------------------------------------

This is from the top of my head - not tested, so you may want to adjust.

Also, obviously you would not be able to use foreign key constraint, the key will be 'implied', but you should sacrifice the FK requirement to achive what you're trying to achieve.