项目中有个项目编号字段需要自动赋值,格式为P0001、P0002......P0010......
通过触发器的形式实现自动赋值
首先需要创建一个从1开始的序列(xmbm_seq)
CREATE SEQUENCE "public"."xmbm_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 1000000000
START 1
CACHE 1;
SELECT setval('"public"."xmbm_seq"', 1, true);
ALTER SEQUENCE "public"."xmbm_seq" OWNER TO "postgres";
创建触发器函数
CREATE OR REPLACE FUNCTION "public"."update_xmgl_jbxx_tb_xmbm_funcs"()
RETURNS "pg_catalog"."trigger" AS $BODY$ BEGIN
UPDATE xmgl_jbxx_tb SET xmbm = 'P'||RIGHT('000'||nextval('xmbm_seq'),4) where pk_uid= NEW.PK_UID;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
创建触发器(每次插入一条数据就执行触发器函数,给项目编号字段自动赋值)
CREATE TRIGGER "update_xmgl_jbxx_tb_xmbm_trigger" AFTER INSERT ON "public"."xmgl_jbxx_tb"
FOR EACH ROW
EXECUTE PROCEDURE "public"."update_xmgl_jbxx_tb_xmbm_funcs"();