MS SQL Server 2008: updating and keeping tracking of anuual price increase
I have a project right now where it ask me to update the prices on a list of products, the prices for 1/1/2007 to 12/31/2011 is given, i need to increase the price by 5% each year till the end of 2015. Here is what i have, but i getting stuck at updating the prices (5% increase) its telling me its duplicate data. Thanks in advance for any help/hint !


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

--------

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

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


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



--------

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

I have a project right now where it ask me to update the prices on a list of products, the prices for 1/1/2007 to 12/31/2011 is given, i need to increase the price by 5% each year till the end of 2015. Here is what i have, but i getting stuck at updating the prices (5% increase) its telling me its duplicate data. Thanks in advance for any help/hint !


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

--------

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

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


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



--------
