Small. Fast. Reliable.
Choose any three.

SQLite C Interface

Count The Number Of Rows Modified

int sqlite3_changes(sqlite3*);

This function returns the number of database rows that were changed or inserted or deleted by the most recently completed SQL statement on the database connection specified by the first parameter. Only changes that are directly specified by the INSERT, UPDATE, or DELETE statement are counted. Auxiliary changes caused by triggers are not counted. Use the sqlite3_total_changes() function to find the total number of changes including changes caused by triggers.

A "row change" is a change to a single row of a single table caused by an INSERT, DELETE, or UPDATE statement. Rows that are changed as side effects of REPLACE constraint resolution, rollback, ABORT processing, DROP TABLE, or by any other mechanisms do not count as direct row changes.

A "trigger context" is a scope of execution that begins and ends with the script of a trigger. Most SQL statements are evaluated outside of any trigger. This is the "top level" trigger context. If a trigger fires from the top level, a new trigger context is entered for the duration of that one trigger. Subtriggers create subcontexts for their duration.

Calling sqlite3_exec() or sqlite3_step() recursively does not create a new trigger context.

This function returns the number of direct row changes in the most recent INSERT, UPDATE, or DELETE statement within the same trigger context.

Thus, when called from the top level, this function returns the number of changes in the most recent INSERT, UPDATE, or DELETE that also occurred at the top level. Within the body of a trigger, the sqlite3_changes() interface can be called to find the number of changes in the most recently completed INSERT, UPDATE, or DELETE statement within the body of the same trigger. However, the number returned does not include changes caused by subtriggers since those have their own context.

SQLite implements the command "DELETE FROM table" without a WHERE clause by dropping and recreating the table. Doing so is much faster than going through and deleting individual elements from the table. Because of this optimization, the deletions in "DELETE FROM table" are not row changes and will not be counted by the sqlite3_changes() or sqlite3_total_changes() functions, regardless of the number of elements that were originally in the table. To get an accurate count of the number of rows deleted, use "DELETE FROM table WHERE 1" instead. Or recompile using the SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time option to disable the optimization on all queries.

Invariants:

H12241 The sqlite3_changes() function shall return the number of row changes caused by the most recent INSERT, UPDATE, or DELETE statement on the same database connection and within the same or higher trigger context, or zero if there have not been any qualifying row changes.
H12243 Statements of the form "DELETE FROM tablename" with no WHERE clause shall cause subsequent calls to sqlite3_changes() to return zero, regardless of the number of rows originally in the table.

Assumptions:

A12252 If a separate thread makes changes on the same database connection while sqlite3_changes() is running then the value returned is unpredictable and not meaningful.

See also lists of Objects, Constants, and Functions.


This page last modified 2008/12/09 18:44:04 UTC