CODEWITHSUNDEEP
sqlsql-servert-sql
Blake Rivell
Blake Rivell

Reputation: 13875

Creating a trigger in SQL Server for the appropriate table based on the logic

I have an InventoryOnHand table with a Quantity column.

When an InventoryTransaction record is INSERTED, I need to update the Quantity in the InventoryOnHand table.

Can someone please explain the following:

  1. Should the trigger created in the InventoryOnHand table or the InventoryTransaction table? I would assume the InventoryTransaction table since this is the 'INSERTED'

  2. When naming the trigger, should the tablename be the updated table or the actual table with the trigger applied to it? For example: tr_InventoryTransactionIns

Here is what I have:

CREATE TRIGGER dbo.tr_InventoryTransactionIns 
   ON  dbo.Inventory 
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    UPDATE Inventory SET QuantityOnHand = QuantityOnHand + INSERTED.Quantity
END
GO

Upvotes: 1

Views: 59

Answers (1)

marc_s
marc_s

Reputation: 754488

You also need to somehow link the rows being inserted with the rows you're updating in the Inventory table -right now, you're updating the entire Inventory table!

Try something like this:

CREATE TRIGGER dbo.tr_InventoryTransactionIns 
ON dbo.InventoryTransaction
AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE inv
    SET QuantityOnHand = QuantityOnHand + INSERTED.Quantity
    FROM Inventory inv
    INNER JOIN Inserted ins ON inv.InventoryId = ins.InventoryId
END
GO

Upvotes: 2

Related Questions