SQLite 触发器与视图

触发器

触发器概述

触发器是数据库中的一种特殊对象，它会在指定的数据库事件发生时自动执行。SQLite 支持在表上创建触发器，用于响应 INSERT、UPDATE 或 DELETE 操作，实现数据自动处理和业务规则强制执行。

触发器类型

AFTER 触发器

在触发事件完成后执行，适用于记录操作日志、数据审计等场景。

sql

CREATE TRIGGER users_after_cud
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, action_type, record_id, old_data, new_data, action_time)
  VALUES(
    'users',
    CASE
      WHEN INSERT THEN 'INSERT'
      WHEN UPDATE THEN 'UPDATE'
      ELSE 'DELETE'
    END,
    COALESCE(NEW.id, OLD.id),
    CASE WHEN DELETE THEN json_serialize(OLD) ELSE NULL END,
    CASE WHEN INSERT OR UPDATE THEN json_serialize(NEW) ELSE NULL END,
    CURRENT_TIMESTAMP
  );
END;

BEFORE 触发器

在触发事件执行前执行，适用于数据验证、默认值设置等场景。

sql

CREATE TRIGGER orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  -- 设置默认创建时间
  IF NEW.created_at IS NULL THEN
    SET NEW.created_at = CURRENT_TIMESTAMP;
  END IF;
  
  -- 设置默认更新时间
  IF NEW.updated_at IS NULL THEN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
  END IF;
  
  -- 验证订单金额必须大于 0
  IF NEW.amount <= 0 THEN
    SELECT RAISE(ABORT, '订单金额必须大于 0');
  END IF;
END;

INSTEAD OF 触发器

替代触发事件执行，主要用于视图上，允许对视图执行 DML 操作。

sql

CREATE TRIGGER products_instead_of_delete
INSTEAD OF DELETE ON active_products
FOR EACH ROW
BEGIN
  -- 软删除，更新状态而非物理删除
  UPDATE products
  SET status = 'inactive',
      updated_at = CURRENT_TIMESTAMP
  WHERE id = OLD.id;
END;

触发器生产实践

生产场景 1：电商订单状态管理

需求：当订单状态更新为 "shipped" 时，自动减少对应产品的库存

sql

CREATE TRIGGER orders_after_update_status
AFTER UPDATE ON orders
FOR EACH ROW
WHEN OLD.status != NEW.status AND NEW.status = 'shipped'
BEGIN
  -- 减少产品库存
  UPDATE products
  SET stock = stock - (
    SELECT SUM(quantity) 
    FROM order_items 
    WHERE order_id = NEW.id
  )
  WHERE id IN (
    SELECT product_id 
    FROM order_items 
    WHERE order_id = NEW.id
  );
  
  -- 记录库存变更日志
  INSERT INTO inventory_logs (product_id, order_id, change_type, quantity, log_time)
  SELECT 
    product_id, 
    NEW.id, 
    'order_shipped', 
    -quantity, 
    CURRENT_TIMESTAMP
  FROM order_items 
  WHERE order_id = NEW.id;
END;

生产场景 2：用户数据审计

需求：记录所有用户数据的变更，包括变更前后的值

sql

-- 创建审计日志表
CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT NOT NULL,
    action_type TEXT NOT NULL,
    record_id INTEGER NOT NULL,
    old_data TEXT,
    new_data TEXT,
    action_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    performed_by TEXT DEFAULT 'system'
);

-- 创建用户表
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    status TEXT DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 假设已注册 json_serialize 自定义函数

-- 创建插入触发器
CREATE TRIGGER users_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (
    table_name, action_type, record_id, new_data
  ) VALUES (
    'users', 'INSERT', NEW.id, json_serialize(NEW)
  );
END;

