SQLite触发器 #

-- 触发器是自动执行的SQL代码
-- 当特定事件发生时自动触发

-- 触发器特点：
-- 1. 自动执行
-- 2. 与表关联
-- 3. 在特定事件前后执行
-- 4. 可以访问NEW和OLD行数据

SQLite 触发器类型：
├── BEFORE 触发器
│   ├── BEFORE INSERT
│   ├── BEFORE UPDATE
│   └── BEFORE DELETE
├── AFTER 触发器
│   ├── AFTER INSERT
│   ├── AFTER UPDATE
│   └── AFTER DELETE
└── INSTEAD OF 触发器（用于视图）
    ├── INSTEAD OF INSERT
    ├── INSTEAD OF UPDATE
    └── INSTEAD OF DELETE

-- CREATE TRIGGER 基本语法
CREATE TRIGGER trigger_name
[BEFORE | AFTER | INSTEAD OF]
[INSERT | UPDATE | DELETE]
ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
    SQL statements;
END;

-- 示例表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE users_log (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    action TEXT,
    action_time DATETIME
);

-- 插入后记录日志
CREATE TRIGGER trg_users_insert_log
AFTER INSERT ON users
BEGIN
    INSERT INTO users_log (user_id, action, action_time)
    VALUES (NEW.id, 'INSERT', datetime('now'));
END;

-- 测试
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

-- 查看日志
SELECT * FROM users_log;

-- 更新后记录日志
CREATE TRIGGER trg_users_update_log
AFTER UPDATE ON users
BEGIN
    INSERT INTO users_log (user_id, action, action_time)
    VALUES (NEW.id, 'UPDATE', datetime('now'));
END;

-- 更新时自动记录
UPDATE users SET name = 'John Updated' WHERE id = 1;

-- 删除后记录日志
CREATE TRIGGER trg_users_delete_log
AFTER DELETE ON users
BEGIN
    INSERT INTO users_log (user_id, action, action_time)
    VALUES (OLD.id, 'DELETE', datetime('now'));
END;

-- 删除时自动记录
DELETE FROM users WHERE id = 1;

-- 插入前设置时间戳
CREATE TRIGGER trg_users_set_timestamp
BEFORE INSERT ON users
BEGIN
    SELECT RAISE(IGNORE) WHERE NEW.email IS NULL;
    -- 或设置默认值
    SELECT 
        COALESCE(NEW.created_at, datetime('now')),
        COALESCE(NEW.updated_at, datetime('now'));
END;

-- 更新前更新时间戳
CREATE TRIGGER trg_users_update_timestamp
BEFORE UPDATE ON users
BEGIN
    UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
    SELECT RAISE(IGNORE);  -- 阻止原始更新
END;

-- NEW：新插入或更新后的行
-- 用于 INSERT 和 UPDATE 触发器

CREATE TRIGGER trg_validate_email
BEFORE INSERT ON users
BEGIN
    SELECT RAISE(ABORT, 'Invalid email format')
    WHERE NEW.email NOT LIKE '%@%.%';
END;

-- 使用NEW值
CREATE TRIGGER trg_copy_email
AFTER INSERT ON users
BEGIN
    INSERT INTO email_list (email) VALUES (NEW.email);
END;

-- OLD：更新前或删除前的行
-- 用于 UPDATE 和 DELETE 触发器

CREATE TRIGGER trg_archive_deleted
BEFORE DELETE ON users
BEGIN
    INSERT INTO users_archive (id, name, email, deleted_at)
    VALUES (OLD.id, OLD.name, OLD.email, datetime('now'));
END;

-- 使用OLD值
CREATE TRIGGER trg_log_changes
AFTER UPDATE ON users
BEGIN
    INSERT INTO change_log (user_id, old_name, new_name)
    VALUES (OLD.id, OLD.name, NEW.name);
END;

-- 只在特定条件下触发
CREATE TRIGGER trg_log_high_salary
AFTER INSERT ON employees
WHEN NEW.salary > 100000
BEGIN
    INSERT INTO high_salary_log (employee_id, salary)
    VALUES (NEW.id, NEW.salary);
END;

-- 更新时条件
CREATE TRIGGER trg_log_status_change
AFTER UPDATE ON orders
WHEN OLD.status != NEW.status
BEGIN
    INSERT INTO order_status_log (order_id, old_status, new_status, changed_at)
    VALUES (NEW.id, OLD.status, NEW.status, datetime('now'));
END;

-- 创建视图
CREATE VIEW user_summary AS
SELECT id, name, email FROM users;

-- INSTEAD OF INSERT触发器
CREATE TRIGGER trg_user_summary_insert
INSTEAD OF INSERT ON user_summary
BEGIN
    INSERT INTO users (name, email)
    VALUES (NEW.name, NEW.email);
END;

-- INSTEAD OF UPDATE触发器
CREATE TRIGGER trg_user_summary_update
INSTEAD OF UPDATE ON user_summary
BEGIN
    UPDATE users 
    SET name = NEW.name, email = NEW.email
    WHERE id = OLD.id;
END;

-- INSTEAD OF DELETE触发器
CREATE TRIGGER trg_user_summary_delete
INSTEAD OF DELETE ON user_summary
BEGIN
    DELETE FROM users WHERE id = OLD.id;
