SQL 行级触发器 vs 语句级触发器

在本文中，我们将介绍SQL中的行级触发器和语句级触发器的区别和使用场景。

阅读更多：SQL 教程

什么是触发器？

触发器是在数据库中定义的一种特殊类型的存储过程，它会在特定的数据库操作（如插入、更新或删除）发生前后自动执行。触发器可以用来实现数据的自动维护、约束和业务规则等。

行级触发器

行级触发器是指当每行受影响时触发的触发器。即当对表执行操作（插入、更新或删除）时，触发器会对每一行进行操作。行级触发器可以在每一行上执行某些操作，例如检查数据的完整性、自动计算字段、记录审计信息等。

行级触发器的语法如下：

CREATE TRIGGER trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON table_name
    [REFERENCING [OLD AS old] [NEW AS new]]
    [FOR [EACH] {ROW | STATEMENT}]
    [WHEN condition]
BEGIN
    -- 触发器的操作语句
END;

以下是一个示例，演示了如何使用行级触发器来自动计算商品库存的变化：

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    quantity INT,
    price DECIMAL(10, 2),
    total_value DECIMAL(10, 2)
);

CREATE TRIGGER update_total_value
    AFTER INSERT OR UPDATE OF quantity, price
    ON products
    FOR EACH ROW
BEGIN
    SET new.total_value = new.quantity * new.price;
END;

在上面的示例中，每当在products表中插入或更新quantity或price列时，行级触发器update_total_value会根据新的quantity和price计算新的total_value。这样可以确保total_value始终与quantity和price的乘积保持一致。

语句级触发器

语句级触发器是指在执行整个SQL语句后触发的触发器。即当对表执行操作（插入、更新或删除）时，触发器只会在语句执行完毕后触发一次。语句级触发器可以用来实现一些对整个操作结果进行分析和处理的逻辑。

语句级触发器的语法如下：

CREATE TRIGGER trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON table_name
    [FOR [EACH] {ROW | STATEMENT}]
    [WHEN condition]
BEGIN
    -- 触发器的操作语句
END;

以下是一个示例，演示了如何使用语句级触发器来记录商品表的变化历史：

CREATE TABLE products_history (
    id INT,
    name VARCHAR(100),
    quantity INT,
    price DECIMAL(10, 2),
    operation VARCHAR(10),
    updated_time TIMESTAMP
);

CREATE TRIGGER log_product_changes
    AFTER INSERT OR UPDATE OR DELETE
    ON products
    REFERENCING NEW AS new OLD AS old
    FOR EACH STATEMENT
BEGIN
    IF INSERTING THEN
        INSERT INTO products_history (id, name, quantity, price, operation, updated_time)
        SELECT id, name, quantity, price, 'INSERT', CURRENT_TIMESTAMP
        FROM new;
    ELSEIF UPDATING THEN
        INSERT INTO products_history (id, name, quantity, price, operation, updated_time)
        SELECT id, name, quantity, price, 'UPDATE', CURRENT_TIMESTAMP
        FROM new;
    ELSEIF DELETING THEN
        INSERT INTO products_history (id, name, quantity, price, operation, updated_time)
        SELECT id, name, quantity, price, 'DELETE', CURRENT_TIMESTAMP
        FROM old;
    END IF;
END;

在上面的示例中，语句级触发器log_product_changes会在每次对products表执行插入、更新或删除操作后，将变化记录到products_history表中。通过记录每一次操作的详细信息和操作时间，可以实现对商品表变化历史的审计和追溯。

行级触发器 vs 语句级触发器

行级触发器和语句级触发器在以下几个方面有所不同：

受影响的行数：行级触发器对每一行操作都会触发，而语句级触发器只在执行完整个SQL语句后触发一次。
访问数据：行级触发器可以访问和操作当前行的数据，而语句级触发器可以通过NEW和OLD关键字来访问和操作所有受影响的行的数据。
语句上下文：行级触发器在每一行上执行，因此在触发器中可以有条件语句和循环语句等。而语句级触发器在整个操作执行完毕后执行，因此无法使用条件语句和循环语句等。
性能开销：由于行级触发器需要对每一行操作都进行触发和执行，因此对大表的操作可能会导致性能开销较大。而语句级触发器只在整个操作执行完毕后触发一次，因此性能开销相对较小。

总结

SQL中的行级触发器和语句级触发器都是一种在数据库操作前后自动执行的存储过程。行级触发器对每一行操作都会触发，可以用来实现数据的自动维护和业务规则等。语句级触发器在整个操作执行完毕后触发一次，可以用来实现对整个操作结果的分析和处理。根据实际需求选择适合的触发器类型可以提高数据库的数据完整性和业务逻辑的灵活性。