-- 创建更新触发器
CREATE TRIGGER users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (
    table_name, action_type, record_id, old_data, new_data
  ) VALUES (
    'users', 'UPDATE', NEW.id, json_serialize(OLD), json_serialize(NEW)
  );
  
  -- 更新时间戳
  UPDATE users
  SET updated_at = CURRENT_TIMESTAMP
  WHERE id = NEW.id;
END;

触发器管理

查看触发器

sql

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

-- 查看特定表的触发器
SELECT name FROM sqlite_master WHERE type='trigger' AND sql LIKE '%ON table_name%';

删除触发器

sql

DROP TRIGGER IF EXISTS trigger_name;

查看触发器定义

sql

SELECT sql FROM sqlite_master WHERE type='trigger' AND name='trigger_name';

视图

视图概述

视图是基于一个或多个表的查询结果集，它是一个虚拟表，不存储实际数据。视图可以简化复杂查询，提高数据安全性，并提供数据的逻辑独立性。

视图类型

简单视图

基于单个表的视图，支持基本的 SELECT、INSERT、UPDATE 和 DELETE 操作（在满足条件的情况下）。

sql

CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active';

复杂视图

基于多个表的连接或包含聚合函数的视图，通常只支持 SELECT 操作。

sql

CREATE VIEW order_details AS
SELECT 
  o.id AS order_id,
  o.order_date,
  o.status,
  o.amount,
  c.username AS customer_name,
  c.email AS customer_email,
  COUNT(oi.id) AS item_count,
  GROUP_CONCAT(p.name, ', ') AS product_names
FROM orders o
JOIN users c ON o.user_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status != 'cancelled'
GROUP BY o.id;

索引视图模拟

SQLite 不支持真正的索引视图，但可以通过触发器和物理表模拟实现：

sql

