postgresql 触发器更新操作

最新推荐文章于 2024-09-12 06:14:14 发布

weixin_30588907

最新推荐文章于 2024-09-12 06:14:14 发布

阅读量1.6k

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/fanbi/p/10307435.html

本文详细介绍如何在PostgreSQL中创建触发器与自定义函数，实现数据更新时的状态同步。通过两个具体示例，展示了如何在订单商品表更新时，自动同步修改订单状态及更新时间线字段。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 前言

功能需求：当一张表格某个字段变化，另一张表某个字段写入该值

2 代码

  CREATE OR REPLACE FUNCTION "public"."synStatus"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
 
 declare vl_status  int;
 
 BEGIN

SELECT status INTO vl_status  FROM ordergoods WHERE order_id = NEW.order_id; 
	-- Routine body goes here...
	IF( TG_OP='UPDATE' ) THEN
		UPDATE order SET status = vl_status  WHERE NEW.order_id = order_id;
	END IF;
	RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 10


//触发器
CREATE TRIGGER "triggerSynOrder" AFTER UPDATE OF "status", "timeline" ON "public"."ordergoods"
FOR EACH ROW
EXECUTE PROCEDURE "public"."synStatus"();

功能2：子订单中状态最小的更新到订单上，并更新时间线字段

  CREATE OR REPLACE FUNCTION "public"."synStatus2"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
 
 declare status_min int;
 
 BEGIN

SELECT MIN(status) INTO status_min FROM ordergoods WHERE order_id = NEW.order_id; 
	-- Routine body goes here...
	IF( TG_OP='UPDATE' ) THEN
		UPDATE order SET status = status_min,timeline = concat_ws(',', timeline,cur_timeline_status)),cur_timeline_status = NEW.cur_timeline_status WHERE NEW.order_id = order_id;
	END IF;
	RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 10

//触发器
CREATE TRIGGER "triggerSynOrder" AFTER UPDATE OF "status", "timeline" ON "public"."ordergoods"
FOR EACH ROW
EXECUTE PROCEDURE "public"."synStatus2"();

3 小结

注：方法中NEW和OLD指的是触发器监听的某表某行的新数据和未更新前的数据　　

转载于:https://www.cnblogs.com/fanbi/p/10307435.html