SQL 语句级触发器

介绍

SQL触发器是一种特殊的存储过程，它在数据库中的特定事件（如INSERT、UPDATE或DELETE）发生时自动执行。触发器可以分为行级触发器和语句级触发器。本文将重点介绍语句级触发器。

语句级触发器在SQL语句执行时触发，而不是在每一行数据被修改时触发。这意味着，无论SQL语句影响了多少行数据，触发器只会执行一次。这种特性使得语句级触发器非常适合用于批量操作或需要在语句执行前后进行某些操作的场景。

语法

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

sql

CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON table_name
FOR EACH STATEMENT
EXECUTE FUNCTION function_name();

• trigger_name：触发器的名称。
• BEFORE 或 AFTER：指定触发器是在SQL语句执行之前还是之后触发。
• INSERT、UPDATE 或 DELETE：指定触发器在哪种操作下触发。
• table_name：触发器所关联的表。
• FOR EACH STATEMENT：指定这是一个语句级触发器。
• function_name()：触发器触发时执行的函数。

代码示例

假设我们有一个名为 orders 的表，用于存储订单信息。我们希望在每次插入新订单时，记录一条日志到 order_logs 表中。

首先，创建 orders 表和 order_logs 表：

sql

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    log_message TEXT,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

接下来，创建一个触发器函数，用于在插入新订单时记录日志：

sql

CREATE OR REPLACE FUNCTION log_order_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO order_logs (log_message)
    VALUES ('A new order has been inserted.');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

最后，创建语句级触发器：

sql

CREATE TRIGGER order_insert_trigger
AFTER INSERT ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION log_order_insert();

现在，每当我们向 orders 表中插入新订单时，触发器都会在 order_logs 表中插入一条日志记录。

实际案例

假设我们有一个电商平台，每天会有大量的订单被插入到 orders 表中。为了监控订单的插入情况，我们可以使用语句级触发器来记录每天的订单插入次数。

首先，创建一个 daily_order_count 表来存储每天的订单数量：

sql

CREATE TABLE daily_order_count (
    count_date DATE PRIMARY KEY,
    order_count INT
);

然后，创建一个触发器函数，用于更新每天的订单数量：

sql

CREATE OR REPLACE FUNCTION update_daily_order_count()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO daily_order_count (count_date, order_count)
    VALUES (CURRENT_DATE, 1)
    ON CONFLICT (count_date)
    DO UPDATE SET order_count = daily_order_count.order_count + 1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

最后，创建语句级触发器：

sql

CREATE TRIGGER daily_order_count_trigger
AFTER INSERT ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION update_daily_order_count();

这样，每当有订单被插入时，触发器会自动更新 daily_order_count 表中的订单数量。

总结

SQL语句级触发器是一种强大的工具，可以在SQL语句执行时自动执行特定的操作。与行级触发器不同，语句级触发器只在SQL语句执行时触发一次，适合用于批量操作或需要在语句执行前后进行某些操作的场景。

通过本文的介绍和示例，你应该已经掌握了如何创建和使用语句级触发器。希望这些知识能够帮助你在实际项目中更好地管理和监控数据库操作。

附加资源

• PostgreSQL 触发器文档
• MySQL 触发器文档
• SQL Server 触发器文档

练习

1. 创建一个语句级触发器，在每次更新 orders 表中的订单金额时，记录一条日志到 order_logs 表中。
2. 修改 daily_order_count 表，使其能够记录每天的订单总金额，并创建一个触发器来更新该表。