How do Triggers work in SQLite?

Explanation & Concept

SQLite triggers execute automated SQL actions `BEFORE` or `AFTER` an `INSERT`, `UPDATE`, or `DELETE` on a specified table. Special `NEW` and `OLD` record pseudotables provide access to changing row states.

Code Example

sql

CREATE TABLE product_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    updated_at TEXT
);

CREATE TRIGGER audit_price_updates
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN OLD.price != NEW.price
BEGIN
    INSERT INTO product_history (product_id, old_price, new_price, updated_at)
    VALUES (OLD.id, OLD.price, NEW.price, datetime('now'));
END;

How It Works & Key Takeaways

Whenever a product's price changes, this trigger automatically records the old and new prices into the audit log table.

Mock Test · 1 Questions · Optional

Ready for Views?

Take a quick test to evaluate yourself, or move straight to the next module.

Advanced2 questions

Skip

TriggersAdvanced

1Question 1 of 1

sql

How do Triggers work in SQLite?

Explanation & Concept

Code Example

sql

CREATE TABLE product_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    updated_at TEXT
);

CREATE TRIGGER audit_price_updates
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN OLD.price != NEW.price
BEGIN
    INSERT INTO product_history (product_id, old_price, new_price, updated_at)
    VALUES (OLD.id, OLD.price, NEW.price, datetime('now'));
END;

How It Works & Key Takeaways

Whenever a product's price changes, this trigger automatically records the old and new prices into the audit log table.

Mock Test · 1 Questions · Optional

Ready for Views?

Take a quick test to evaluate yourself, or move straight to the next module.

Advanced2 questions

Skip