gpt4 book ai didi

SQL 服务器 2008 : updating and keeping tracking of anuual price increase

转载 作者:行者123 更新时间:2023-12-04 20:55:35 27 4
gpt4 key购买 nike

我现在有一个项目需要更新产品列表的价格。给出了 2007 年 1 月 1 日到 2011 年 12 月 31 日的价格,我需要将这些价格每年提高 5%,直到 2015 年底。

这是我的。但我一直坚持更新价格(即 5% 的涨幅)。我不断收到有关重复数据的错误消息。在此先感谢您的帮助/提示!

错误信息:

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

表格:

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)

触发器

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

最佳答案

price_history 在 ProductID 上有 PK,因此它不能有重复的产品。我会添加身份主键。

关于SQL 服务器 2008 : updating and keeping tracking of anuual price increase,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9660291/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com