nested counters

[bluebytez]

Hi, can anyone help me with this problem ?
I want to create a table with three fields, DeptID, StaffID and StaffCounter.
The staffcounter is a counter for the number of staff in a department. Whenever the DeptID changes, I would like to reset the staffcounter. I don't think Identity works for this problem.
Can anyone help me ?

 

[bperry]

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

 

[bluebytez]

Thanks for your input bperry. i agree that it's probably not a good idea to implement this. I will probably format the numbering in my ASP programming. Thanks again, I really appreciate your response.