Blake Rivell Blake Rivell - 3 months ago 18
SQL Question

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

Answer

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
Comments