END;

-- RAISE 用于抛出错误或忽略操作

-- ABORT：终止事务
CREATE TRIGGER trg_check_age
BEFORE INSERT ON users
BEGIN
    SELECT RAISE(ABORT, 'Age must be positive')
    WHERE NEW.age < 0;
END;

-- IGNORE：跳过当前行
CREATE TRIGGER trg_skip_invalid
BEFORE INSERT ON users
BEGIN
    SELECT RAISE(IGNORE) WHERE NEW.email IS NULL;
END;

-- ROLLBACK：回滚事务
CREATE TRIGGER trg_rollback_on_error
BEFORE UPDATE ON critical_data
BEGIN
    SELECT RAISE(ROLLBACK, 'Cannot modify critical data');
END;

-- 在触发器中使用SELECT执行操作
CREATE TRIGGER trg_update_counter
AFTER INSERT ON orders
BEGIN
    UPDATE order_counters 
    SET count = count + 1 
    WHERE user_id = NEW.user_id;
    
    SELECT CASE 
        WHEN (SELECT count FROM order_counters WHERE user_id = NEW.user_id) > 100
        THEN RAISE(ABORT, 'Order limit exceeded')
    END;
END;

-- 查看所有触发器
SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'trigger';

-- 查看特定表的触发器
SELECT name, sql FROM sqlite_master 
WHERE type = 'trigger' AND tbl_name = 'users';

-- 命令行方式
.schema trg_users_insert_log

-- 查看表的触发器信息
PRAGMA trigger_list(users);

-- 删除触发器
DROP TRIGGER trg_users_insert_log;

-- 使用 IF EXISTS
DROP TRIGGER IF EXISTS trg_users_insert_log;

-- 创建审计表
CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY,
    table_name TEXT,
    record_id INTEGER,
    action TEXT,
    old_values TEXT,
    new_values TEXT,
    changed_at DATETIME,
    changed_by TEXT
);

-- 通用审计触发器
CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_values, new_values, changed_at)
    VALUES (
        'users',
        COALESCE(NEW.id, OLD.id),
        CASE 
            WHEN NEW.id IS NOT NULL AND OLD.id IS NULL THEN 'INSERT'
            WHEN NEW.id IS NOT NULL AND OLD.id IS NOT NULL THEN 'UPDATE'
            ELSE 'DELETE'
        END,
        CASE WHEN OLD.id IS NOT NULL THEN json_object('name', OLD.name, 'email', OLD.email) ELSE NULL END,
        CASE WHEN NEW.id IS NOT NULL THEN json_object('name', NEW.name, 'email', NEW.email) ELSE NULL END,
        datetime('now')
    );
END;

-- 验证数据完整性
CREATE TRIGGER trg_validate_order
BEFORE INSERT ON orders
BEGIN
    SELECT RAISE(ABORT, 'User does not exist')
    WHERE NOT EXISTS (SELECT 1 FROM users WHERE id = NEW.user_id);
    
    SELECT RAISE(ABORT, 'Invalid order amount')
    WHERE NEW.total < 0;
END;

-- 自动计算字段
CREATE TRIGGER trg_calculate_total
BEFORE INSERT OR UPDATE ON order_items
BEGIN
    SELECT RAISE(IGNORE) WHERE NEW.quantity IS NULL OR NEW.price IS NULL;
    
    -- 更新订单总金额
    UPDATE orders 
    SET total = (
        SELECT SUM(quantity * price) 
        FROM order_items 
        WHERE order_id = NEW.order_id
    )
    WHERE id = NEW.order_id;
END;

-- 级联更新
CREATE TRIGGER trg_update_user_orders
AFTER UPDATE OF id ON users
BEGIN
    UPDATE orders SET user_id = NEW.id WHERE user_id = OLD.id;
END;

-- 级联删除（如果没有外键）
CREATE TRIGGER trg_delete_user_orders
BEFORE DELETE ON users
BEGIN
    DELETE FROM orders WHERE user_id = OLD.id;
END;

-- 实现软删除
CREATE TRIGGER trg_soft_delete
INSTEAD OF DELETE ON users
BEGIN
    UPDATE users 
    SET deleted_at = datetime('now')
    WHERE id = OLD.id AND deleted_at IS NULL;
END;

-- 触发器会增加操作开销
-- 注意：
-- 1. 避免复杂操作
-- 2. 避免递归触发
-- 3. 考虑使用批量操作

-- 递归触发示例（避免）
CREATE TRIGGER trg_update_timestamp
AFTER UPDATE ON users
BEGIN
    UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
    -- 这会再次触发UPDATE，导致无限循环
END;

-- 使用日志表调试
CREATE TABLE trigger_debug (
    id INTEGER PRIMARY KEY,
    trigger_name TEXT,
    message TEXT,
    created_at DATETIME
);

CREATE TRIGGER trg_debug
AFTER INSERT ON users
BEGIN
    INSERT INTO trigger_debug (trigger_name, message, created_at)
    VALUES ('trg_debug', 'Insert: ' || NEW.name, datetime('now'));
END;