salve,
burinic ha scritto:
Ciao a tutti. Ho una tabella articoli molto semplice come quella di seguito:
ID_Articolo
Codice_Fornitore
Descrizione
Modello
Prezzo_Vendita
Prezzo_Acquisto
Giacenza
Devo creare uno storico dei cambiamenti alla tabella...ovvero quando cambia il prezzo d'acquisto, il prezzo di vendita o la giacenza devo memorizzare in un'altra tabella la variazione che si è verificata. Avevo pensato ad una tabella del tipo:
ID_Articolo
Data_Ora
Prezzo_Vendita
Prezzo_Acquisto
Giacenza
Ogni record conterrà quindi l'id, il momento in cui si è verificata la modifica e la variazione dei campi (+20 in prezzo_vendita significherà un aumento di 20 euro rispetto il prezzo precedente, -10 in prezzo di acquisto significherà -10 euro rispetto il prezzo precedente, 0 in giacenza significa ad esempio che la quantità disponibile non è cambiata).
Devo usare trigger in quanto vi sono diversi modi per cui può avvenire una variazione e è troppo complesso considerare ogni singolo caso. Faccio sicuramente prima a far fare tutto al DB.
Però...come faccio a creare questo script?
di seguito un esempio (ridotto) di come puoi procedere in tal senso..
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.Articoli (
ID_Articolo int NOT NULL PRIMARY KEY,
Codice_Fornitore int NOT NULL,
Descrizione varchar(10) NOT NULL,
Modello int NOT NULL ,
Prezzo_Vendita decimal(18,4) NOT NULL DEFAULT 0,
Prezzo_Acquisto decimal(18,4) NOT NULL DEFAULT 0,
Giacenza decimal(18,4) NOT NULL DEFAULT 0
);
CREATE TABLE dbo.ArticoliHistory (
ID_Articolo int NOT NULL,
Data_Ora datetime NOT NULL DEFAULT GETDATE(),
Prezzo_Vendita decimal(18,4) NOT NULL DEFAULT 0,
--Prezzo_Acquisto decimal(18,4) NOT NULL DEFAULT 0,
--Giacenza decimal(18,4) NOT NULL DEFAULT 0
OldPrezzo decimal (18,4) DEFAULT 0,
NewPrezzo decimal (18,4) DEFAULT 0
CONSTRAINT pk_ArticoliHistory PRIMARY KEY
(ID_Articolo, Data_Ora)
);
GO
CREATE TRIGGER tr_U_Articoli ON dbo.Articoli
FOR UPDATE
AS BEGIN
DECLARE @r int;
SET @r = @@ROWCOUNT;
IF @r = 0 RETURN;
IF @r > 1 BEGIN
RAISERROR ('É possibile aggiornare una sola riga alla volta', 16 , 10);
RETURN;
END;
-- modica esistente
INSERT INTO dbo.ArticoliHistory
SELECT i.ID_Articolo, GETDATE(),
( (i.Prezzo_Vendita * 100) / d.Prezzo_Vendita ) - 100 AS [DeltaPVendita]
,d.Prezzo_vendita
,i.Prezzo_Vendita
FROM deleted d
JOIN inserted i ON d.ID_Articolo = i.ID_Articolo
JOIN
(SELECT TOP 1 h2.*
FROM dbo.ArticoliHistory h2 JOIN inserted i2
ON i2.ID_Articolo = h2.ID_Articolo
ORDER BY Data_Ora DESC ) h
ON i.ID_Articolo = h.ID_Articolo
WHERE EXISTS (
SELECT *
FROM dbo.ArticoliHistory h
JOIN inserted i ON i.ID_Articolo = h.ID_Articolo
);
-- inserimento iniziale esistente
INSERT INTO dbo.ArticoliHistory
SELECT i.ID_Articolo, GETDATE(),
((i.Prezzo_Vendita * 100) /h.Prezzo_Vendita) -100 AS [DeltaPVendita]
,h.Prezzo_Vendita
,i.Prezzo_Vendita
FROM inserted i JOIN deleted h
ON i.ID_Articolo = h.ID_Articolo
WHERE NOT EXISTS (
SELECT *
FROM dbo.ArticoliHistory h
JOIN inserted i ON i.ID_Articolo = h.ID_Articolo
);
END;
GO
INSERT INTO dbo.Articoli VALUES ( 1, 1, 'aa', 1, 10,20,30);
INSERT INTO dbo.Articoli VALUES ( 2, 1, 'aa', 1, 20,20,30);
WAITFOR DELAY '00:00:01';
UPDATE dbo.Articoli SET Prezzo_Vendita = 12 WHERE id_articolo = 1;
WAITFOR DELAY '00:00:01';
UPDATE dbo.Articoli SET Prezzo_Vendita = 15 WHERE id_articolo = 1;
WAITFOR DELAY '00:00:01';
UPDATE dbo.Articoli SET Prezzo_Vendita = 15 WHERE id_articolo = 1;
WAITFOR DELAY '00:00:01';
UPDATE dbo.Articoli SET Prezzo_Vendita = 12 WHERE id_articolo = 2;
GO
PRINT 'fallisce';
UPDATE dbo.Articoli SET Prezzo_Vendita = Prezzo_Vendita * 1.5;
GO
PRINT 'visione della storicizzazione';
SELECT * FROM dbo.ArticoliHistory;
GO
DROP TABLE dbo.ArticoliHistory, dbo.Articoli;
--<--------------
fallisce
Msg 50000, Level 16, State 10, Procedure tr_U_Articoli, Line 9
É possibile aggiornare una sola riga alla volta
visione della storicizzazione
ID_Articolo Data_Ora Prezzo_Vendita OldPrezzo NewPrezzo
----------- ----------------------- ---------------- ----------- ----------
1 2007-01-29 17:09:06.700 20.0000 10.0000 12.0000
1 2007-01-29 17:09:07.717 25.0000 12.0000 15.0000
1 2007-01-29 17:09:08.717 0.0000 15.0000 15.0000
2 2007-01-29 17:09:09.717 -40.0000 20.0000 12.0000
personalmente non ti consiglio di storicizzare il delta, cioe' la sola variazione, bensi' di procedere con il valore finito visto che il solo delta ti comporta inutili rinvii a complicati join nel caso di renderizzazione di report, oltre al fatto stesso di richiedere un "tappo" iniziale e tutte le righe di storico come fattore non eliminabile al fine di ottenere risultati corretti..
la stessa correttezza formale dei calcoli puo' poi purtroppo essere compromessa da eventuali arrotondamenti (e successivi arrotondamenti sugli arrotondamenti) che andassero ad accumularsi..
saluti
