SQL Server 2008: updating and keeping tracking of anuual price increase

Question

I have a project right now where I need to update the prices for a list of products. The prices for 1/1/2007 to 12/31/2011 are given and I need to increase those prices by 5% each year, until the end of 2015.

Here is what I have. But I am getting stuck on updating the prices (ie 5% increase). I keep getting an error message about duplicate data. 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'. Cannot insert duplicate key in object 'dbo.PriceChange_History'.

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

Answer 1

price_history has PK on ProductID so it cannot have duplicate products. I would add identity primary key.