LabEx
登录免费加入

SQLite 触发器自动化

SQLiteSQLiteBeginner
立即练习

💡 本教程由 AI 辅助翻译自英文原版。如需查看原文，您可以 切换至英文原版

介绍

在这个实验中，你将探索 SQLite 触发器自动化。你将学习如何创建、验证和管理触发器，这些触发器会自动执行 SQL 语句，以响应表上的特定事件。这个实验将指导你创建审计日志并有效地管理触发器。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/add_rows("Insert Multiple Rows") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") subgraph Lab Skills sqlite/make_table -.-> lab-552559{{"SQLite 触发器自动化"}} sqlite/add_rows -.-> lab-552559{{"SQLite 触发器自动化"}} sqlite/get_all -.-> lab-552559{{"SQLite 触发器自动化"}} sqlite/query_where -.-> lab-552559{{"SQLite 触发器自动化"}} end

创建表：employees 和 employee_audit

在这一步中，你将创建两个表：employees 用于存储员工数据，employee_audit 用于记录薪资更新。

首先，在你的终端中打开 SQLite shell。

sqlite3 /home/labex/project/test.db

这个命令会打开 SQLite shell 并连接到名为 test.db 的数据库文件。如果该文件不存在，SQLite 将会创建它。

现在，创建具有以下结构的 employees 表：

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

这个 SQL 命令创建了一个名为 employees 的表，包含三列：

  • id：一个整数，作为每个员工的主键（primary key）。
  • name：一个文本字段，用于存储员工的姓名。
  • salary：一个实数，用于存储员工的薪资。

接下来，创建 employee_audit 表：

CREATE TABLE employee_audit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    old_salary REAL,
    new_salary REAL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

这个表将存储薪资变更的日志。这些列包括：

  • id：每个审计条目的唯一标识符，自动递增。
  • employee_id：薪资被更改的员工的 ID。
  • old_salary：更新前的薪资。
  • new_salary：更新后的薪资。
  • updated_at：一个时间戳，指示更新发生的时间。

向 employees 表中插入示例数据

在这一步中，你将向 employees 表中插入一些示例数据。这些数据将用于测试你在下一步中将要创建的触发器。

执行以下 SQL 命令来插入三条员工记录：

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);

这些命令将三行数据添加到 employees 表中，包含指定的 id、name 和 salary 值。

为了确认数据已正确添加，运行以下命令来查看表中的所有记录：

SELECT * FROM employees;

预期输出：

1|Alice|50000.0
2|Bob|60000.0
3|Charlie|70000.0

此输出显示了每条记录的 id、name 和 salary。

创建触发器：salary_update_trigger

现在，你将创建一个触发器，该触发器会自动将薪资更新记录到 employee_audit 表中。

执行以下 SQL 命令来创建触发器：

CREATE TRIGGER salary_update_trigger
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO employee_audit (employee_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
END;

这个命令创建了一个名为 salary_update_trigger 的触发器（trigger）。该触发器将在 employees 表的 salary 列发生任何更新后自动执行。

  • AFTER UPDATE OF salary ON employees：指定触发器将在 employees 表中的 salary 列更新后激活。
  • BEGIN ... END：包含触发器激活时要执行的 SQL 语句。
  • INSERT INTO employee_audit ...：将一条新记录插入到 employee_audit 表中，包含员工的 ID、旧薪资和新薪资。
    • OLD.id：引用更新前行的 id。
    • OLD.salary：引用更新前的 salary。
    • NEW.salary：引用更新后的 salary。

测试触发器

在这一步中，你将通过更新一名员工的薪资，然后验证是否向 employee_audit 表添加了一条新记录来测试触发器。

让我们更新 Bob 的薪资：

UPDATE employees SET salary = 65000.00 WHERE id = 2;

这个命令将 id 为 2（Bob）的员工的 salary 列更新为 65000.00。

现在，查询 employee_audit 表，看看触发器是否生效：

SELECT * FROM employee_audit;

预期输出：

1|2|60000.0|65000.0|...

你应该在 employee_audit 表中看到一行，其中包含 Bob 的员工 ID、旧薪资和新薪资。 updated_at 列将显示更新的时间戳。确切的时间戳将根据你执行更新的时间而有所不同。

删除触发器

在这一步中，你将学习如何从你的 SQLite 数据库中删除（drop）或移除（remove）一个触发器。

要删除 salary_update_trigger，执行以下命令：

DROP TRIGGER salary_update_trigger;

这个命令从数据库中移除 salary_update_trigger。

要验证触发器是否已被删除，你可以查询 sqlite_master 表：

SELECT name FROM sqlite_master WHERE type='trigger' AND name='salary_update_trigger';

这个查询应该返回一个空的结果集，表明该触发器不再存在。

现在，让我们尝试更新 Alice 的薪资，以确认触发器确实已消失：

UPDATE employees SET salary = 55000.00 WHERE id = 1;

然后，检查 employee_audit 表：

SELECT * FROM employee_audit;

你应该看到没有新的条目被添加到 employee_audit 表中，这确认触发器已成功删除。

最后，退出 SQLite shell：

.exit

总结

在这个实验中，你已经学习了如何创建 SQLite 触发器，这些触发器可以自动执行 SQL 语句，以响应特定表上的更新事件。你创建了一个 employees 表和一个 employee_audit 表。然后，你创建了一个名为 salary_update_trigger 的触发器，该触发器将薪资更新记录到 employee_audit 表中。最后，你学习了如何删除一个触发器。