Is it possible to have a generated column based on another table?

Question

I was wonder if it was possible to create a generated column by using a group by query. For example, in the diagram below, is it possible to have the quantity of a equipment be generated based on the number of asset that matches it's foreign key?

Answer 1

You can't do that with a computed column. If you wanted to store and maintain such information, you would need trigger code for every DML operation on table asset , which makes things rather complex.

You can, on the other hand, create a view:

create view v_equipment
select e.*, 
    (select count(*) from asset a where a.equipment_id = e.equipment_id) as quantity
from equipment e

This gives you an always up-to-date perspective at your data. You can query the view directly instead of the table whenever you need the quantity information.