I am trying to write a trigger between an inventory table and a returns table. When a return comes in I want it to check the condition field to see if the return is to be destroyed or returned to stock. If it is to be returned to stock it should add the value to Qty_on_hand and to Qty_returned in the inventory table.
This is what I am trying so far.
CREATE TRIGGER RETURNS_CALC ON [RETURNS]
FOR INSERT
AS UPDATE PM
IF ( RETURNS.CONDITION = '1')
BEGIN
SET PM.QTY_ON_HAND = (PM.QTY_ON_HAND + RETURNS.QTY)
SET PM.QTY_RETURNED = (PM.QTY_RETURNED + RETURNS.QTY)
FROM PRODUCT_MASTER PM JOIN INSERTED
ON RETURNS.PART_NUMBER = PM.PART_NUMBER)
END
This is what I am trying so far.
CREATE TRIGGER RETURNS_CALC ON [RETURNS]
FOR INSERT
AS UPDATE PM
IF ( RETURNS.CONDITION = '1')
BEGIN
SET PM.QTY_ON_HAND = (PM.QTY_ON_HAND + RETURNS.QTY)
SET PM.QTY_RETURNED = (PM.QTY_RETURNED + RETURNS.QTY)
FROM PRODUCT_MASTER PM JOIN INSERTED
ON RETURNS.PART_NUMBER = PM.PART_NUMBER)
END