Use a Trigger After Insert for updating a table?

Question

I'm using a Trigger on SQL server to update a table stock when a sell is inserted into another table, but the trigger is not doing anything to the table, I suspect I must have an error I can't decipher. When I execute the test Inserts it shows no change to the first table.

The tables are:

 Sku VARCHAR (50) PRIMARY KEY, 
 Stock NUMERIC (38)
);

CREATE TABLE dbo.Salida_Producto (
 Numero_Salida INT PRIMARY KEY,
 Sku VARCHAR (50),
 Cantidad_Salida INT,
 FOREIGN KEY(Sku) REFERENCES Stock(Sku)
);
--Test Tabla Stock. Test Values.
INSERT INTO dbo.Stock VALUES ('El Mitchies',100);
INSERT INTO dbo.Stock VALUES ('La Karencilla',200);
INSERT INTO dbo.Stock VALUES ('Perro',300);```


The Trigger:

CREATE TRIGGER [dbo].[tr_for_insert]
   ON  [dbo].[Salida_Producto]
   AFTER INSERT
AS 
BEGIN
DECLARE 
@Item varchar,
@Cuantos numeric
    SELECT @Item = INSERTED.Sku,
            @Cuantos = INSERTED.Cantidad_Salida
            FROM INSERTED
    UPDATE Stock
        SET Stock = Stock - @Cuantos
        WHERE Sku = @Item

END;
GO

Test Inserts

INSERT INTO dbo.Salida_Producto VALUES (1, 'El Mitchies',3);
INSERT INTO dbo.Salida_Producto VALUES (2, 'La Karencilla',6);
INSERT INTO dbo.Salida_Producto VALUES (3,'Perro',130); ```

The problem I have is that the message box says:

(0 row(s) affected)

(1 row(s) affected)

Answer 1

You have thought your trigger with a row by row approach. It is not good practice. You must learn to think with a "by set" approach.

So a better way to achieve your goal should be something like :

Update S
      Set S.Stock = S.Stock - I.Cantidad_Salida
   From Stock S
   Inner Join inserted I
   On S.Sku = I.Sku