SQL Server 2008: Updating and Tracking Profit Growth
I have a project right now where I need to update prices for a product list. The prices are for the period from January 1, 2007 to 12/31/2011, and I need to increase these prices by 5% every year until the end of 2015.
Here's what I have. But I am fixated on updating prices (i.e. 5% more). I keep getting duplicate data error. Thanks in advance for any help / hints!
Error message:
Msg 2627, Level 14, State 1, Procedure update_history, Line 9
Violation of PRIMARY KEY constraint "PK_PriceCha_207F7DE23A81B327". Unable to insert duplicate key into "dbo.PriceChange_History" object.
Tables:
create table PriceChange
(ProductID INTEGER NOT NULL PRIMARY KEY,
StartDate DATE,
EndingDate DATE,
UnitPrice MONEY);
ALTER TABLE PriceChange ADD FOREIGN KEY (ProductID) REFERENCES PRODUCT(ProductID)
create table PriceChange_History
(History_ProductID INTEGER NOT NULL PRIMARY KEY,
History_StartDate DATE,
History_EndingDate DATE,
History_UnitPrice MONEY,
Modified_date datetime,
ChangeType varchar(20) );
ALTER TABLE PriceChange_History
ADD FOREIGN KEY (History_ProductID) REFERENCES PRODUCT(ProductID)
Triggers
create trigger [insert_history] on PriceChange
for insert
as
insert PriceChange_History (History_ProductID, History_StartDate,
History_EndingDate, History_UnitPrice,
Modified_date, ChangeType)
select
ProductID, StartDate, EndingDate, UnitPrice,
GETDATE(), 'INSERTED'
from inserted
create trigger [update_history] on PriceChange
for update
as
insert PriceChange_History(History_ProductID, History_StartDate,
History_EndingDate, History_UnitPrice,
Modified_date, ChangeType)
select
ProductID, StartDate, EndingDate, UnitPrice,
GETDATE(), 'BEFORE UPDATE'
from deleted
insert PriceChange_History(History_ProductID, History_StartDate,
History_EndingDate, History_UnitPrice,
Modified_date, ChangeType)
select
ProductID, StartDate, EndingDate, UnitPrice,
GETDATE(), 'AFTER UPDATE'
from inserted
INSERT + UPDATE
INSERT INTO PriceChange
VALUES(1,'1/1/2007', '12/31/2011', 500) <--- this will record the query into both pricechange and pricechange history
UPDATE PriceChange
SET UnitPrice = (UnitPrice * 1.05),
StartDate = '1/1/2012',
EndingDate = '12/31/2012'
WHERE
ProductID = 1
UPDATE PriceChange
SET UnitPrice = (UnitPrice * 1.05),
StartDate = '1/1/2013',
EndingDate = '12/31/2013'
WHERE
ProductID = 1
UPDATE PriceChange
SET UnitPrice = (UnitPrice * 1.05),
StartDate = '1/1/2014',
EndingDate = '12/31/2014'
WHERE
ProductID = 1
UPDATE PriceChange
SET UnitPrice = (UnitPrice * 1.05),
StartDate = '1/1/2015',
EndingDate = '12/31/2015'
WHERE
ProductID = 1
source to share
The first thing I noticed is that your PriceChange table data can actually be retrieved from the PriceChange_History table.
If there is a reason you should be storing the PriceChange table rather than expose it through a view, I would start by adding an identity column as PK for both tables. I highly recommend that tables always have a simple id column ... you'll be glad you did this sooner or later :-) Then add a suitable unique clustered key to PriceChange: ProductId, StartDate, EndDate. Then bind the PriceChange_History table to the PSP PriceChange ID instead of the productId. Finally, add the corresponding unique clustered key to PriceChange_History: PriceChangeId, ModifiedDate.
You are now ready to update related triggers, functions, etc. accordingly. and be able to insert multiple PriceChange rows into the PriceChange_History table.
Update Just from your current history table, the current price information can be retrieved in a view with the logic below:
Select
LastChangeDetails.ProductId,
ProductChangeDetails.StartDate,
ProductChangeDetails.EndDate,
ProductChangeDetails.UnitPrice
(
Select
History_ProductId,
Max(Modified_date) as LastModifiedDate
From PriceChange_History
) as LastChange
Join PriceChange_History as LastChangeDetails
on LastChangeDetails.History_productId = LastChange.ProductId
and LastChangeDetails.modified_Date = LastChange.LastChangeDate --assumes the most recently modified information is the 'most correct'
source to share