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.