1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
|
ALTER TRIGGER [AP_TG_INS_UPD_F_DOCLIGNE] ON [F_DOCLIGNE]
WITH EXECUTE AS OWNER
FOR INSERT, UPDATE AS
BEGIN
declare @NBJ_MOINS tinyint = 1;
declare @NBJ_PLUS tinyint = 1;
DECLARE @Action as char(1);
SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
THEN 'U' -- Set Action to Updated.
WHEN EXISTS(SELECT * FROM INSERTED)
THEN 'I' -- Set Action to Insert.
WHEN EXISTS(SELECT * FROM DELETED)
THEN 'D' -- Set Action to Deleted.
ELSE NULL -- Skip. It may have been a "failed delete".
END)
BEGIN TRY
IF UPDATE(cbReplication)
RETURN
-- les exceptions du trigger :
-- 1- si le docuement est un BC et que la colonne modifié est DL_QtePL (Qte à livrer) il ne faut
-- déclencher le trigger
If EXISTS (SELECT '*'
FROM inserted
WHERE DO_Type =1 ) AND UPDATE(DL_QtePL)
BEGIN
RETURN
END
-- 2- s'il s'agit d'une transformation partielle d'un BC vers n'importe quel type de docuement
-- il ne faut pas déclencher le trigger
IF EXISTS (SELECT '*'
FROM inserted i
INNER JOIN deleted d ON i.DO_Type =d.DO_Type AND i.DO_Piece = d.DO_Piece
WHERE i.DO_Type =1
AND i.DL_Qte = d.DL_Qte-d.DL_QtePL
)
BEGIN
RETURN
END
-- fin bloc exception
IF EXISTS (SELECT '*'
FROM inserted i
WHERE CONVERT(date, DATEADD(DAY, -@NBJ_MOINS , GETDATE())) > i.DO_Date
OR CONVERT(date, DATEADD(DAY, @NBJ_PLUS, GETDATE())) < i.DO_Date )
RAISERROR('La date du document est hors période de saisie.',16,1)
END TRY
BEGIN CATCH
PRINT error_message()
DECLARE @t TABLE (AR_Ref varchar(35),
AG_No1 int,
AG_No2 int,
DE_No int)
INSERT INTO @t (AR_Ref,AG_No1,AG_No2,DE_No)
SELECT i.AR_Ref,AG_No1,AG_No2,DE_No
FROM inserted i
ROLLBACK TRANSACTION
IF @Action = 'U'
BEGIN
;with x as (SELECT s.DL_Qte As Cumul_Qte, i.AR_Ref,i.DE_No
FROM @t i
OUTER APPLY dbo.Fnc_StockADate(i.DE_No,'31/12/2021',i.AR_Ref,i.AG_NO1,i.AG_NO2,NULL) s
)
UPDATE s
SET s.AS_QteSto = x.Cumul_Qte
FROM F_ARTSTOCK s
inner join x ON s.AR_Ref = x.AR_Ref AND s.DE_No = x.DE_No
END
END CATCH
END;
GO |
Partager