sql - Insert Trigger Subtracting Values -

i'm working on mini project involves couple of tables: invoice , inventory

both of these tables have product_id , quantity , need write trigger when new invoice created (inserted) decrement product's quantity in inventory table. appreciated.

create trigger tr_updatequantity on invoice after insert  begin   update inventory      set inventory.quantity = inventory.quantity - invoice.quantity     invoice     inventory.product_id = invoice.product_id end 

i'm assuming you're using sql server based on syntax you've attempted.

you can this:

create trigger tr_updatequantity on invoice after insert  begin declare  @productid numeric, @quantity numeric      select @productid = inserted.product_id,           @quantity = inserted.quantity     inserted    update inventory      set quantity = quantity - @quantity     product_id = @productid  end; 

basically capture product_id , quantity inserted table , use them in update statement. see msdn documentation on inserted table here: https://msdn.microsoft.com/en-us/library/ms191300.aspx

as mentioned @damien_the_unbeliever in comment under question, if have control on schema should consider not storing derived data. can @ data through view or other structure without possibility of data getting out of sync.

update original/accepted answer: others have pointed out, above not account inserting more 1 record, in case need join inserted table.