八月 4, 2024
摘要：在本教程中，您将学习如何创建一个条件触发器，该触发器仅在条件满足时触发。
目录
介绍
在 PostgreSQL 中，触发器是一个数据库对象，当表上发生INSERT、UPDATE、DELETE或TRUNCATE事件时，它会自动执行一个函数。
有时，您希望仅在满足特定条件时才激活触发器。为此，您可以在 CREATE TRIGGER 语句的WHEN子句中，指定一个布尔条件，如下所示：
CREATE TRIGGER trigger_name
ON table_name
WHEN condition
EXECUTE FUNCTION function_name(arguments);
在此语法中，condition是一个布尔表达式。如果为condition计算结果为 true，则触发器会生效；否则，触发器将不会被激活。
在行级触发器中，您可以在条件中访问行列的旧值/新值。但是，在语句级触发器中，您无权访问列值。
PostgreSQL 触发器 WHEN 条件示例
首先，创建一个表，名为orders，用于存储订单数据：
CREATE TABLE orders (
order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INT NOT NULL,
total_amount NUMERIC NOT NULL DEFAULT 0,
status VARCHAR(20) NOT NULL
);
第二步，创建另一个表，名为customer_stats，用于存储客户总支出金额：
CREATE TABLE customer_stats (
customer_id INT PRIMARY KEY,
total_spent NUMERIC NOT NULL DEFAULT 0
);
第三步，创建一个 AFTER INSERT 触发器，当插入新行到orders表中时，该触发器会向customer_stats表中插入一行：
CREATE OR REPLACE FUNCTION insert_customer_stats()
RETURNS TRIGGER
AS $$
BEGIN
INSERT INTO customer_stats (customer_id)
VALUES (NEW.customer_id);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER insert_customer_stats_trigger
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION insert_customer_stats();
第四步，在orders表上定义一个带有条件的AFTER UPDATE触发器：
CREATE OR REPLACE FUNCTION update_customer_stats()
RETURNS TRIGGER
AS
$$
BEGIN
IF NEW.status = 'completed' THEN
-- Update the total_spent for the customer
UPDATE customer_stats
SET total_spent = total_spent + NEW.total_amount
WHERE customer_id = NEW.customer_id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_customer_stats_trigger
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status <> 'completed' AND NEW.status = 'completed')
EXECUTE FUNCTION update_customer_stats();
仅当行的状态从“未完成”状态变为“已完成”时，该AFTER UPDATE触发器才会触发。
第五步，向orders表中插入一些行：
INSERT INTO orders (customer_id, total_amount, status)
VALUES
(1, 100, 'pending'),
(2, 200, 'pending');
AFTER INSERT触发器触发，并将行插入到customer_stats表中。
第六步，将客户 ID 为 1 和 2 的订单状态修改为completed：
UPDATE order
SET status = 'completed'
WHERE customer_id IN (1,2);
AFTER UPDATE触发器触发，并更新customer_stats表中的total_spent列。
最后，从customer_stats表中检索数据：
SELECT * FROM customer_stats;
输出：
customer_id | total_spent
-------------+-------------
1 | 100
2 | 200
(2 rows)
总结
在CREATE TRIGGER语句的WHEN子句中指定一个条件，以指示 PostgreSQL 在条件满足时触发触发器。