SQLite 触发器（Trigger）

在 SQLite 中，触发器（Trigger） 是一种特殊的存储过程，自动在指定表上执行 INSERT、UPDATE 或 DELETE 操作时触发。常用于：

• 数据完整性校验
• 自动更新相关表
• 记录操作日志
• 实现复杂业务逻辑

---

一、基本语法

CREATE [TEMP] TRIGGER [IF NOT EXISTS] 触发器名
{BEFORE | AFTER | INSTEAD OF} {DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW]
[WHEN 条件]
BEGIN
    -- SQL 语句
END;

---

二、关键说明

关键字	说明
BEFORE	操作前执行（可阻止操作）
AFTER	操作后执行
INSTEAD OF	用于视图（替代原始操作）
FOR EACH ROW	每行触发一次（SQLite 默认）
WHEN	条件判断，只在满足时触发

---

三、特殊对象（仅在触发器内可用）

对象	含义
NEW	新插入或更新后的行数据
OLD	删除或更新前的行数据
NEW.列名 / OLD.列名	访问具体字段

---

四、常见示例

1. 自动更新时间戳

CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
FOR EACH ROW
WHEN NEW.updated_at = OLD.updated_at  -- 只有时间没变才更新
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.id;
END;

---

2. 防止删除管理员

CREATE TRIGGER prevent_delete_admin
BEFORE DELETE ON users
FOR EACH ROW
WHEN OLD.role = 'admin'
BEGIN
    SELECT RAISE(ABORT, 'Cannot delete admin user!');
END;

---

3. 插入日志记录

-- 创建日志表
CREATE TABLE user_log (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    action TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 触发器：记录每次插入
CREATE TRIGGER log_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_log (user_id, action)
    VALUES (NEW.id, 'INSERT');
END;

---

4. 库存自动扣减（订单插入时）

CREATE TRIGGER deduct_stock
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;

    -- 检查库存负值
    UPDATE products
    SET stock = 0
    WHERE id = NEW.product_id AND stock < 0;
END;

---

5. INSTEAD OF 触发器（用于视图）

CREATE VIEW user_view AS
SELECT id, name, email FROM users;

CREATE TRIGGER instead_of_insert_user
INSTEAD OF INSERT ON user_view
FOR EACH ROW
BEGIN
    INSERT INTO users (name, email) VALUES (NEW.name, NEW.email);
END;

---

五、查看与删除触发器

-- 查看所有触发器
PRAGMA trigger_list;

-- 或
SELECT name, sql FROM sqlite_master WHERE type = 'trigger';

-- 删除触发器
DROP TRIGGER IF EXISTS 触发器名;

---

六、注意事项

注意点	说明
不能直接回滚事务	只能用 RAISE() 抛异常中止
递归触发	默认允许，可用 PRAGMA recursive_triggers = ON/OFF; 控制
性能影响	触发器会增加 DML 操作开销
调试困难	错误信息不明显，建议用 RAISE() 提示

---

七、RAISE() 函数（抛异常）

RAISE(ABORT, '错误消息')    -- 中止操作
RAISE(IGNORE)              -- 跳过当前行
RAISE(ROLLBACK, '消息')    -- 回滚事务（需在事务中）

---

总结：触发器适用场景

场景	推荐使用触发器
自动维护审计日志	✅
级联更新/删除	✅（也可使用外键）
复杂校验逻辑	⚠️（建议用应用层）
替代视图写操作	✅（INSTEAD OF）

---

需要我为你生成一个完整的 用户操作审计系统（含表 + 触发器）示例吗？