how to I create a derived column by aggregate data from another column in another table

Question

I have 2 tables :

Book(Name,quantity,supplier)
SuppliedBy (supplier,quantity)

I want the quantity in SuppliedBy table equal sum of the books supplied by that supplier, so I wrote a set statement

update supplier
set supplier.quantity = (select quantity 
                         from (select sum(quantity) as quantity, SuppliedBy 
                               from book group by SuppliedBy) a
                         where a.SuppliedBy = supplier.SuppliedBy)

However I want to automatically update quantity value in SuppliedBy when a new book is add. Please help

Answer 1

I think you need to create a after insert trigger on Book table to update value in supliedBy table.