Using Triggers for Automatic Data Integrity

Implement database-level triggers to maintain relationships and audit logs automatically.

In this lesson, you will learn how to use database triggers to enforce complex data integrity rules and maintain automated audit trails directly within your embedded database schema.

Why Use Triggers?

While FOREIGN KEY constraints handle basic relationship integrity, they cannot handle complex business logic or logging. In embedded environments like SQLite, triggers act as "automated listeners" that execute SQL statements whenever a specific operation—INSERT, UPDATE, or DELETE—occurs on a table. Because the logic lives inside the database file itself, it remains consistent regardless of whether you access the data through a C++ application, a Python script, or a command-line tool.

Implementing an Audit Log

Imagine you want to track changes to a sensitive_configs table without writing audit code in every single application function. You can create an audit_log table and use a trigger to populate it automatically:

-- The audit table
CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    old_value TEXT
);

-- The trigger that watches for updates
CREATE TRIGGER log_config_changes
AFTER UPDATE ON sensitive_configs
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (old_value) 
    VALUES (OLD.value);
END;

Ensuring Data Integrity

Triggers can also prevent invalid data from being saved. For example, if you need to ensure that a product_price cannot be lowered by more than 20% in a single update, you can use a BEFORE UPDATE trigger:

CREATE TRIGGER prevent_sharp_price_drop
BEFORE UPDATE ON products
FOR EACH ROW
WHEN NEW.price < (OLD.price * 0.8)
BEGIN
    SELECT RAISE(ABORT, 'Price reduction exceeds 20% limit.');
END;

By utilizing the RAISE function, the database rejects the transaction entirely if your business rule is violated. This keeps your application layer clean, as the database takes full responsibility for rejecting forbidden changes, ensuring the integrity of your embedded storage even if the application code contains bugs.

Key Takeaway: Triggers provide a robust, centralized mechanism to enforce complex business constraints and maintain audit trails, ensuring data integrity remains intact regardless of the external application logic.