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指的是触发器监听的某表某行的新数据和未更新前的数据