条件触发器
专栏内容：
- 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
如有错误或者疏漏欢迎指出，互相学习。
注：未经同意，不得转载！
版权归原作者 韩楚风 所有， 如有侵权，请联系我们删除。