SQL Server 2008: Aktualisierung und Verfolgung der jährlichen Preiserhöhung

Question

Ich habe gerade ein Projekt, bei dem ich die Preise für eine Liste von Produkten aktualisieren muss. Die Preise für den Zeitraum vom 1.1.2007 bis 31.12.2011 sind gegeben und ich muss diese Preise jedes Jahr um 5% erhöhen, bis Ende 2015.

Hier ist, was ich habe. Aber ich komme beim Aktualisieren der Preise (dh 5% Erhöhung) nicht weiter. Ich erhalte ständig eine Fehlermeldung über doppelte Daten. Vielen Dank im Voraus für jegliche Hilfe/Hinweise!

Fehlermeldung:

Msg 2627, Level 14, State 1, Procedure update_history, Line 9
Verstoß gegen den PRIMARY KEY-Constraint 'PK_PriceCha_207F7DE23A81B327'. Kann keinen doppelten Schlüssel in Objekt 'dbo.PriceChange_History' einfügen.

Tabellen:

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)

Trigger

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(), 'EINGEFÜGT'
        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(), 'VOR AKTUALISIERUNG'
       from deleted

   insert PriceChange_History(History_ProductID, History_StartDate,
                              History_EndingDate, History_UnitPrice,
                              Modified_date, ChangeType)
      select     
          ProductID, StartDate, EndingDate, UnitPrice,
          GETDATE(), 'NACH AKTUALISIERUNG'
      from inserted

EINFÜGEN + AKTUALISIEREN

INSERT INTO PriceChange 
VALUES(1,'1.1.2007', '31.12.2011', 500) <---  diese Abfrage wird sowohl in pricechange als auch in die PriceChange-Geschichte aufgenommen

UPDATE PriceChange 
SET UnitPrice = (UnitPrice * 1.05),
    StartDate = '1.1.2012',
    EndingDate = '31.12.2012' 
WHERE 
    ProductID = 1

UPDATE PriceChange 
SET UnitPrice = (UnitPrice * 1.05),
    StartDate = '1.1.2013',
    EndingDate = '31.12.2013' 
WHERE 
    ProductID = 1

UPDATE PriceChange 
SET UnitPrice = (UnitPrice * 1.05),
    StartDate = '1.1.2014',
    EndingDate = '31.12.2014' 
WHERE 
    ProductID = 1

UPDATE PriceChange 
SET UnitPrice = (UnitPrice * 1.05),
    StartDate = '1.1.2015',
    EndingDate = '31.12.2015' 
WHERE
    ProductID = 1

Answer 1

Price_history hat einen Primärschlüssel auf ProductID, daher können dieselben Produkte nicht dupliziert werden. Ich würde einen automatisch generierten Primärschlüssel hinzufügen.

Answer 2

Das Erste, was mir auffällt, ist, dass Ihre PriceChange-Tabelle tatsächlich aus der PriceChange_History-Tabelle abgeleitet werden könnte.

Wenn es einen Grund gibt, warum Sie die PriceChange-Tabelle speichern müssen, anstatt sie durch eine Ansicht bereitzustellen, würde ich damit beginnen, beiden Tabellen eine Identitätsspalte als Primärschlüssel hinzuzufügen. Ich empfehle nachdrücklich, dass Tabellen immer eine einfache Identitätsspalte haben ... Sie werden froh sein, dass Sie es früher oder später gemacht haben :-) Als nächstes fügen Sie einen geeigneten eindeutigen gruppierten Schlüssel zu PriceChange hinzu: ProductId, Startdatum, Enddatum. Verknüpfen Sie anschließend die PriceChange_History-Tabelle mit dem Primärschlüssel der PriceChange-Identität anstelle der ProductId. Fügen Sie schließlich einen geeigneten eindeutigen gruppierten Schlüssel zu PriceChange_History hinzu: PriceChangeId, ModifiedDate.

Jetzt sind Sie bereit, die entsprechenden Trigger, Funktionen usw. zu aktualisieren, und werden in der Lage sein, mehrere PriceChange-Zeilen in die PriceChange_History-Tabelle einzufügen.

Aktualisierung Allein aus Ihrer aktuellen Historientabelle können die aktuellen Preisinformationen in einer Ansicht mit der untenstehenden Logik abgeleitet werden:

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 --geht davon aus, dass die zuletzt geänderten Informationen die 'korrektesten' sind