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

条件触发器

​专栏内容：

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

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

文章目录

概述

触发器trigger 是语句级的(row-level) 时, 被触发的频率是相当高的，会带来一定的性能开销。

今天给大家分享一种带有条件表达式的触发器，只在条件满足时触发，其它情况下并不会执行，这样既能满足触发器的复杂处理，也能满足性能的要求。

原理机制

下面就来详细了解一下带条件表达式的触发器吧。

语法

带条件表达式的触发器的SQL语法格式如下：

CREATETRIGGER trigger_name
{BEFORE |AFTER} { INSERTORUPDATEORDELETEORTRUNCATE }
ON table_name
WHEN condition
FOR[EACH]ROWEXECUTEFUNCTION function_name();

说明

• 这里与普通触发器的语法类似，可以参见前面的分享；
• 如果是行级的触发器，需要指定BEFORE或AFTER以及对应的事件；
• 此处通过WHEN子句指定触发的条件，只有在条件满足时才能执行；
• 触发器类型指定，行级触发器为FOR EACH ROW；
• 最后指定触发器执行函数；

原理

在行级触发器中：

• 可以访问修改前的行与修改后的行，通过访问这两行的值设定条件表达式。
• 修改前的行使用OLD.column_name进行引用，修改后的行引用方式为 NEW.column_name；
• INSERT命令没有OLD行，DELETE命令没有NEW行；
• INSTEAD OF不支持设置条件；

而在语句级触发器中没有可引用的行数据。

案例

下面我们通过一个案例来看看条件触发器的使用场景。

场景介绍

当在饭店消费时，会先点一些菜，过程中又会根据需要再加一些菜或者饮品，最后再统一结帐。

对于这样一个很常见的场景，通过条件触发器可以实现自动计算账单。

数据准备

新建两张表，一张为订单表，记录客户消费情况，以及订单状态；另一张为客户统计，记录客户总的消费金额。

CREATETABLE orders (
    order_id INT GENERATED ALWAYS ASIDENTITYPRIMARYKEY,
    customer_id INTNOTNULL,
    total_amount NUMERICNOTNULLDEFAULT0,statusVARCHAR(20)NOTNULL);CREATETABLE customer_stats (
    customer_id INTPRIMARYKEY,
    total_spent NUMERICNOTNULLDEFAULT0);

创建触发器

下面在orders表上对

insert

,

update

事件创建两个触发器。

创建订单触发器

当有新客人时，会向

orders

表中插入一条新记录；

此时通过触发器自动在

customer_stats

表中也插入客户ID，金额为0，此时订单状态还为

pending

。

CREATEORREPLACEFUNCTION insert_customer_stats()RETURNSTRIGGERAS $$
BEGININSERTINTO customer_stats (customer_id)VALUES(NEW.customer_id);RETURNNULL;END;
$$ LANGUAGE plpgsql;CREATETRIGGER insert_customer_stats_trigger
AFTERINSERTON orders
FOR EACH ROWEXECUTEFUNCTION insert_customer_stats();

结帐付款

客人在过程中可能会变动订单金额，订单状态始终为

pending

, 最后结帐时将订单状态改为

completed

，此时生成帐单。

在

orders

表上增加

UPDATE

事件的条件触发器，只有

NEW.status

为

completed

时才会触发，此时将客户金额插入

customer_stats

表中。

CREATEORREPLACEFUNCTION update_customer_stats()RETURNSTRIGGERAS 
$$
BEGINIF NEW.status='completed'THEN-- Update the total_spent for the customerUPDATE customer_stats
        SET total_spent = total_spent + NEW.total_amount
        WHERE customer_id = NEW.customer_id;ENDIF;RETURNNULL;END;
$$ LANGUAGE plpgsql;CREATETRIGGER update_customer_stats_trigger
AFTERUPDATEON orders
FOR EACH ROWWHEN(OLD.status<>'completed'AND NEW.status='completed')EXECUTEFUNCTION update_customer_stats();

效果验证

好了，来测试一下业务流。

先来了两波客人，分别点了100，200的菜品。

INSERTINTO orders (customer_id, total_amount,status)VALUES(1,100,'pending'),(2,200,'pending');

两波客人都要结帐了，要忙了。

UPDATE orders
SETstatus='completed'WHERE customer_id IN(1,2);

看一下这两波客人的账单：

postgres=> SELECT * FROM customer_stats;
 customer_id | total_spent
-------------+-------------
           1|1002|200(2 rows)

先生/女士，这是您的账单，请您过目... 
请慢走，欢迎下次光临!

总结

本章节分享了通过表达式条件来限制触发器执行，这样不仅提升DML操作的性能，同时还能利用触发器实现复杂的功能。

最后通过一个经典的帐单结算的案例，演示了条件触发器，会在订单转为完成状态时自动生成帐单。

结尾

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！