SQLite CHANGES() Function

The SQLite changes() function returns the number of database rows that were changed, inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers.

Basically, it allows us to see how many rows are affected when we run any of those statements.

Syntax

The syntax goes like this:

changes()

So no arguments are required or accepted.

Example

Suppose we have the following table:

SELECT * FROM Dogs;

Result:

DogId  DogName
-----  -------
1      Yelp   
2      Woofer 
3      Fluff  

Update

Let’s update the name of one of those dogs:

UPDATE Dogs 
SET DogName = "Fluffy" 
WHERE DogId = 3;

Now let’s run the changes() function to see how many rows were changed:

SELECT changes();

Result:

changes()
---------
1        

As expected, it tells us that one row was changed.

Insert

Now let’s do some inserts:

INSERT INTO Dogs VALUES 
  ( 4, "Bark" ),
  ( 5, "Wag" ),
  ( 6, "Bite" );

Now let’s run the changes() function to see how many rows were changed:

SELECT changes();

Result:

changes()
---------
3        

As expected, it tells us that three rows were changed.

Let’s take a quick look at our table now:

SELECT * FROM Dogs;

Result:

DogId  DogName
-----  -------
1      Yelp   
2      Woofer 
3      Fluffy 
4      Bark   
5      Wag    
6      Bite   

We can see the changes that we have done so far.

Note that we can run changes() again, and it will still return information based on the last INSERT, UPDATE, or DELETE statement, even though we’ve run a SELECT statement since running our INSERT statement:

SELECT changes();

Result:

changes()
---------
3        

Delete

Now let’s delete some rows:

DELETE FROM Dogs 
WHERE DogId IN ( 4, 6 );

Now let’s run the changes() function to see how many rows were changed:

SELECT changes();

Result:

changes()
---------
2        

As expected, it tells us that two rows were changed.

More Information

The changes() SQL function is a wrapper around the sqlite3_changes64() C/C++ function and therefore follows the same rules for counting changes.

Only changes made directly by the INSERT, UPDATE, or DELETE statement are considered. Auxiliary changes caused by triggers, foreign key actions or REPLACE constraint resolution are not counted.

See the SQLite documentation for the sqlite3_changes64() C/C++ function for more information.