auto updated column by another column in another table

I have two tables, Employee and Sales. in the Employee table there is a column called 'number of sales'. but I want it to be uninsertable. so you cannot insert anything to it, and it will be updated by another factor: for every column in the Sales that has the same ID as that employee I want to see the number of sales in the Employee 'number of sales' column.

something like [number of sales]=select count(*) from sales s group by employeeID where EmployeeID=s.EmployeeID

1 answer

  • answered 2017-06-17 19:05 Gordon Linoff

    The usual approach to this is a trigger (documented here).

    You can also use a generated column with a user-defined function.

    However, I would caution you from both these approaches because they can be complex and can affect performance in unexpected ways. Instead, why not just create a view?

    create view v_employees as
        select e.*, s.cnt
        from employees e outer apply
             (select count(*) as cnt
              from sales s 
              where s.EmployeeID = e.EmployeeID
             ) s;

    You can query the view and get the value whenever you need it. The value is automatically "updated" when the values in sales change -- due to inserts, updates, and deletes.