It is possible to do this with an Update trigger. Whenever a record in the table gets updated, the trigger would automatically fire; if DeptId changed, it could count some records and update the table again with a new StaffCount value.
But just because it is possible, doesn't mean it's a good idea, and I would not be comfortable with this situation myelf. From your brief description, it sounds like StaffID is probably a unique index, and there are many StaffIds attached to any given department.
Problems are:
(1) Which row to update the new StaffCount Value? Since there are (it seems) many rows per DeptId, then it appears we would have to update the StaffCount in all the rows for that DeptId.
(2) All the rows for both the old AND the new DeptId would have to be updated. i.e. When we updated the DeptId, we are adjusting the StaffCount for the new DeptId, but also the old DeptId is wrong too.
Again, please forgive if I have misunderstood you. But this sounds a lot like a de-normalized design where the StaffCount is stored redundantly in too many records, and will be waaaaay too much trouble to keep updated properly.
Even if a dynamic staff count is actually needed (which I somehow doubt, but that's a separate discussion), the only decent way I could see it working was if StaffID was not in the table at all. (In other words if this table was essentially a Dept lookup table.)
Again, forgive me if I am wrong here or if I misunderstood you, and my apologies for such a long post. I hope there's a little bit in there you can use. Probably other forum readers will also post their comments here.
bperry
