Trigger SQL Server insert record and update a field from another table

Question

Trigger SQL Server insert record and update a field from another table

Navigation

#1 by (3 votes)

1

I have two tables:

tblProducts

-Id int,  
-Producto varchar(10),  
-Cantidad int

tblVentas

-Idventa  
-Idproducto  
-Cantidad int

The tblProductos table is related to tblVentas. What I want to realize is that when the sale of a product is added, the quantity field of the table tblProducts is updated. That is, decrease the amount of products once a sale is made.

I am trying to do it in the following way:

CREATE TRIGGER actualiza_cantidad
on tblventas
AFTER INSERT
AS
BEGIN
UPDATE tblProductos set cantidad=cantidad - inserted.cantidad WHERE 
tblventas.id= inserted.id;
END
GO

sql-server sql-server-2012

asked by Ricardo 14.03.2018 в 07:14

source

1 answer

How to capture the string of the processes that the python interpreter is executing in cmd? Problem with links in laravel

score 3 · Accepted Answer

I think you are not accessing the INSERTED tables correctly.

ALTER TRIGGER [dbo].[actualiza_cantidad] ON [dbo].[tblventas] 
  AFTER INSERT
AS 
BEGIN
   --SET NOCOUNT ON agregado para evitar conjuntos de resultados adicionales
   -- interferir con las instrucciones SELECT.
  SET NOCOUNT ON;

  -- obtener el último valor de identificación del registro insertado o actualizado
  DECLARE @ID INT, @CANTIDAD INT
  SELECT @ID = [id], @CANTIDAD = [cantidad]
  FROM INSERTED

  -- Insertar declaraciones para desencadenar aquí
  UPDATE tblProductos 
  set [cantidad] = ([cantidad] - @CANTIDAD) WHERE 
  [dbo].[tblventas].[id]= @ID;

END

I hope it works for you, I have not tried it, I wrote it in Notepad, Greetings!