【postgresql初级使用】条件表达式触发器，兼顾DML执行性能，又能执行复杂逻辑，只在结帐时计算总帐

码农世界 2024-06-19 后端 82 次浏览 0个评论

条件触发器

专栏内容：

postgresql使用入门基础
手写数据库toadb
并发编程

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

文章目录

条件触发器
概述
原理机制
- 语法
- 原理
- 案例
- - 场景介绍
  - 数据准备
  - 创建触发器
  - 效果验证
  - 总结
  - 结尾
    概述
    
    触发器trigger 是语句级的(row-level) 时, 被触发的频率是相当高的，会带来一定的性能开销。
    
    今天给大家分享一种带有条件表达式的触发器，只在条件满足时触发，其它情况下并不会执行，这样既能满足触发器的复杂处理，也能满足性能的要求。
    
    原理机制
    
    下面就来详细了解一下带条件表达式的触发器吧。
    
    语法
    
    带条件表达式的触发器的SQL语法格式如下：
```
CREATE TRIGGER trigger_name
{BEFORE | AFTER} { INSERT OR UPDATE OR DELETE OR TRUNCATE }
ON table_name
WHEN condition
FOR [EACH]  ROW 
EXECUTE FUNCTION function_name();
```
    说明
    - 这里与普通触发器的语法类似，可以参见前面的分享；
    - 如果是行级的触发器，需要指定BEFORE或AFTER以及对应的事件；
    - 此处通过WHEN子句指定触发的条件，只有在条件满足时才能执行；
    - 触发器类型指定，行级触发器为FOR EACH ROW；
    - 最后指定触发器执行函数；
      原理
      
      在行级触发器中：
      - 可以访问修改前的行与修改后的行，通过访问这两行的值设定条件表达式。
      - 修改前的行使用OLD.column_name进行引用，修改后的行引用方式为 NEW.column_name；
      - INSERT命令没有OLD行，DELETE命令没有NEW行；
      - INSTEAD OF不支持设置条件；
        而在语句级触发器中没有可引用的行数据。
        
        案例
        
        下面我们通过一个案例来看看条件触发器的使用场景。
        
        场景介绍
        
        当在饭店消费时，会先点一些菜，过程中又会根据需要再加一些菜或者饮品，最后再统一结帐。
        
        对于这样一个很常见的场景，通过条件触发器可以实现自动计算账单。
        
        数据准备
        
        新建两张表，一张为订单表，记录客户消费情况，以及订单状态；另一张为客户统计，记录客户总的消费金额。
        
        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 ); CREATE TABLE customer_stats ( customer_id INT PRIMARY KEY, total_spent NUMERIC NOT NULL DEFAULT 0 );
        
        创建触发器
        
        下面在orders表上对insert,update事件创建两个触发器。
        
        创建订单触发器
        
        当有新客人时，会向orders表中插入一条新记录；
        
        此时通过触发器自动在customer_stats表中也插入客户ID，金额为0，此时订单状态还为pending。
        
        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();
        
        结帐付款
        
        客人在过程中可能会变动订单金额，订单状态始终为pending, 最后结帐时将订单状态改为completed，此时生成帐单。
        
        在orders表上增加UPDATE事件的条件触发器，只有NEW.status为completed时才会触发，此时将客户金额插入customer_stats表中。
        
        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();
        
        效果验证
        
        好了，来测试一下业务流。
        
        先来了两波客人，分别点了100，200的菜品。
        
        INSERT INTO orders (customer_id, total_amount, status) VALUES (1, 100, 'pending'), (2, 200, 'pending');
        
        两波客人都要结帐了，要忙了。
        
        UPDATE orders SET status = 'completed' WHERE customer_id IN (1,2);
        
        看一下这两波客人的账单：
        
        postgres=> SELECT * FROM customer_stats; customer_id | total_spent -------------+------------- 1 | 100 2 | 200 (2 rows)
        
        先生/女士，这是您的账单，请您过目... 请慢走，欢迎下次光临!
        
        总结
        
        本章节分享了通过表达式条件来限制触发器执行，这样不仅提升DML操作的性能，同时还能利用触发器实现复杂的功能。
        
        最后通过一个经典的帐单结算的案例，演示了条件触发器，会在订单转为完成状态时自动生成帐单。
        
        结尾
        
        非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！
        
        作者邮箱：study@senllang.onaliyun.com
        如有错误或者疏漏欢迎指出，互相学习。
        
        注：未经同意，不得转载！