SQLite is a popular, lightweight, SQL-based relational database engine, frequently used in applications where a full database management system isn’t necessary. Among its various features, SQLite offers built-in functions to track the number of changes made to the database during operations, and one of these functions is total_changes().

In this article, we’ll discuss what the total_changes() function does, how it can be used, and provide examples to illustrate its functionality.

What is the total_changes() Function?

The total_changes() function in SQLite returns the cumulative number of rows that have been inserted, updated, or deleted since the database connection was opened. This count accumulates across all SQL commands executed within the current session, providing a snapshot of the modifications made up to that point.

The function is particularly useful for monitoring activity within a database, debugging applications, and keeping track of changes across different operations.

Key points:

• Accumulative Count: total_changes() counts all changes since the database connection was first established.
• Session-Specific: It only counts changes for the current session or connection, so if multiple connections to the database are open, each connection will have its own total_changes() count.
• Read-Only Database Calls: If the database is accessed in a read-only mode, total_changes() will always return 0 because no modifications are made.

Syntax

The syntax for total_changes() is straightforward, as it doesn’t require (or accept) any arguments:

total_changes()

Usage

To call this function, simply include it in a SQL query within the session where you want to track changes. It will return the number of row changes caused by INSERT, UPDATE or DELETE statements since the current database connection was opened.

Example

Let’s walk through an example to illustrate how total_changes() works. Suppose we have a database with a table named employees:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    position TEXT,
    salary REAL
);

Step 1: Open a Database Connection

First, we open a connection to the SQLite database, which initializes the total_changes() counter to 0 for this session.

Step 2: Insert Data into the Table

Now, we’ll insert some rows into the employees table:

INSERT INTO employees (name, position, salary) VALUES ('Marge', 'Manager', 75000);
INSERT INTO employees (name, position, salary) VALUES ('Homer', 'Developer', 60000);

Each of these INSERT statements adds one row to the database, so after running both statements, total_changes() should report a total of 2 changes.

Step 3: Check total_changes()

To retrieve the total number of changes made so far, we use:

SELECT total_changes();

Output:

+-----------------+
| total_changes() |
+-----------------+
| 2               |
+-----------------+

The output confirms that two rows have been modified since the connection was opened.

Step 4: Update Rows in the Table

Next, let’s update the salary of one of the employees:

UPDATE employees SET salary = 80000 WHERE name = 'Marge';

After this update, the number of changes should increase by one because we modified a single row.

SELECT total_changes();

Output:

+-----------------+
| total_changes() |
+-----------------+
| 3               |
+-----------------+

As expected, it shows that three rows have been changed since we first connected.

Step 5: Delete Rows from the Table

Finally, we can delete a row:

DELETE FROM employees WHERE name = 'Homer';

After the deletion, the count should increase by one more.

SELECT total_changes();

Output:

+-----------------+
| total_changes() |
+-----------------+
| 4               |
+-----------------+

This sequence of actions demonstrates that total_changes() provides an ongoing tally of all rows inserted, updated, or deleted within the current session.

Summary

The total_changes() function is a handy tool in SQLite for tracking all database modifications within a session. By returning the cumulative count of rows inserted, updated, and deleted, it enables developers to monitor activity, validate operations, and troubleshoot potential issues.