如何在SQLite中记录数据表（CREATE/UPDATE/DELETE）的字段变更

针对需求，我们可以通过设计日志表+创建触发器的方式，自动记录products表的INSERT、UPDATE、DELETE操作及字段变更细节。

1. 完善变更日志表结构 #

首先补全product_changes_log的表结构，包含日志标识、操作类型、操作时间、关联用户、变更对象、字段变更详情：

CREATE TABLE IF NOT EXISTS product_changes_log (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    operation_type TEXT NOT NULL CHECK(operation_type IN ('INSERT', 'UPDATE', 'DELETE')),
    operation_time DATETIME DEFAULT (datetime('now','localtime')),
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    changed_field TEXT NOT NULL,
    old_value TEXT,
    new_value TEXT
);

字段说明：

• log_id：日志唯一标识，自增主键
• operation_type：明确记录是插入/更新/删除操作，通过CHECK约束限制可选值
• operation_time：自动记录操作发生的本地时间
• user_id：记录执行该操作的用户ID（关联原表的user_id）
• product_id：关联被操作的产品ID
• changed_field：记录发生变更的字段名
• old_value：字段变更前的值（INSERT时为NULL，DELETE时为原字段值）
• new_value：字段变更后的值（DELETE时为NULL，INSERT时为新增字段值）

2. 创建INSERT操作触发器 #

当向products插入新数据时，自动记录所有字段的新增值：

CREATE TRIGGER IF NOT EXISTS trigger_products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    -- 记录name字段
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('INSERT', NEW.user_id, NEW.id, 'name', NULL, NEW.name);
    -- 记录barcode字段
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('INSERT', NEW.user_id, NEW.id, 'barcode', NULL, NEW.barcode);
    -- 记录category_id字段
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('INSERT', NEW.user_id, NEW.id, 'category_id', NULL, NEW.category_id);
    -- 记录unit_id字段
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('INSERT', NEW.user_id, NEW.id, 'unit_id', NULL, NEW.unit_id);
    -- 记录count字段
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('INSERT', NEW.user_id, NEW.id, 'count', NULL, NEW.count);
    -- 记录purchase_price字段
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('INSERT', NEW.user_id, NEW.id, 'purchase_price', NULL, NEW.purchase_price);
    -- 记录extra_charge字段
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('INSERT', NEW.user_id, NEW.id, 'extra_charge', NULL, NEW.extra_charge);
    -- 记录selling_price字段
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('INSERT', NEW.user_id, NEW.id, 'selling_price', NULL, NEW.selling_price);
    -- 记录created_at字段
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('INSERT', NEW.user_id, NEW.id, 'created_at', NULL, NEW.created_at);
    -- 记录update_at字段
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('INSERT', NEW.user_id, NEW.id, 'update_at', NULL, NEW.update_at);
END;

3. 创建UPDATE操作触发器 #

当更新products数据时，仅记录实际发生变化的字段：

CREATE TRIGGER IF NOT EXISTS trigger_products_after_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    -- 仅当name变化时记录
    IF OLD.name != NEW.name THEN
        INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
        VALUES ('UPDATE', NEW.user_id, NEW.id, 'name', OLD.name, NEW.name);
    END IF;
    -- 仅当barcode变化时记录
    IF OLD.barcode != NEW.barcode THEN
        INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
        VALUES ('UPDATE', NEW.user_id, NEW.id, 'barcode', OLD.barcode, NEW.barcode);
    END IF;
    -- 仅当category_id变化时记录
    IF OLD.category_id != NEW.category_id THEN
        INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
        VALUES ('UPDATE', NEW.user_id, NEW.id, 'category_id', OLD.category_id, NEW.category_id);
    END IF;
    -- 仅当unit_id变化时记录
    IF OLD.unit_id != NEW.unit_id THEN
        INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
        VALUES ('UPDATE', NEW.user_id, NEW.id, 'unit_id', OLD.unit_id, NEW.unit_id);
    END IF;
    -- 仅当count变化时记录
    IF OLD.count != NEW.count THEN
        INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
        VALUES ('UPDATE', NEW.user_id, NEW.id, 'count', OLD.count, NEW.count);
    END IF;
    -- 仅当purchase_price变化时记录
    IF OLD.purchase_price != NEW.purchase_price THEN
        INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
        VALUES ('UPDATE', NEW.user_id, NEW.id, 'purchase_price', OLD.purchase_price, NEW.purchase_price);
    END IF;
    -- 仅当extra_charge变化时记录
    IF OLD.extra_charge != NEW.extra_charge THEN
        INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
        VALUES ('UPDATE', NEW.user_id, NEW.id, 'extra_charge', OLD.extra_charge, NEW.extra_charge);
    END IF;
    -- 仅当selling_price变化时记录
    IF OLD.selling_price != NEW.selling_price THEN
        INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
        VALUES ('UPDATE', NEW.user_id, NEW.id, 'selling_price', OLD.selling_price, NEW.selling_price);
    END IF;
    -- 仅当update_at变化时记录（如果你的代码会主动更新这个字段）
    IF OLD.update_at != NEW.update_at THEN
        INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
        VALUES ('UPDATE', NEW.user_id, NEW.id, 'update_at', OLD.update_at, NEW.update_at);
    END IF;
END;

注：如果业务中update_at字段需要自动刷新，可以额外添加BEFORE UPDATE触发器，确保每次更新都同步更新该字段。

4. 创建DELETE操作触发器 #

当删除products数据时，记录所有字段的原始值：

CREATE TRIGGER IF NOT EXISTS trigger_products_after_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
    -- 记录name字段原始值
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('DELETE', OLD.user_id, OLD.id, 'name', OLD.name, NULL);
    -- 记录barcode字段原始值
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('DELETE', OLD.user_id, OLD.id, 'barcode', OLD.barcode, NULL);
    -- 记录category_id字段原始值
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('DELETE', OLD.user_id, OLD.id, 'category_id', OLD.category_id, NULL);
    -- 记录unit_id字段原始值
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('DELETE', OLD.user_id, OLD.id, 'unit_id', OLD.unit_id, NULL);
    -- 记录count字段原始值
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('DELETE', OLD.user_id, OLD.id, 'count', OLD.count, NULL);
    -- 记录purchase_price字段原始值
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('DELETE', OLD.user_id, OLD.id, 'purchase_price', OLD.purchase_price, NULL);
    -- 记录extra_charge字段原始值
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('DELETE', OLD.user_id, OLD.id, 'extra_charge', OLD.extra_charge, NULL);
    -- 记录selling_price字段原始值
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('DELETE', OLD.user_id, OLD.id, 'selling_price', OLD.selling_price, NULL);
    -- 记录created_at字段原始值
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('DELETE', OLD.user_id, OLD.id, 'created_at', OLD.created_at, NULL);
    -- 记录update_at字段原始值
    INSERT INTO product_changes_log (operation_type, user_id, product_id, changed_field, old_value, new_value)
    VALUES ('DELETE', OLD.user_id, OLD.id, 'update_at', OLD.update_at, NULL);
END;

使用说明 #

• 上述触发器会在执行INSERT/UPDATE/DELETE操作时自动写入日志，无需手动调用
• 可通过查询product_changes_log表追踪任意产品的变更历史，例如：

  SELECT * FROM product_changes_log WHERE product_id = 1 ORDER BY operation_time DESC;
  

  该语句会列出ID为1的产品所有操作记录，按时间倒序排列。

内容的提问来源于stack exchange，提问作者Nurbek Uzakov