CREATE TRIGGER CAMION_HUILE_SUIVI_INSERT_TR ON CAMION_HUILE_SUIVI INSTEAD OF INSERT
AS
BEGIN
DECLARE @CamionId AS INT ;
DECLARE @CamionVIN AS VARCHAR(24) ;
DECLARE @CamionNumber AS VARCHAR(6) ;
DECLARE @SerialNumber AS VARCHAR(25) ;
DECLARE @PositionId AS INT ;
DECLARE @ComposantId AS INT ;
DECLARE @ComponentSerialNumberInDataBase AS VARCHAR(25) ;
DECLARE @ComposantTypeCode AS CHAR(1) ;
DECLARE @ComposantTypeSource AS varchar(12) ;
DECLARE @ComposantType AS VARCHAR(12) ;
DECLARE @OilChangeDate AS DATE ;
DECLARE @OilChangeMilleage AS INT ;
DECLARE @trigger as varchar(64) = 'ENGINE_OIL_CHANGE_V_INSERT_TR'
DECLARE @Engueulade as VARCHAR(256) = @trigger + ' - Oil change ; ' ;
DECLARE @N AS INT ;
---- pour debug ---- select @trigger as '@trigger'
---- pour debug ---- select '' as INSERTED, * from INSERTED
--------------------------------------------------------
-- Le curseur utilisé pour les inserts multiples,
-- appliqué à INSERTED :
---------------------------------------------------------
DECLARE theCurseur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT CamionVIN, CamionNumber, ComposantType, SerialNumber, HuileChangementDate, HuileKm
FROM INSERTED ;
OPEN theCurseur
---- pour debug ----select 'open csr'
-----------------------------------------------------
-- Lecture de la 1re ligne de INSERTED
-----------------------------------------------------
FETCH theCurseur INTO @CamionVIN, @CamionNumber, @ComposantTypeSource, @SerialNumber, @OilChangeDate, @OilChangeMilleage
---- pour debug ---- select @@FETCH_STATUS as ' @@FETCH_STATUS'
WHILE @@FETCH_STATUS = 0
-------------------------------------------------------------
-- tant que le curseur produit,
-- on traite la ligne correpondante de INSERTED
-------------------------------------------------------------
BEGIN
--- pour debug --- select 'un tour de manège'
---- pour debug ---- select @CamionVIN as '@CamionVIN'
----------------------------------------
-- Pour la ligne en cours :
-- Récup de l'identification du camion
-- en fonction de son numéro
----------------------------------------
SET @CamionId =
(
SELECT CamionId
FROM CAMION
WHERE CamionNumber = @CamionNumber
)
;
---- pour debug ---- select @CamionId as '@CamionId'
------------------------------------
-- On s'assure que le camion existe
------------------------------------
IF @CamionId IS NULL
BEGIN
SET @Engueulade = @Engueulade + 'Unknow Truck'
SELECT @Engueulade AS Engueulons, * FROM INSERTED
-------- RAISERROR (@Engueulade,16,1) -- state = 16 pour bloquer
RAISERROR (@Engueulade,0,1) -- state = 0 pour les tests
RETURN
END
---- pour debug ---- select @ComposantTypeSource as '@ComposantTypeSource'
------------------------------------------------
-- récup du composant type
------------------------------------------------
SET @ComposantType =
(
CASE
WHEN lower(@ComposantTypeSource) = 'engine' THEN 'e'
WHEN left(lower(@ComposantTypeSource),5) = 'trans' THEN 't'
WHEN left(lower(@ComposantTypeSource),4) = '1int' THEN '1INTGAWR'
WHEN left(lower(@ComposantTypeSource),4) = '2int' THEN '2INTGAWR'
WHEN left(lower(@ComposantTypeSource),4) = '3int' THEN '3INTGAWR'
WHEN left(lower(@ComposantTypeSource),4) = '4int' THEN '4INTGAWR'
WHEN left(lower(@ComposantTypeSource),4) = '5int' THEN '5INTGAWR'
WHEN left(lower(@ComposantTypeSource),4) = '6int' THEN '6INTGAWR'
WHEN left(lower(@ComposantTypeSource),4) = '7int' THEN '7INTGAWR'
WHEN left(lower(@ComposantTypeSource),4) = '8int' THEN '8INTGAWR'
WHEN left(lower(@ComposantTypeSource),3) = 'frg' THEN 'frgawr'
WHEN left(lower(@ComposantTypeSource),4) = 'rear' THEN 'reargawr'
WHEN lower(@ComposantTypeSource) = 'tag' THEN 'tag'
ELSE '?'
END
)
---- pour debug ---- select @ComposantType as '@ComposantType'
--------------------------------------------------
-- si le type dde composant est foireux, on dégage
-- (comme le môme de l'Elysée)
--------------------------------------------------
IF @ComposantType = '?'
BEGIN
SET @Engueulade = @Engueulade + ' camion '''
+ @CamionNumber + ''' : Invalid component type '''
+ @ComposantTypeSource + '''.'
SELECT @Engueulade AS Engueulons, * FROM INSERTED
-------- RAISERROR (@Engueulade,16,1) -- state = 16 pour bloquer
RAISERROR (@Engueulade,0,1) -- state = 0 pour les tests
RETURN
END
------------------------------------------
-- On s'assure que Cézigue a fourni
-- une date pour le changement d'huile,
-- sinon on perd du temps pour rien...
------------------------------------------
IF @OilChangeDate IS NULL
BEGIN
SET @Engueulade = @Engueulade + 'camion ''' + @CamionNumber
+ ''', composant de type ''' + @ComposantTypeSource
+ ''' : veuillez fournir la date de changement d''huile.'
SELECT @Engueulade AS Engueulons, * FROM INSERTED
-------- RAISERROR (@Engueulade,16,1) -- state = 16 pour bloquer
RAISERROR (@Engueulade,0,1) -- state = 0 pour les tests
RETURN
END
------------------------------------------------------------------
-- Table locale @t servant à empiler pour un camion
-- les identifiants des composants du type de composant
-- fourni par Cézigue (moteur, transmission, axle positionné).
-- En fait, cette table n'a d'intérêt que pour les transmissions,
-- sachant que si Cézigue n'en fournit pas le numéro de série
-- tandis qu'il y a plus d'une transmission pour le camion,
-- alors on lui demandera de fournir ce numéro. Il s'agit d'un
-- cas rare, mais pas impossible.
-- Un camion n'ayant qu'un seul moteur, on sait le déterminer,
-- il est donc inutile que Cézigue en fournisse le numéro de série.
-- Quant aux axles, là encore il est inutile que Cézigue en
-- fournisse le numéro de série, puisqu'on sait déterminer
-- celui-ci en fonction de sa position sur le camion,
-- position que Cézigue devra évidemment impértivement fournir.
--
-- La table @t n'est pas essentielle : pour le camion et le
-- type de composant en cours on pourrait se contenter d'un COUNT
-- des composants, mais à l'avenir on pourrait être intéressé
-- de savoir quels sont ces composants.
------------------------------------------------------------------
DECLARE @t TABLE
(
ComposantId INT
)
-------------------------------------------------
-- en cas d'insert multivalué, il faut
-- virer ce qui a été empilé lors du tour
-- de manège précédent.
-------------------------------------------------
DELETE FROM @t
----------------------------------------------------
-- moteur ou transmission : empilement dans @t.
--
-- Pour les axles, on atendra d'avoir traité
-- de leur position sur le camion.
----------------------------------------------------
IF @ComposantType IN ('e', 't')
BEGIN
INSERT INTO @t
SELECT DISTINCT x.ComposantId
FROM COMPOSANT_AFFECTATION AS x
JOIN COMPOSANT AS y ON x.ComposantId = y.ComposantId
WHERE x.LocalisationId = @CamionId
AND LOWER(y.ComposantType) = @ComposantType
END
ELSE
--------------------------------------------
-- axles - contrôles liés à leur position
-- et emplilement dans @t.
--------------------------------------------
BEGIN
SET @PositionId = (SELECT PositionId FROM AXLE_POSITION WHERE PositionCode = @ComposantType)
---- pour debug ---- select @PositionId as '@PositionId'
-----------------------------------------------------------
-- on vérifie qu'il y a bien un axle à la position proposée
-----------------------------------------------------------
DECLARE @N2 AS INT
SET @N2 = (
SELECT COUNT(*)
FROM COMPOSANT_AFFECTATION AS x
JOIN AXLE AS y ON x.ComposantId = y.ComposantId
WHERE x.LocalisationId = @CamionId
AND x.PositionId = @PositionId
)
---- pour debug ---- select @n2 as '@N2'
IF @N2 = 0
BEGIN
------------------------------------------
-- Cézigue s'est gourré
------------------------------------------
SET @Engueulade = @Engueulade + 'Camion ''' + @CamionNumber + ''' : '
+ 'Il n''y a pas d''axle en position '''
+ @ComposantType + '''.' -- + @ComposantTypeSource + ').'
SELECT @Engueulade AS Engueulons, * FROM INSERTED
-------- RAISERROR (@Engueulade,16,1) -- state = 16 pour bloquer
RAISERROR (@Engueulade,0,1) -- state = 0 pour les tests
RETURN
END
---------------------------------------------------
-- Il y a bien un axle à la position proposée,
-- on empile pour retrouver ensuite le traitement
-- valant pour tous les types de composants.
---------------------------------------------------
ELSE
BEGIN
INSERT INTO @t
SELECT DISTINCT x.ComposantId
FROM COMPOSANT_AFFECTATION AS x
JOIN AXLE AS y ON x.ComposantId = y.ComposantId
WHERE x.LocalisationId = @CamionId
AND x.PositionId = @PositionId
---pour debug --- select '' as '@t !!', * from @t
END
------------------------------------------------------
-- terminé pour le contrôle de la position des axles
------------------------------------------------------
END
--------------------------------------------------
--
-- Reprise des traitements communs à tous les
-- types de cmposants
--
--------------------------------------------------
---- pour debug ---- select '' as '@t', * from @t
------------------------------------------------------
-- Rappel : à part le cas des transmissions (rare !),
-- un composant n'a qu'un élément
-- dans la pile des identifiants des composants.
-------------------------------------------------------
-------------------------------------------------------
-- Normalement, Cézigue ne fournit pas le serial
-- number d'un composant, mais s'il le fait, il faut
-- s'assurer que c'est bien celui qui est connu
-- dans la base de données.
--
-- A noter que dans le cas des transmissions,
-- s'il y en a plus d'une pour un camion,
-- il faudra gueuler en demandant
-- à Cézigue de fournir le numéro de série de la
-- transmission à huiler.
-------------------------------------------------------
---------------------------------------------------------
-- Comme la variable locale @ComposantId n'a le droit de
-- prendre qu'une valeur, alors que pour une transmision
-- on peut en avoir deux, on utilise l'astuce "TOP 1"
-- afin de ne pas se faire massacrer par SQL Server,
-- sachant qu'on traitera plus loin du cas de deux
-- transmissions pour un camion.
---------------------------------------------------------
SET @ComposantId = (SELECT TOP 1 ComposantId FROM @t)
--- pour debug ---- select @ComposantId as '@ComposantId (TOP 1)'
--------------------------------------------------------
-- Le numéro de série du composant présent dans la base
--------------------------------------------------------
SET @ComponentSerialNumberInDataBase =
CASE @ComposantType
WHEN 'e' then (select MoteurNumeroSerie from MOTEUR where ComposantId = @ComposantId)
WHEN 't' then (select TransmissionNumeroSerie from TRANSMISSION where ComposantId = @ComposantId)
ELSE (select AxleSerialNumber from AXLE where ComposantId = @ComposantId)
END
--- pour debug ---- select @ComponentSerialNumberInDataBase as '@ComponentSerialNumberInDataBase (TOP 1)'
-------------------------------------------------------
-- S'il n'y a qu'un seul composant, tant mieux,
-- c'est plus simple !
-------------------------------------------------------
SET @N = (SELECT COUNT(DISTINCT ComposantId) FROM @t)
--- pour debug ---- select @N as '@N'
IF @N = 1
BEGIN
-------------------------------------------------------
-- On regarde si Cézigue a fourni un numéro de série.
-- Dans la négative, c'est bien, et on prend celui
-- qu'on a en base. S'il en a fourni un, on se doit
-- de vérifier qu'il a fourni celui qu'on a en base.
-------------------------------------------------------
IF @SerialNumber IS NULL OR LEN(TRIM(@SerialNumber)) > 0
BEGIN
SET @SerialNumber = @ComponentSerialNumberInDataBase
END
ELSE
BEGIN
----------------------------------------------------
-- Si Cézigue a proposé un numéro de série et que
-- la base n'en contient qu'un pour le camion,
-- d'accord, mais à condition que Cézigue soit
-- en phase avec la base.
----------------------------------------------------
IF LOWER(@ComponentSerialNumberInDataBase) <> LOWER(@SerialNumber)
BEGIN
SET @Engueulade = @Engueulade + 'Camion ''' + @CamionNumber + ''', ' + @ComposantType
+ ' ; selon la base de données, le serial number correspondant n''est pas '''
+ @SerialNumber + ''', mais ''' + @ComponentSerialNumberInDataBase + '''.'
SELECT @Engueulade AS Engueulons, * FROM INSERTED
-------- RAISERROR (@Engueulade,16,1) -- state = 16 pour bloquer
RAISERROR (@Engueulade,0,1) -- state = 0 pour les tests
RETURN
END
END
END -- fin de @N = 1
-------------------------------------------------------
-- S'il y a plus d'un composant possible, cas des
-- transmissions, pour que l'ambiguïté soit levée, Cézigue
-- aura dû en fournir le numéro de série.
-------------------------------------------------------
IF @N > 1
BEGIN
--------------------------------------------------------------
-- On a plus d'une occurence de Composantid dans la table @t :
-- il s'agit de composants distincts (transmissions)
-- Pour qu'il n'y ait pas d'ambiguïté, Cézigue aura dû fournir
-- le numéro de série du comosant en cours. S'il ne l'a pas
-- fait, on l'engueule.
--------------------------------------------------------------
IF @SerialNumber IS NULL
BEGIN
SET @Engueulade = @Engueulade + 'le camion ''' + @CamionNumber + ''' a ' + CAST(@N AS VARCHAR)
+ ' composants de type ' + @ComposantTypeSource
+ ', veuillez fournir le numéro de série de celui dont l''huile est changée.'
SELECT @Engueulade AS Engueulons, * FROM INSERTED
-------- RAISERROR (@Engueulade,16,1) -- state = 16 pour bloquer
RAISERROR (@Engueulade,0,1) -- state = 0 pour les tests
RETURN
END
---------------------------------------------------------------
-- Cézigue a fourni un serial number, reste à vérifier qu'il
-- existe dans la base.
-- On recalcule @ComposantId, car celui obtenu initialement
-- avec TOP 1 n'est pas forcément le bon et n'est pertinent
-- que lorqu'un camion n'a qu'une seul composant du type
-- fourni par Cézigue.
---------------------------------------------------------------
SET @ComposantId =
(
SELECT y.ComposantId
FROM COMPOSANT_AFFECTATION AS x
JOIN TRANSMISSION AS y ON x.ComposantId = y.ComposantId
WHERE x.LocalisationId = @CamionId
AND y.TransmissionNumeroSerie = @SerialNumber
)
---- pour debug ---- select @ComposantId as '@ComposantId (@N > 1)'
-------------------------------------------------------
-- Si Cézigue a fourni un numéro de série inconnu,
-- on dégage.
-------------------------------------------------------
IF @ComposantId IS NULL
BEGIN
SET @Engueulade = @Engueulade + 'Camion ''' + @CamionNumber + ''', ' + @ComposantTypeSource
+ ' ; le serial number ''' + @SerialNumber
+ ''', n''est pas connu. '
SELECT @Engueulade AS Engueulons, * FROM INSERTED
-------- RAISERROR (@Engueulade,16,1) -- state = 16 pour bloquer
RAISERROR (@Engueulade,0,1) -- state = 0 pour les tests
RETURN
END
END -- fin de @N > 1
--- pour debug ---- select @ComposantId as '@ComposantId'
-----------------------------------------------------
-- On s'assure que le composant n'a pas déjà
-- fait l'objet d'un changement d'huile à la date
-- fournie par Cézigue
-----------------------------------------------------
DECLARE @Nchg as INT
set @Nchg =
(
SELECT COUNT(*)
from HUILE_CHANGEMENT
where ComposantId = @ComposantId
and HuileChangementDate = @OilChangeDate
)
if @Nchg > 0
begin
SET @Engueulade = @Engueulade + 'camion ''' + @CamionNumber + ''', ' + @ComposantTypeSource + ' ''' + @SerialNumber
--------- + ' ' + lower(@ComposantTypeSource)
+ ''' : changement d''huile déjà effectué le '
+ CAST(@OilChangeDate AS VARCHAR)
+ ' (au besoin, faites un update).'
SELECT @Engueulade AS Engueulons, * FROM INSERTED
-------- RAISERROR (@Engueulade,16,1) -- state = 16 pour bloquer
RAISERROR (@Engueulade,0,1) -- state = 0 pour les tests
RETURN
end
-------------------------------------------------------------
--------------------------------------------------------------
-- On insère une ligne
--------------------------------------------------------------
----pour debug --- select 'on tente l''insert'
INSERT INTO HUILE_CHANGEMENT
(
ComposantId
, HuileChangementDate
, HuileKm
)
SELECT @ComposantId
, COALESCE(@OilChangeDate, CAST('9999-12-31' as DATE))
, COALESCE(@OilChangeMilleage, 0) -- attention à la division par zéro...
;
--------------------------------------------
-- On entame le tour de manège suivant
-- avec le curseur.
--------------------------------------------
---- pour debug --- select 'fetch suivant'
FETCH theCurseur INTO @CamionVIN, @CamionNumber, @ComposantTypeSource, @SerialNumber, @OilChangeDate, @OilChangeMilleage
-- LOOP
--------------------------------------------
-- Fin du manège
--------------------------------------------
END
CLOSE theCurseur
DEALLOCATE theCurseur
END
GO
Envoyé par fsmrel
Partager