The question is published on by Tutorial Guruji team.
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 ?
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.