Il y a manifestement au moins un homme heureux ce soir chez DVP 
J’ai oublié les colonnes LitersPer100Km, AverageMPG dans la vue FUEL_CONSUMPTION_V.
Voici la nouvelle mouture :
CREATE VIEW FUEL_CONSUMPTION_V (CamionVIN, FuelDateReleve, LitersFuel, MileAgeKm, LitersPer100Km, AverageMPG)
AS
SELECT CamionVIN, FuelDateReleve, LitersFuel, MileAgeKm, LitersPer100Km, AverageMPG
FROM CAMION AS x JOIN FUEL_CONSUMPTION AS y ON x.CamionId = y.CamionId
;
Un trigger pour les inserts via la vue (attention à la division par zéro... !) :
-------------------------------------------------------------------------------
-- INSERT dans FUEL_CONSUMPTION à partir de FUEL_CONSUMPTION_V
------------------------------------------------------------------------------
CREATE TRIGGER FUEL_CONSUMPTION_INSERT_TR ON FUEL_CONSUMPTION_V INSTEAD OF INSERT
AS
INSERT INTO FUEL_CONSUMPTION
(
CamionId
, FuelDateReleve
, LitersFuel
, MileAgeKm
)
SELECT
(SELECT CamionId FROM CAMION AS x WHERE EXISTS (SELECT '' FROM INSERTED AS y WHERE x.CamionVIN = y.CamionVIN))
, COALESCE(FuelDateReleve, CAST('9999-12-31' as DATE))
, COALESCE(LitersFuel, 0)
, COALESCE(MileAgeKm, 0) -------attention à la division par zéro !
FROM INSERTED
;
GO
Un trigger pour update (en cours de mise au point)
-------------------------------------------------------------------------------
-- UPDATE de FUEL_CONSUMPTION à partir de FUEL_CONSUMPTION_V
------------------------------------------------------------------------------
CREATE TRIGGER FUEL_CONSUMPTION_UPDATE_TR ON FUEL_CONSUMPTION_V INSTEAD OF UPDATE
AS
DECLARE @CamionIdOld as int ;
DECLARE @CamionIdNew as int ;
DECLARE @CamionVINold as varchar(48) ;
DECLARE @CamionVINnew as varchar(48) ;
DECLARE @FuelDateReleveOld AS DATE ;
DECLARE @FuelDateReleveNew AS DATE ;
------------ pour debug : ---- select '' as inserted, * from INSERTED
------------ pour debug : ---- select '' as deleted, * from DELETED
SET @CamionIdOld = (SELECT CamionId FROM CAMION as x WHERE exists (select '' from DELETED as y where x.CamionVIN = y.CamionVIN))
SET @CamionIdNew = (SELECT CamionId FROM CAMION as x WHERE exists (select '' from INSERTED as y where x.CamionVIN = y.CamionVIN))
SET @CamionVINold = (SELECT CamionVIN from DELETED) ;
SET @CamionVINnew = (SELECT CamionVIN from INSERTED) ;
SET @FuelDateReleveOld = (SELECT FuelDateReleve from DELETED) ;
SET @FuelDateReleveNew = (SELECT FuelDateReleve from INSERTED) ;
------------ pour debug : ---- select '' as '@CamionIdOld', @CamionIdOld ;
------------ pour debug : ---- select '' as '@CamionIdNew', @CamionIdNew ;
------------ pour debug : ---- select '' as '@CamionVINold', @CamionVINold ;
------------ pour debug : ---- select '' as '@CamionVINnew', @CamionVINnew ;
------------ pour debug : ---- select '' as '@FuelDateReleveOld', @FuelDateReleveOld ;
------------ pour debug : ---- select '' as '@FuelDateReleveNew', @FuelDateReleveNew ;
IF UPDATE(LitersFuel) OR UPDATE(MileAgeKm)
BEGIN
UPDATE FUEL_CONSUMPTION
SET LitersFuel = (SELECT LitersFuel FROM INSERTED)
, MileAgeKm = (SELECT MileAgeKm FROM INSERTED)
WHERE CamionId = @CamionIdOld AND FuelDateReleve = @FuelDateReleveOld
;
END
;
IF UPDATE(FuelDateReleve)
BEGIN
UPDATE FUEL_CONSUMPTION
SET FuelDateReleve = (SELECT FuelDateReleve FROM INSERTED)
WHERE CamionId = @CamionIdOld AND FuelDateReleve = @FuelDateReleveOld
;
END
;
IF UPDATE(CamionVIN) AND NOT UPDATE(FuelDateReleve) -- si la date a changé elle aussi, le code ci-dessous ne suffit pas
BEGIN
UPDATE FUEL_CONSUMPTION
SET CamionId = @CamionIdNew
WHERE CamionId = @CamionIdOld AND FuelDateReleve = @FuelDateReleveOld
;
END
/*
IF UPDATE(CamionVIN) AND UPDATE(FuelDateReleve) -- à essayer de faire marcher
BEGIN
UPDATE FUEL_CONSUMPTION
SET CamionId = @CamionIdNew
WHERE CamionId = @CamionIdOld AND FuelDateReleve = (select distinct FuelDateReleve from FUEL_CONSUMPTION WHERE CamionId = @CamionIdOld)
;
END
*/
GO
Un bout de jeu d’essai :
INSERT INTO FUEL_CONSUMPTION_V (CamionVIN, FuelDateReleve, LitersFuel, MileAgeKm)
SELECT CamionVIN, '2018-01-01', 4, 10000
FROM CAMION WHERE CamionVIN = '1FUJGLDR2ALAR5747'
;
INSERT INTO FUEL_CONSUMPTION_V (CamionVIN, FuelDateReleve, LitersFuel, MileAgeKm)
SELECT CamionVIN, '2018-01-01', 300000, 200000
FROM CAMION WHERE CamionVIN = 'C1'
;
INSERT INTO FUEL_CONSUMPTION_V (CamionVIN, FuelDateReleve, LitersFuel, MileAgeKm)
SELECT CamionVIN, '2018-01-02', 400, 800
FROM CAMION WHERE CamionVIN = 'C2'
;
INSERT INTO FUEL_CONSUMPTION_V (CamionVIN, FuelDateReleve, LitersFuel, MileAgeKm)
SELECT CamionVIN, '2018-06-01', 500, 15000
FROM CAMION WHERE CamionVIN = '1FUJGLDR2ALAR5747'
;
SELECT '' as FUEL_CONSUMPTION, * FROM FUEL_CONSUMPTION ;
SELECT '' AS FUEL_CONSUMPTION_V, * FROM FUEL_CONSUMPTION_V
ORDER BY CamionVIN, FuelDateReleve ;
UPDATE FUEL_CONSUMPTION_V
SET LitersFuel = 555
, MileAgeKm = MileAgeKm + 57
WHERE CamionVIN = '1FUJGLDR2ALAR5747' AND FuelDateReleve = '2018-01-01'
select * from FUEL_CONSUMPTION
UPDATE FUEL_CONSUMPTION_V
SET FuelDateReleve = '2018-02-05'
, LitersFuel = LitersFuel + 3
, MileAgeKm = MileAgeKm + 57
WHERE CamionVIN = '1FUJGLDR2ALAR5747' AND FuelDateReleve = '2018-01-01'
UPDATE FUEL_CONSUMPTION_V
SET -------------------CamionVIN = '1FUJGLDR2ALAR5712'
FuelDateReleve = '2018-10-12'
, LitersFuel = LitersFuel + 3
, MileAgeKm = MileAgeKm + 100
WHERE CamionVIN = '1FUJGLDR2ALAR5747' AND FuelDateReleve = '2018-02-05'
UPDATE FUEL_CONSUMPTION_V
SET CamionVIN = '1FUJGLDR2ALAR5712'
------- , FuelDateReleve = '2018-10-15'
, LitersFuel = LitersFuel + 3
, MileAgeKm = MileAgeKm + 100
WHERE CamionVIN = '1FUJGLDR2ALAR5747' AND FuelDateReleve = '2018-10-12' ----------'2018-02-05'
select * from FUEL_CONSUMPTION
select * from FUEL_CONSUMPTION_V
fsmrel
Envoyé par ordigil
Partager