How to Automatically Update a Field in SQLite After Inserting Into Another Table

Learn how to easily set up triggers in SQLite to automatically update the number of enrolled students in a course when a new person is added to the database.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQLite, Automatically update field in table after insert in another table

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Automatically Update a Field in SQLite After Inserting Into Another Table

Managing databases can be quite daunting, especially if you are just starting out. One common scenario that many beginners encounter is the need to automatically update certain fields in their tables based on actions taken in another table. In this post, we'll explore how you can achieve this in SQLite by using triggers.

The Problem

Imagine you have two tables in your SQLite database: courses and people. Here's a brief overview of their structure:

courses table contains:

id: Unique identifier for each course

name: Name of the course

total: Total number of people enrolled in the course

people table contains:

id: Unique identifier for each person

name: Name of the person

course_id: Foreign key connecting the person to their respective course

The goal is to automatically update the total field in the courses table whenever a new entry is made in the people table. This way, you can easily keep track of how many students are enrolled in each course without having to manually update the number each time.

The Solution: Using Triggers

To achieve this automation, SQLite allows us to create triggers, which are essentially instructions that are automatically executed in response to certain events in the database, such as INSERT operations. Here’s a step-by-step guide on how to set this up.

Step 1: Create the Trigger

Open your SQLite database.

Use the following SQL command to create an AFTER INSERT trigger for the people table.

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Break Down the Code

CREATE TRIGGER trg_people_ins: This command starts the creation of a new trigger named trg_people_ins.

AFTER INSERT ON people: This specifies that the trigger will be activated right after an insert operation occurs in the people table.

BEGIN / END: This block contains the actions that the trigger will perform.

UPDATE courses: This command updates the courses table. It specifies which table to modify.

SET total = COALESCE(total, 0) + 1: This line updates the total field. The COALESCE function is used to treat null values as 0, ensuring that the total is incremented properly.

WHERE id = NEW.course_id: This ensures that we are only updating the total for the course that the new person is enrolled in.

Step 3: Adjust the Code if Necessary

If the total field in your courses table always defaults to 0 and will never have null values, you can simplify the update statement like this:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Using triggers in SQLite allows for seamless management of data between related tables. By setting up an AFTER INSERT trigger, you not only save time but also minimize the chance of errors from manual updates.

This method is particularly useful for educational purposes or any applications managing users and courses, helping you maintain accurate counts without extra effort!

If you're querying from JavaScript, you can simply execute the INSERT statement for new people, and the trigger will take care of updating the course totals automatically, making your workflow smoother and more efficient.

Now you can confidently proceed to implement this in your database projects! If you have further questions about SQLite or triggers, feel free to ask.