SQLite is a lightweight, serverless database engine widely used in many applications. It provides a convenient way to handle and manage data without the need for a separate database server.

One powerful feature of SQLite is triggers, which allow you to automatically execute specific actions or operations when certain events occur in the database. This can be useful in scenarios where you need to enforce data integrity, implement complex business logic, or maintain certain constraints within the database.

In this blog post, we will explore how to work with SQLite triggers using Python. We will cover the following topics:

1. Setting up a SQLite database
2. Creating triggers with SQL statements
3. Executing triggers in Python

Setting up a SQLite database

To begin, let’s set up a SQLite database using Python. We’ll be using the sqlite3 module, which is included in the Python standard library.

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('example.db')

# Create a cursor object to execute SQL statements
cursor = conn.cursor()

# Create a table for demonstration
cursor.execute('''CREATE TABLE IF NOT EXISTS employees (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT,
                    salary REAL
                )''')

# Commit the changes and close the connection
conn.commit()
conn.close()

In the above code snippet, we create a new SQLite database file called example.db and connect to it. Then, we create a table named employees with a few columns. This will be the table on which we will create triggers.

Creating triggers with SQL statements

SQLite triggers are defined using SQL statements, and they can be associated with specific database events such as INSERT, UPDATE, or DELETE. Triggers can be defined to execute either before or after the event occurs.

Here’s an example of creating a simple trigger that updates a column whenever an INSERT operation is performed on the employees table:

import sqlite3

def create_trigger():
    # Connect to the SQLite database
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()

    # Create the trigger
    cursor.execute('''CREATE TRIGGER IF NOT EXISTS salary_update
                      AFTER INSERT ON employees
                      FOR EACH ROW
                      BEGIN
                          UPDATE employees
                          SET salary = salary * 1.1
                          WHERE id = NEW.id;
                      END''')

    # Commit the changes and close the connection
    conn.commit()
    conn.close()

In the above code, we define a function create_trigger() that creates a trigger named salary_update. This trigger is set to execute after every INSERT operation on the employees table. Inside the trigger, we update the salary column of the same row using the NEW keyword.

Executing triggers in Python

To execute the trigger we created in the previous step, we simply need to perform an INSERT operation on the employees table. Here’s an example of how you can do that:

import sqlite3

def insert_employee(name, salary):
    # Connect to the SQLite database
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()

    # Insert a new employee
    cursor.execute('''INSERT INTO employees (name, salary)
                      VALUES (?, ?)''', (name, salary))

    # Commit the changes and close the connection
    conn.commit()
    conn.close()

# Insert a new employee and trigger the salary_update trigger
insert_employee("John Doe", 5000.00)

In the above example, we define a function insert_employee() that inserts a new employee record into the employees table. When we call this function, it will trigger the salary_update trigger and update the salary by multiplying it with a factor of 1.1.

Conclusion

SQLite triggers in Python provide a powerful mechanism to automate actions or enforce constraints within the database. By combining the capabilities of SQLite and Python, you can easily handle various events and implement complex business logic.

In this blog post, we covered the basics of setting up a SQLite database, creating triggers using SQL statements, and executing those triggers in Python. This should give you a good starting point to explore and utilize triggers in your SQLite database applications.