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

Jin Ka 26 Reputation points
2022-07-03T19:52:43.047+00:00

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

217137-mybusiness3.jpg

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,236 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 98,636 Reputation points
    2022-07-03T20:55:16.047+00:00

    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.

    1 person found this answer helpful.

  2. Tom Phillips 17,706 Reputation points
    2022-07-05T16:45:15.92+00:00

    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).

    1 person found this answer helpful.
    0 comments No comments

  3. Naomi 7,361 Reputation points
    2022-07-03T20:33:09.953+00:00

    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.