Is using triggers a good practice for updating count or timestamp fields? Code Answer

I have two tables (simplified):

TABLE batch
batch_id      uuid
student_count smallint

TABLE students
batch_id    uuid
student_id  uuid

So I want to update student_count(add 1) whenever a student is inserted into the database. My question is should I use a trigger to update the student_count or use a separate query to update student_count. (My database and server are not on same machine.)

I cannot use a before trigger since insertion may fail.(unless I use a transaction which I want to avoid if I am using triggers.)

so basically this is a good practice ?

Answer

For tasks like that it is best to use a trigger, precisely because the trigger is guaranteed to run, and it automatically runs in the same transaction as the triggering statement. That is a good thing, because it means that either both operations will succeed or both will fail (atomicity), which is precisely what you want in such a case.

The best solution, however, might be not to store the redundant student_count at all, but to calculate it on the fly when you SELECT from the tables. Persisting redundant data is only a good solution if calculating it when you need it would be intolerable from a performance perspective.