SQLite Triggers: AFTER INSERT

In SQLite, a trigger is a set of actions that are automatically performed when a specified database event occurs. An AFTER INSERT trigger is executed after a row is inserted into the table.

Syntax

The syntax for creating an AFTER INSERT trigger in SQLite is as follows:

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
BEGIN
    -- Actions to be performed after a row is inserted
END;

Example

Suppose we have a table called users with columns for id, name, and age. We want to create a trigger that increments a counter in another table (stats) every time a new user is added.

CREATE TRIGGER update_stats
AFTER INSERT ON users
BEGIN
    UPDATE stats SET counter = counter + 1;
END;

Output

When a new row is inserted into the users table, the update_stats trigger is executed, and the counter column in the stats table is incremented by 1.

Explanation

In the example above, we create an AFTER INSERT trigger called update_stats that is executed after a new row is inserted into the users table. The trigger then executes an SQL statement that updates the counter column in the stats table, incrementing its value by 1.

Use

AFTER INSERT triggers can be used to perform a variety of actions after a new row is inserted into a table. Some common uses of triggers include logging changes, enforcing referential integrity, and updating summary tables.

Important Points

• Trigger names must be unique for a given table.
• Use AFTER INSERT triggers with caution, as they can incur overhead on the database server.
• Be careful not to create recursive triggers that can cause an infinite loop of trigger execution.
• Triggers can be used to enforce complex business rules and keep data consistent across multiple tables.

Summary

In this tutorial, we learned about AFTER INSERT triggers in SQLite and how to create them to perform actions after a new row is inserted into a table. We saw an example of how to use a trigger to update a counter in another table every time a new user is added. When using triggers, it's important to be aware of their potential impact on performance and to use them judiciously.