触发器其实开发中基本没用到，到是运维中用到不少。触发器是数据库中的一种特殊类型的存储过程，它会在特定的数据库事件发生时自动执行。本文将深入探讨 SQLite 触发器的各个方面，包括其概念、语法、类型、使用场景以及最佳实践。
什么是触发器？
触发器是一种数据库对象，它与表关联并在指定的数据库事件（如插入、更新或删除）发生时自动执行。触发器可以在这些事件之前或之后执行，用于实现复杂的业务规则、维护数据完整性、记录变更历史等。
SQLite 触发器的类型
SQLite 支持以下类型的触发器：
BEFORE 触发器：在指定事件之前执行。
AFTER 触发器：在指定事件之后执行。
INSERT 触发器：在插入操作时触发。
UPDATE 触发器：在更新操作时触发。
DELETE 触发器：在删除操作时触发。
创建测试数据
-- 1. 创建用户表CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT NOT NULL,age INTEGER NOT NULL,salary DECIMAL(10,2),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,last_modified DATETIME DEFAULT CURRENT_TIMESTAMP);
-- 2. 创建用户日志表CREATE TABLE user_logs (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER,action TEXT NOT NULL,action_date DATETIME DEFAULT CURRENT_TIMESTAMP,details TEXT,FOREIGN KEY (user_id) REFERENCES users(id));
创建触发器
创建触发器的基本语法如下：
CREATE TRIGGER trigger_name [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name[FOR EACH ROW][WHEN condition]BEGIN -- 触发器逻辑END;
示例：创建触发器：检查年龄限制
CREATE TRIGGER check_minimum_ageBEFORE INSERT ON usersFOR EACH ROWWHEN NEW.age < 18BEGINSELECT RAISE(ABORT, '用户年龄必须至少18岁');END;
INSERT INTO users (username, age, salary) VALUES ('张三', 25, 5000.00);INSERT INTO users (username, age, salary) VALUES ('李四', 30, 6000.00);INSERT INTO users (username, age, salary) VALUES ('王五', 35, 7000.00);
-- 测试年龄限制触发器（会失败）INSERT INTO users (username, age, salary) VALUES ('小明', 16, 3000.00);
创建触发器：记录用户插入日志
CREATE TRIGGER after_insert_userAFTER INSERT ON usersFOR EACH ROWBEGININSERT INTO user_logs (user_id, action, details)VALUES (NEW.id, 'INSERT', '新用户创建: ' || NEW.username);END;
触发器的执行时机
触发器可以在以下时机执行：
BEFORE：在触发事件之前执行，可以用来验证或修改要插入或更新的数据。
AFTER：在触发事件之后执行，通常用于记录日志或执行后续操作。
OLD 和 NEW 引用
在触发器中，可以使用 OLD 和 NEW 引用来访问受影响行的值：
OLD：引用更新或删除之前的行值。
NEW：引用插入或更新之后的行值。
创建触发器：限制工资增长
CREATE TRIGGER before_update_salaryBEFORE UPDATE OF salary ON usersFOR EACH ROWWHEN NEW.salary > OLD.salary * 1.2BEGINSELECT RAISE(ABORT, '工资增长不能超过20%');END;
-- 失败更新（增长超过20%）UPDATE users SET salary = salary * 1.3 WHERE username = '李四';
触发器中的条件执行
创建触发器：更新最后修改时间
CREATE TRIGGER update_modification_dateAFTER UPDATE ON usersFOR EACH ROWBEGINUPDATE usersSET last_modified = CURRENT_TIMESTAMPWHERE id = NEW.id;END;
--成功更新（增长20%以内）UPDATE users SET salary = salary * 1.1 WHERE username = '张三';
触发器的应用场景
审计跟踪：记录数据变更历史。
业务规则实施：自动执行复杂的业务逻辑。
数据验证：在数据插入或更新前进行验证。
自动更新关联数据：在一个表更新时自动更新相关表。
计算衍生值：自动计算和更新统计数据。
修改和删除触发器
SQLite 不直接支持修改触发器。要修改触发器，需要先删除再重新创建：
DROP TRIGGER IF EXISTS trigger_name;
删除触发器：
DROP TRIGGER IF EXISTS trigger_name;
触发器的限制和注意事项
触发器不能调用包含事务控制的存储过程。
触发器中不能使用 PRAGMA 语句。
触发器不能修改它们正在操作的表（避免无限循环）。
虚拟表和临时表不支持触发器。
触发器与性能
触发器可能会影响数据库性能：
复杂的触发器逻辑可能会降低插入、更新和删除操作的速度。
大量的触发器可能会增加数据库操作的整体延迟。
触发器中的子查询或复杂计算可能会显著影响性能。
优化建议：
保持触发器逻辑简单。
避免在触发器中执行复杂的查询。
考虑使用索引来优化触发器中的查询。
结论
SQLite 触发器是一个强大的功能，可以自动化数据库操作，维护数据完整性，并实现复杂的业务规则。正确使用触发器可以大大简化应用程序逻辑，提高数据一致性，并自动化许多数据库维护任务。
该文章在 2024/11/13 14:48:42 编辑过
400 186 1886
