SQLite: How to Properly Use a Trigger to Log Changes in a Table Without Creating Infinite Loops?
I'm stuck trying to I'm upgrading from an older version and I'm working on a SQLite database where I need to create a trigger that logs changes made to a specific table. The goal is to maintain a history of updates in a separate log table. However, I'm encountering an issue where the trigger seems to execute infinitely, causing a loop that affects performance and results in too many entries in the log table. Here's what I have so far for my trigger: ```sql CREATE TABLE IF NOT EXISTS my_table ( id INTEGER PRIMARY KEY, name TEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS my_table_log ( log_id INTEGER PRIMARY KEY, my_table_id INTEGER, old_name TEXT, new_name TEXT, change_time DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TRIGGER log_my_table_changes AFTER UPDATE ON my_table FOR EACH ROW BEGIN INSERT INTO my_table_log (my_table_id, old_name, new_name) VALUES (OLD.id, OLD.name, NEW.name); END; ``` The trigger is set to log changes made in `my_table` whenever an update occurs. However, when I perform an update, it appears to be firing multiple times. For instance, when I execute the following update command: ```sql UPDATE my_table SET name = 'New Name' WHERE id = 1; ``` I get multiple entries in `my_table_log` for the same change, which I didn't expect. I'm not doing any further updates on the `updated_at` field within the trigger itself, so I'm puzzled about why it keeps triggering. I also checked that there are no other triggers or procedures related to `my_table` that could be causing this behavior. Could it be possible that the way I'm logging the data is causing an infinite loop, or is there something else I might be missing in the trigger logic? Any insights or recommendations for best practices to avoid this kind of issue would be greatly appreciated. This is for a desktop app running on Debian. The stack includes Sql and several other technologies. Any pointers in the right direction? For context: I'm using Sql on CentOS. Thanks for any help you can provide!