-- 创建物化视图基表
CREATE TABLE mv_order_summary (
    id INTEGER PRIMARY KEY,
    order_date DATE,
    total_orders INTEGER,
    total_amount DECIMAL(10, 2),
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建刷新存储过程（通过触发器实现）
CREATE TRIGGER refresh_mv_order_summary
AFTER INSERT OR UPDATE OR DELETE ON orders
BEGIN
  -- 清空并重新生成物化视图数据
  DELETE FROM mv_order_summary;
  INSERT INTO mv_order_summary (order_date, total_orders, total_amount)
  SELECT 
    DATE(order_date) AS order_date,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_amount
  FROM orders
  GROUP BY DATE(order_date);
END;

视图生产实践

生产场景 1：报表数据简化

需求：为报表工具提供简化的订单数据视图

sql

CREATE VIEW sales_report_data AS
SELECT 
  STRFTIME('%Y-%m', o.order_date) AS month,
  c.region AS customer_region,
  p.category AS product_category,
  COUNT(o.id) AS order_count,
  SUM(o.amount) AS total_sales,
  AVG(o.amount) AS avg_order_value
FROM orders o
JOIN users c ON o.user_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
GROUP BY 
  STRFTIME('%Y-%m', o.order_date),
  c.region,
  p.category;

生产场景 2：数据安全控制

需求：限制客服只能访问用户的基本信息，不能查看敏感数据

sql

CREATE VIEW customer_service_users AS
SELECT 
  id,
  username,
  email,
  created_at,
  status
FROM users
WHERE status = 'active';

视图管理

查看视图

sql

-- 查看所有视图
SELECT name FROM sqlite_master WHERE type='view';

删除视图

sql

DROP VIEW IF EXISTS view_name;

更新视图定义

sql

CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, phone, created_at, updated_at
FROM users
WHERE status = 'active';

查看视图定义

sql

-- 使用 sqlite3 命令行工具
.schema view_name

-- 通过 SQL 查询
SELECT sql FROM sqlite_master WHERE type='view' AND name='view_name';

触发器与视图的结合使用

触发器和视图可以结合使用，实现更复杂的数据管理逻辑，特别是通过 INSTEAD OF 触发器允许对视图执行 DML 操作。

sql

-- 创建产品库存视图
CREATE VIEW product_inventory AS
SELECT 
  p.id,
  p.name,
  p.category,
  p.price,
  p.stock,
  CASE 
    WHEN p.stock <= 0 THEN 'out_of_stock'
    WHEN p.stock <= 10 THEN 'low_stock'
    ELSE 'in_stock'
  END AS stock_status
FROM products p;

-- 创建 INSTEAD OF 触发器，允许更新视图
CREATE TRIGGER product_inventory_instead_of_update
INSTEAD OF UPDATE ON product_inventory
FOR EACH ROW
BEGIN
  -- 更新产品表
  UPDATE products
  SET 
    name = NEW.name,
    category = NEW.category,
    price = NEW.price,
    stock = NEW.stock,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = NEW.id;
  
  -- 记录库存变更
  INSERT INTO stock_logs (product_id, old_stock, new_stock, change_reason, log_time)
  VALUES (
    NEW.id,
    (SELECT stock FROM products WHERE id = NEW.id),
    NEW.stock,
    '手动更新',
    CURRENT_TIMESTAMP
  );
END;

版本差异

SQLite 3.6.19 及以上

支持 INSTEAD OF 触发器，允许对视图执行 DML 操作
增强了触发器的错误处理能力

SQLite 3.7.0 及以上

支持在触发器中使用 WHEN 子句，允许更精确地控制触发器的执行条件
支持在触发器中使用 RAISE() 函数抛出异常

SQLite 3.8.0 及以上

支持在视图定义中使用 CTE（公共表表达式），增强了视图的表达能力
优化了触发器的执行性能

SQLite 3.25.0 及以上

支持窗口函数在视图中的使用
增强了视图的查询优化

SQLite 3.31.0 及以上

支持 UPDATE FROM 语法在触发器中的使用
增强了触发器对复杂数据类型的支持

SQLite 3.35.0 及以上

支持 MERGE 语句在触发器中的使用
优化了视图的查询计划生成

生产运维最佳实践

触发器最佳实践

保持触发器简洁：避免在触发器中执行复杂逻辑，复杂业务应在应用层实现
注意性能影响：触发器会增加操作的执行时间，避免在高频操作表上创建复杂触发器
避免递归触发：防止触发器之间相互调用导致无限循环
明确命名规范：使用清晰的命名规则，如 table_event_type
文档化触发器：记录触发器的用途、触发条件和执行逻辑
测试触发器：编写单元测试，确保触发器在各种情况下都能正确工作
监控触发器性能：对频繁触发的触发器进行性能监控

视图最佳实践

合理命名：使用清晰的命名规则，如 vw_view_name
避免过度复杂：不要在视图中嵌套多个视图或使用过于复杂的逻辑
考虑性能：视图在查询时才执行，复杂视图可能影响性能
文档化视图：记录视图的用途和查询逻辑
定期维护：在基础表结构变更时更新视图
优化视图查询：在基础表上创建合适的索引，提高视图查询性能
避免在视图上执行高频 DML：视图的 DML 操作性能较差

安全考虑

限制触发器权限：避免在触发器中执行危险操作
验证视图访问权限：确保用户只能访问其有权限的视图
防止 SQL 注入：在动态视图定义中使用参数化查询
加密敏感数据：在视图中对敏感数据进行加密或脱敏

常见问题 (FAQ)

SQLite 支持 INSTEAD OF 触发器吗？

是的，SQLite 支持 INSTEAD OF 触发器，主要用于视图上，允许对视图执行 DML 操作。INSTEAD OF 触发器在 SQLite 3.6.19 及以上版本中支持。

触发器可以访问其他表吗？

是的，触发器可以访问和修改其他表的数据，但需要注意避免循环依赖和性能问题。在复杂业务场景下，建议将逻辑放在应用层实现。

视图可以包含参数吗？

SQLite 不直接支持带参数的视图，但可以使用 CTE（公共表表达式）或临时表实现类似功能。例如：

sql

WITH params AS (
  SELECT '2023-01-01' AS start_date, '2023-12-31' AS end_date
)
SELECT * FROM orders o, params p
WHERE o.order_date BETWEEN p.start_date AND p.end_date;

如何查看视图的定义？

可以使用 .schema view_name 命令在 sqlite3 命令行工具中查看视图定义，或查询 sqlite_master 表：

sql

SELECT sql FROM sqlite_master WHERE type='view' AND name='view_name';

触发器会影响性能吗？

是的，触发器会在每次触发事件发生时执行，可能会增加操作的执行时间。因此，应谨慎使用触发器，避免在高频操作的表上创建复杂触发器。

视图可以创建索引吗？

SQLite 不直接支持在视图上创建索引，但可以在视图的基础表上创建合适的索引来提高视图查询性能。对于频繁查询的复杂视图，可以考虑使用物化视图（通过触发器和物理表模拟）。

如何优化视图查询性能？

在基础表上创建合适的索引
简化视图定义，避免复杂的 JOIN 和聚合
避免在视图中嵌套多个视图
考虑使用物化视图替代频繁查询的复杂视图
定期分析视图查询计划，优化查询逻辑

触发器可以回滚事务吗？

是的，触发器可以使用 RAISE() 函数回滚事务。例如：

sql

CREATE TRIGGER orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.amount <= 0 THEN
    SELECT RAISE(ABORT, '订单金额必须大于 0');
  END IF;
END;

总结

触发器和视图是 SQLite 数据库开发中的重要工具，它们可以帮助开发者实现数据自动处理、业务规则强制执行、数据安全控制和查询简化。

触发器适用于：

数据审计和日志记录
业务规则验证
自动数据更新
级联操作

视图适用于：

简化复杂查询
数据安全控制
数据一致性保证
报表数据提供

在实际生产环境中，应根据业务需求和性能考虑，合理使用触发器和视图，遵循最佳实践，确保数据库的高效运行和数据安全。同时，要关注 SQLite 版本差异，选择适合当前版本的功能和语法，避免兼容性问题。

通过合理设计和使用触发器与视图，可以提高数据库开发效率，增强数据安全性，并简化应用程序逻辑，为应用提供强大的数据支持。

SQLite 触发器与视图 ​

触发器 ​

触发器概述 ​

触发器类型 ​

AFTER 触发器 ​

BEFORE 触发器 ​

INSTEAD OF 触发器 ​

触发器生产实践 ​

生产场景 1：电商订单状态管理 ​

生产场景 2：用户数据审计 ​

触发器管理 ​

查看触发器 ​

删除触发器 ​

查看触发器定义 ​

视图 ​

视图概述 ​

视图类型 ​

简单视图 ​

复杂视图 ​

索引视图模拟 ​

视图生产实践 ​

生产场景 1：报表数据简化 ​

生产场景 2：数据安全控制 ​

视图管理 ​

查看视图 ​

删除视图 ​

更新视图定义 ​

查看视图定义 ​

触发器与视图的结合使用 ​

版本差异 ​

SQLite 3.6.19 及以上 ​

SQLite 3.7.0 及以上 ​

SQLite 3.8.0 及以上 ​

SQLite 3.25.0 及以上 ​

SQLite 3.31.0 及以上 ​

SQLite 3.35.0 及以上 ​

生产运维最佳实践 ​

触发器最佳实践 ​

视图最佳实践 ​

安全考虑 ​

常见问题 (FAQ) ​

SQLite 支持 INSTEAD OF 触发器吗？ ​

触发器可以访问其他表吗？ ​

视图可以包含参数吗？ ​

如何查看视图的定义？ ​

触发器会影响性能吗？ ​

视图可以创建索引吗？ ​

如何优化视图查询性能？ ​

触发器可以回滚事务吗？ ​

总结 ​