SQLite 触发器使用示例和最佳实践

2024-12-20 SQLite 触发器 85 0

在 SQLite 中，触发器（Trigger）是特殊的存储过程，它在特定的表上执行某些操作时被自动调用。触发器可以在 INSERT、UPDATE 或 DELETE 语句执行之前或之后触发。以下是 SQLite 触发器的使用示例和一些最佳实践。

SQLite 基本语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} 
{INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- SQL 语句；
END;

trigger_name: 触发器名称。
BEFORE | AFTER | INSTEAD OF: 触发时间。
{INSERT | UPDATE | DELETE}: 操作类型。
table_name: 触发器所作用的表。
FOR EACH ROW: 指触发器针对每行执行。
BEGIN ... END: 包含触发器操作的 SQL 语句块。

SQLite 使用示例

1. 记录日志

创建一个触发器，在 users 表中插入记录时，将日志写入 logs 表：

创建表

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message TEXT,
    created_at TEXT
);

创建触发器

CREATE TRIGGER log_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO logs (message, created_at)
    VALUES ('User ' || NEW.name || ' was added.', datetime('now'));
END;

测试触发器

INSERT INTO users (name) VALUES ('Alice');
SELECT * FROM logs;

输出：

| id | message                   | created_at         |
|----|---------------------------|--------------------|
| 1  | User Alice was added.     | 2024-12-20 12:00  |

2. 防止非法更新

创建一个触发器，禁止将 salary 字段的值更新为负数。

创建表

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary REAL
);

创建触发器

CREATE TRIGGER prevent_negative_salary
BEFORE UPDATE ON employees
FOR EACH ROW
WHEN NEW.salary < 0
BEGIN
    SELECT RAISE(FAIL, 'Salary cannot be negative');
END;

测试触发器

INSERT INTO employees (name, salary) VALUES ('Bob', 5000);
UPDATE employees SET salary = -1000 WHERE name = 'Bob'; -- 报错

错误信息：

Error: Salary cannot be negative

3. 自动更新时间戳

创建一个触发器，更新记录时自动设置 updated_at 字段。

创建表

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    description TEXT,
    updated_at TEXT
);

创建触发器

CREATE TRIGGER update_timestamp
AFTER UPDATE ON tasks
FOR EACH ROW
BEGIN
    UPDATE tasks
    SET updated_at = datetime('now')
    WHERE id = OLD.id;
END;

测试触发器

INSERT INTO tasks (description) VALUES ('Complete project');
UPDATE tasks SET description = 'Complete project (updated)' WHERE id = 1;
SELECT * FROM tasks;

输出：

| id | description                   | updated_at          |
|----|-------------------------------|---------------------|
| 1  | Complete project (updated)    | 2024-12-20 12:30   |

SQLite 最佳实践

避免复杂逻辑

触发器的逻辑不应过于复杂，以免影响性能和可维护性。

复杂逻辑可以通过调用存储过程或分解为多个简单触发器实现。

使用条件约束

使用 WHEN 子句限制触发器仅在特定情况下执行，避免不必要的触发。

注意递归

避免触发器自身引发的递归调用。例如，更新表时调用的触发器再次触发自身。

RAISE 函数

使用 RAISE 函数返回错误消息，例如：

SELECT RAISE(FAIL, 'Error message');

调试触发器

触发器的错误可能难以排查，建议在触发器内部插入日志记录表，记录每次触发器的执行情况。

性能问题

避免在触发器中使用过多的子查询或复杂操作，可能影响表的插入、更新或删除性能。

文档化

为触发器添加详细的注释，说明其功能和触发条件，以便维护。

通过这些示例和最佳实践，触发器可以用来实现强大的数据完整性保护和自动化功能，同时保持良好的性能和可维护性。