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

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Is using triggers a good practice for updating count or timestamp fields? without wasting too much if your time.

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 ?

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.

We are here to answer your question about Is using triggers a good practice for updating count or timestamp fields? - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji