Research for https://github.com/simonw/sqlite-chronicle/issues/7

demo_triggers.py

import sqlite3

# Connect to the test.db database
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# Create the news table
cursor.execute(
    """
CREATE TABLE IF NOT EXISTS news (
    id INTEGER PRIMARY KEY,
    headline TEXT,
    date TEXT
);
"""
)

# Create the log table
cursor.execute(
    """
CREATE TABLE IF NOT EXISTS log (
    id INTEGER PRIMARY KEY,
    trigger_name TEXT,
    old_row TEXT,
    new_row TEXT
);
"""
)

# Define the triggers
triggers = [
    # BEFORE INSERT
    """
    CREATE TRIGGER before_insert_news
    BEFORE INSERT ON news
    BEGIN
        INSERT INTO log (trigger_name, old_row, new_row)
        VALUES ('before_insert', NULL, json_object('id', NEW.id, 'headline', NEW.headline, 'date', NEW.date));
    END;
    """,
    # AFTER INSERT
    """
    CREATE TRIGGER after_insert_news
    AFTER INSERT ON news
    BEGIN
        INSERT INTO log (trigger_name, old_row, new_row)
        VALUES ('after_insert', NULL, json_object('id', NEW.id, 'headline', NEW.headline, 'date', NEW.date));
    END;
    """,
    # BEFORE UPDATE
    """
    CREATE TRIGGER before_update_news
    BEFORE UPDATE ON news
    BEGIN
        INSERT INTO log (trigger_name, old_row, new_row)
        VALUES ('before_update', json_object('id', OLD.id, 'headline', OLD.headline, 'date', OLD.date), json_object('id', NEW.id, 'headline', NEW.headline, 'date', NEW.date));
    END;
    """,
    # AFTER UPDATE
    """
    CREATE TRIGGER after_update_news
    AFTER UPDATE ON news
    BEGIN
        INSERT INTO log (trigger_name, old_row, new_row)
        VALUES ('after_update', json_object('id', OLD.id, 'headline', OLD.headline, 'date', OLD.date), json_object('id', NEW.id, 'headline', NEW.headline, 'date', NEW.date));
    END;
    """,
    # BEFORE DELETE
    """
    CREATE TRIGGER before_delete_news
    BEFORE DELETE ON news
    BEGIN
        INSERT INTO log (trigger_name, old_row, new_row)
        VALUES ('before_delete', json_object('id', OLD.id, 'headline', OLD.headline, 'date', OLD.date), NULL);
    END;
    """,
    # AFTER DELETE
    """
    CREATE TRIGGER after_delete_news
    AFTER DELETE ON news
    BEGIN
        INSERT INTO log (trigger_name, old_row, new_row)
        VALUES ('after_delete', json_object('id', OLD.id, 'headline', OLD.headline, 'date', OLD.date), NULL);
    END;
    """,
]

# Create the triggers
for trigger in triggers:
    cursor.execute(trigger)

# Commit the changes
conn.commit()

# Test the triggers
test_queries = [
    "INSERT INTO news (id, headline, date) VALUES (1, 'Breaking News', '2024-02-27');",
    "UPDATE news SET headline = 'Updated News' WHERE id = 1;",
    "DELETE FROM news WHERE id = 1;",
    # This updates in place
    "INSERT OR REPLACE INTO news (id, headline, date) VALUES (1, 'Replaced News', '2024-02-28');",
    # This updates in place but makes no changes
    "INSERT OR REPLACE INTO news (id, headline, date) VALUES (1, 'Replaced News', '2024-02-28');",
    # This inserts a new row
    "INSERT OR REPLACE INTO news (id, headline, date) VALUES (2, 'Insert-or-replace inserted', '2024-02-28');",
]

for query in test_queries:
    print(query)
    before_max_id = cursor.execute("SELECT MAX(id) FROM log;").fetchone()[0]
    with conn:
        cursor.execute(query)
    # Show new log entries
    if before_max_id:
        log_sql = "SELECT * FROM log WHERE id > ?;"
        log_args = (before_max_id,)
    else:
        log_sql = "SELECT * FROM log;"
        log_args = ()
    log_entries = cursor.execute(log_sql, log_args)
    for entry in log_entries:
        print('  ', entry)

conn.close()

Output:

INSERT INTO news (id, headline, date) VALUES (1, 'Breaking News', '2024-02-27');
   (1, 'before_insert', None, '{"id":1,"headline":"Breaking News","date":"2024-02-27"}')
   (2, 'after_insert', None, '{"id":1,"headline":"Breaking News","date":"2024-02-27"}')
UPDATE news SET headline = 'Updated News' WHERE id = 1;
   (3, 'before_update', '{"id":1,"headline":"Breaking News","date":"2024-02-27"}', '{"id":1,"headline":"Updated News","date":"2024-02-27"}')
   (4, 'after_update', '{"id":1,"headline":"Breaking News","date":"2024-02-27"}', '{"id":1,"headline":"Updated News","date":"2024-02-27"}')
DELETE FROM news WHERE id = 1;
   (5, 'before_delete', '{"id":1,"headline":"Updated News","date":"2024-02-27"}', None)
   (6, 'after_delete', '{"id":1,"headline":"Updated News","date":"2024-02-27"}', None)
INSERT OR REPLACE INTO news (id, headline, date) VALUES (1, 'Replaced News', '2024-02-28');
   (7, 'before_insert', None, '{"id":1,"headline":"Replaced News","date":"2024-02-28"}')
   (8, 'after_insert', None, '{"id":1,"headline":"Replaced News","date":"2024-02-28"}')
INSERT OR REPLACE INTO news (id, headline, date) VALUES (1, 'Replaced News', '2024-02-28');
   (9, 'before_insert', None, '{"id":1,"headline":"Replaced News","date":"2024-02-28"}')
   (10, 'after_insert', None, '{"id":1,"headline":"Replaced News","date":"2024-02-28"}')
INSERT OR REPLACE INTO news (id, headline, date) VALUES (2, 'Insert-or-replace inserted', '2024-02-28');
   (11, 'before_insert', None, '{"id":2,"headline":"Insert-or-replace inserted","date":"2024-02-28"}')
   (12, 'after_insert', None, '{"id":2,"headline":"Insert-or-replace inserted","date":"2024-02-28"}')