0

Table component:

enter image description here

Table material:

enter image description here

How can I write a trigger to decrease the material_quantity value in the material table when component_quantity is increased?

By the way the component is made from material so when one component's quantity is increased, the material's quantity which has same material_id will be decrease with the same amount.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
zhiyuan
  • 23
  • 5

1 Answers1

2

After insert/update trigger should help you.

CREATE TRIGGER some_name
ON dbo.components
AFTER INSERT, UPDATE
AS
BEGIN
    UPDATE m
    SET material_quantity = m.material_quantity - (i.component_quantity - c.component_quantity)
    FROM materials m
    INNER JOIN components c ON c.material_id = m.material_id
    INNER JOIN inserted i ON c.component_id = i.component_id
END
gofr1
  • 15,741
  • 11
  • 42
  • 52
  • When an `UPDATE` occurs your code doesn't take into account any values in `deleted`, i.e calculate the deltas resulting from the update. I wonder what the OP might want. – HABO Aug 14 '16 at 14:21
  • @HABO yes, thought of it too. But better use another trigger on delete. And there should be foreign key so the deletion might not go well – gofr1 Aug 14 '16 at 14:25