SQLite - 触发器

触发器（Trigger）是由 SQLite 自动执行或“触发”的存储程序，以响应特定表上的特定事件。这些事件是 INSERT（插入）、DELETE（删除）或 UPDATE（更新）操作。

触发器是以下方面的强大工具：

审计和日志：记录对数据所做的更改，用于安全或历史记录。
执行复杂的业务规则：实现对于简单 CHECK 约束而言过于复杂的约束。
数据验证和转换：在插入或更新数据之前修改或验证数据。
维护数据完整性：自动保持汇总表或相关数据同步。

语法

以下是创建触发器的通用语法。在触发器内部，您可以使用 OLD.column_name 访问更改前的数据，使用 NEW.column_name 访问更改后的数据。

CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | DELETE | UPDATE OF column_name]
ON table_name
FOR EACH ROW
WHEN (condition)
BEGIN
    -- 使用 OLD 和 NEW 引用的触发器逻辑
    -- 例如，INSERT、UPDATE、DELETE 或 SELECT 语句；
    -- 也可以使用 RAISE() 函数中止操作。
END;

示例：一个全面的审计日志

让我们为 PRODUCTS 表创建一个健壮的审计跟踪。我们将把所有插入、更新和删除操作记录到 AUDIT_LOG 表中。

CREATE TABLE PRODUCTS(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    NAME TEXT NOT NULL,
    PRICE REAL NOT NULL CHECK(PRICE > 0)
);

CREATE TABLE AUDIT_LOG(
    LOG_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    PRODUCT_ID INT,
    ACTION TEXT NOT NULL,
    OLD_DATA TEXT,
    NEW_DATA TEXT,
    TIMESTAMP TEXT NOT NULL
);

-- 现在，创建触发器来填充审计日志
CREATE TRIGGER product_audit_trigger
AFTER INSERT ON PRODUCTS
FOR EACH ROW
BEGIN
    INSERT INTO AUDIT_LOG (PRODUCT_ID, ACTION, NEW_DATA, TIMESTAMP)
    VALUES (NEW.ID, 'INSERT',
            'Name: ' || NEW.NAME || ', Price: ' || NEW.PRICE,
            datetime('now', 'localtime'));
END;

-- 在实际应用中，您还会为 UPDATE 和 DELETE 创建单独的触发器。

让我们测试一下 INSERT 触发器。

sqlite> INSERT INTO PRODUCTS (NAME, PRICE) VALUES ('Laptop', 1200.00);
sqlite> SELECT * FROM AUDIT_LOG;

触发器自动创建一条日志条目：

LOG_ID      PRODUCT_ID  ACTION      OLD_DATA    NEW_DATA                      TIMESTAMP
----------  ----------  ----------  ----------  ----------------------------  -------------------
1           1           INSERT                  Name: Laptop, Price: 1200.0   2023-10-27 10:30:00

高级用法：阻止操作

结合 RAISE() 函数的 BEFORE 触发器可以阻止操作发生。例如，让我们阻止任何人删除最后一个产品。

CREATE TRIGGER prevent_last_product_delete
BEFORE DELETE ON PRODUCTS
FOR EACH ROW
WHEN (SELECT COUNT(*) FROM PRODUCTS) = 1
BEGIN
    SELECT RAISE(ABORT, 'Cannot delete the last product.');
END;

-- 现在，尝试删除唯一的产品将失败：
sqlite> DELETE FROM PRODUCTS WHERE ID = 1;
Error: Cannot delete the last product.

管理触发器

您可以通过查询 sqlite_schema 表（旧 sqlite_master 的别名）来列出数据库中的所有触发器。

SELECT name, tbl_name FROM sqlite_schema WHERE type = 'trigger';

要删除触发器，请使用 DROP TRIGGER 语句。包含 IF EXISTS 是一个好习惯。

DROP TRIGGER IF EXISTS product_audit_trigger;