auto_decrement

[slok]

I have 2 tables employee and department.
In department, there is a field dept_count which keeps
track of the number of employees in a certain department.

Every time a new employee is added, there is a sequence
that can auto_increment

eg. employeeNo.NextVal

Question is

1. Is there a way to auto_decrease the department count
every time an employee is deleted?

 

[EdWall]

I suggest that you take care in using a sequence in this way because a sequence's auto-increment is not associated with a transaction at all so that the nextval of the sequence will be incremented even if the transaction containing the update to the department table is not committed for whatever reason.

The answer is No and you should not be relying on the Sequence at all.

 

[THoey]

Not to mention that this is what is called a calculated value and should not be stored in the table. If you are using this in reports, just count the number of members of that department... Terry