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
|
CREATE or ALTER TRIGGER T1 ON s_item
FOR INSERT
AS
begin
declare @id_ord decimal (7,0)
declare @id_item decimal (7,0)
declare @id_product decimal (7,0)
declare @Qte decimal (9,0)
DECLARE @DIFF decimal (9,0)
DECLARE @max_in_stock decimal (9,0)
DECLARE @id_warehouse decimal (7,0)
DECLARE @amount_in_stock decimal (9,0)
SELECT @id_ord=ord_id,@id_item=item_id, @Id_product=product_id,@Qte=quantity from inserted
SELECT @id_warehouse = warehouse_id, @amount_in_stock=amount_in_stock, @max_in_stock=max_in_stock FROM s_inventory WHERE product_id=@id_product
UPDATE s_inventory SET @amount_in_stock+=@Qte WHERE product_id=@ID_product AND @id_warehouse=warehouse_id
SET @DIFF = @max_in_stock-@amount_in_stock
IF (@DIFF < 0)
BEGIN
SET @Qte = @Qte+(@DIFF)
UPDATE s_item SET @Qte =@Qte WHERE ord_id=@Id_ord
UPDATE s_inventory SET amount_in_stock+=@Qte WHERE product_id=@ID_product AND @id_warehouse=warehouse_id
END
ELSE
BEGIN
UPDATE s_inventory SET amount_in_stock+=@Qte WHERE product_id=@ID_product AND @id_=warehouse_id
END
UPDATE s_item SET quantity= @Qte WHERE product_id=@ID_product
END |
Partager