Skip to content

Instantly share code, notes, and snippets.

@simonw
Last active February 27, 2024 19:20
Show Gist options
  • Save simonw/7f7bf70f4732f5952ab39059d8c069e7 to your computer and use it in GitHub Desktop.
Save simonw/7f7bf70f4732f5952ab39059d8c069e7 to your computer and use it in GitHub Desktop.
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()
@simonw
Copy link
Author

simonw commented Feb 27, 2024

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"}')

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment