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

Question

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

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

I will provide an answer for you, but let me first clarify:

The reasons you have chosen RetailName instead of some ID are wrong, and you probably will pay dearly in the future for that choice.

But people need to learn the hard way sometimes, so here goes:

Your thoughts (apart from the earlier points) are sound. No need for an instead-of trigger, though; an AFTER trigger will work fine. Adding a field to connect them seems sound, let's call them [MyRetailsGroup] and populate it:

alter table [MyRetails] add [MyRetailsGroup] nvarchar(100) -- or whatever datatype [RetailName] is
update MyRetails set [MyRetailsGroup]=[MyRetails]

The trigger sould look something like this:

create or alter trigger TR_MyRetails_update_PK on [MyRetails]
after update as
begin
    if update([RetailName]) -- only run for primary key change
    begin
    
        -- create new rows for previous PK
        insert [MyRetails]([RetailName],[Description],[MyRetailsGroup])
            select 
                d.[RetailName],d.[Description],d.[MyRetailsGroup]
            from deleted d
                
        -- save previous and next PK to temp table
        select d.[RetailName] as [original],i.[RetailName] as [new]
        into #mr
        from
            inserted i
            inner join deleted d on i.[RetailName]=d.[RetailName]
            
        -- now do your business stuff with those pairs
        update sales set [RetailName]=mr.[new]
        from
            [Sales] sales
            inner join #mr mr on mr.[original]=sales.[RetailName]
        where sales.[Date] >= DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0) -- this month onward
        
        ......
    end
end