Run trigger (Sql) for multiple fields

Question

Run trigger (Sql) for multiple fields

Navigation

#1 by (0 votes)

0

I am working on a sales system for food sites with c # and SQL. I have created a trigger that decreases the stock when a product is added to the sale. Each product (plate) is related to a table that stores the ingredients and how many of them are needed.

My problem is that a dish has several ingredients, and I'm consulting in a way that the trigger reduces the stock of all the ingredients and not just one.

The stock is decreased from a table called insumos , which has each input stored:

Id,
stock,
minimum stock.

The table that stores the ingredients is called detalle_Plato and relates the Id of each dish with the id of the input and the quantity of it that is used.

ALTER trigger [dbo].[Stock_D_Venta]
on [dbo].[Detalles_Venta]
after insert 
as
declare @Idinsumo int
select @Idinsumo = Dp.Idinsumo from Detalles_Plato as Dp inner join  
Detalles_Venta as Dv on Dp.IdPlato=Dv.Idproducto
declare @Numero_articulos int
select @Numero_articulos =Cantidad from inserted
declare @Cantidad_Insumo decimal (18,1)
select @Cantidad_Insumo =  Dp.Cantidad from Detalles_Plato as Dp inner join    
Detalles_Venta as Dv on Dp.IdPlato=Dv.Idproducto
declare @Gasto_Stock decimal (18,1)
set @Gasto_Stock=@Cantidad_Insumo*@Numero_articulos
update Insumos set Stock=Stock-@Gasto_Stock
where Idinsumo=@Idinsumo

sql sql-server

asked by Andres Cabrera 20.12.2016 в 06:13

source

1 answer

Filter by a queryset with 2 kwargs How do I remove the white background from a table? Foundation Rails

score 0 · Accepted Answer

It would have been better if you more explicitly described the different tables and their relationships. But I think I could understand more or less.

The correct thing in this case is to combine everything in one sentence, and it is not that difficult. Assuming I understood the relationships correctly, the trigger would look like this:

alter trigger [dbo].[Stock_D_Venta]
on [dbo].[Detalles_Venta]
after insert 
as
  set nocount on;

  update i
     set i.stock -= t.cantidad
    from insumos i
    join (select dp.idinsumo, cantidad = sum(dv.cantidad * dp.cantidad)
            from inserted dv
            join detalles_plato dp
              on dp.idplato = dv.idproducto
           group by dp.idinsumo) t
      on t.idinsumo = i.idinsumo;
go

Demo