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
|
CREATE TRIGGER insert_stock
ON fMOUVEMENTS INSTEAD OF INSERT
AS
declare @id_article int
declare @id_vehicule int
declare @qte int
declare @stock_init int
declare @stock_final int
declare @sens varchar(6)
declare @date datetime
declare @obs varchar(255)
BEGIN
SELECT @id_article = ID_ARTICLE, @sens = TYPE_MOUVEMENT, @qte = QTE_MOUVEMENT, @date = DATE_MOUVEMENT, @id_vehicule = ID_VEHICULE, @obs = OBS_MOUVEMENT FROM inserted
SET @stock_init = (SELECT QTE_STOCK From fARTICLES WHERE ID_ARTICLE = @id_article)
IF @sens = 'Sortie'
BEGIN
IF @stock_init < @qte
BEGIN
RAISERROR ('La quantité que vous souhaitez déstockée est indisponible',0,1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
SET @stock_final = @stock_init - @qte
INSERT INTO fMOUVEMENTS (ID_ARTICLE, DATE_MOUVEMENT, QTE_MOUVEMENT, TYPE_MOUVEMENT, ID_VEHICULE, OBS_MOUVEMENT, STOCK_INITIAL, STOCK_FINAL)
VALUES (@id_article, @date, @qte, @sens, @id_vehicule, @obs, @stock_init, @stock_final)
COMMIT TRANSACTION
END
END
ELSE
BEGIN
SET @stock_final = @stock_init + @qte
INSERT INTO fMOUVEMENTS (ID_ARTICLE, DATE_MOUVEMENT, QTE_MOUVEMENT, TYPE_MOUVEMENT, OBS_MOUVEMENT, STOCK_INITIAL, STOCK_FINAL)
VALUES (@id_article, @date, @qte, @sens, @obs, @stock_init, @stock_final)
COMMIT TRANSACTION
END
END |
Partager