PostgreSQL触发器数据同步

背景

ctm02brss同步数据应用提供标准的视图库，支持把第三方的组织，人员，人脸数据同步到海康EBG的平台上

主要关键数据

组织：代表学校组织架构，如学院、专业、班级等，教师需在非班级节点，学生必须在班级节点上

人员：包含教职工和学生

人脸：教职工和学生的人脸信息

对接方式

常见的对接方式一般分为三种

1、第三方只提供数据库，我们则需要通过编写视图SQL的形式在第三方数据库上建立ctm02brss支持的标准视图

2、第三方只提供接口，我们则需要通过HDI或者定制程序的方式，把数据同步到我们的标准PG库中

3、第三方做开发，直接把标准数据推到我们标准的PG库中

广西XXX大学人员同步

广西XXX大学属于第三种对接方式，同时还需要在该方式的基础上加上触发器做数据清理

以上是XXX大学创建的中间库表，tb_middle_xxx属于标准的ctm02brss支持的中间库表，tb_temp_xxx属于临时中间库表

例如需要同步中XX大学的XXX学院教师信息表，字段如下

我们则需要创建一张对应的临时表tb_temp_science_teacher

CREATE TABLE public.tb_temp_science_teacher (zgh varchar NULL, -- 职工号xm varchar NULL, -- 姓名xb varchar NULL, -- 性别sfzjlxdm varchar NULL, -- 身份证件类型代码sfzjlxmc varchar NULL, -- 身份证件类型名称sfzh varchar NULL, -- 身份证号dwdm varchar NULL, -- 单位代码dwmc varchar NULL, -- 单位名称dqztdm varchar NULL, -- 当前状态代码dqztmc varchar NULL, -- 当前状态名称sjh varchar NULL -- 手机号
);
COMMENT ON TABLE public.tb_temp_science_teacher IS '赛恩斯学院教师信息临时表';COMMENT ON COLUMN public.tb_temp_science_teacher.zgh IS '职工号';
COMMENT ON COLUMN public.tb_temp_science_teacher.xm IS '姓名';
COMMENT ON COLUMN public.tb_temp_science_teacher.xb IS '性别';
COMMENT ON COLUMN public.tb_temp_science_teacher.sfzjlxdm IS '身份证件类型代码';
COMMENT ON COLUMN public.tb_temp_science_teacher.sfzjlxmc IS '身份证件类型名称';
COMMENT ON COLUMN public.tb_temp_science_teacher.sfzh IS '身份证号';
COMMENT ON COLUMN public.tb_temp_science_teacher.dwdm IS '单位代码';
COMMENT ON COLUMN public.tb_temp_science_teacher.dwmc IS '单位名称';
COMMENT ON COLUMN public.tb_temp_science_teacher.dqztdm IS '当前状态代码';
COMMENT ON COLUMN public.tb_temp_science_teacher.dqztmc IS '当前状态名称';
COMMENT ON COLUMN public.tb_temp_science_teacher.sjh IS '手机号';

同时编写触发器，每当tb_temp_science_teacher插入一条数据，就通过触发器把人移到对应的组织目录之下

--删除赛恩斯学院教职工临时表触发器
drop trigger if exists trigger_tb_temp_science_teacher on public.tb_temp_science_teacher;
--删除赛恩斯学院教职工临时表存储过程
drop function if exists public.procedure_tb_temp_science_teacher();
--创建赛恩斯学院教职工临时表存储过程
create or replace function public.procedure_tb_temp_science_teacher() returns trigger as $BODY$declarev_org_id varchar;begincase when TRIM(NEW.dwmc) = '广西XXX大学赛恩斯新医药学院' then v_org_id := 'gxzyydxsesxyyxy' || 'ScienceTeacherOrgNode';when TRIM(NEW.dwmc) = '赛院学工处' then v_org_id := 'saiyuanxuegongchu' || 'ScienceTeacherOrgNode';when TRIM(NEW.dwmc) = '学工处' then v_org_id := 'xuegonchu' || 'ScienceTeacherOrgNode';when TRIM(NEW.dwmc) = '专职辅导员' then v_org_id := 'zhuanzhifudaoyuan' || 'ScienceTeacherOrgNode'; else v_org_id := 'other' || 'ScienceTeacherOrgNode';   end case;      --插入赛恩斯教职工组织新根节点insert into public.tb_middle_org (org_id, org_name, parent_org_id, status, org_type, update_time) values ('scienceTeacherOrgNode', '赛恩斯教职工组织新', 'RootOrgNode', 0, 1, current_timestamp) on conflict(org_id) do nothing;--插入教师所属组织节点insert into public.tb_middle_org (org_id, org_name, parent_org_id, status, org_type, update_time) values (v_org_id, NEW.dwmc, 'scienceTeacherOrgNode', 0, 1, current_timestamp) on conflict(org_id) do nothing;--更新赛恩斯学院教职工insert into public.tb_middle_person (job_no, name, person_type, sex, org_id, mobile, cert_type, certificate_no, status, update_time)values (NEW.zgh,NEW.xm,1,case when NEW.xb = '男' then 1 when NEW.xb = '女' then 2 else 0 end,v_org_id,NEW.sjh,case when NEW.sfzjlxmc = '身份证' then 111 else 990 end,NEW.sfzh,0,current_timestamp)on conflict(job_no) do updatesetname = NEW.xm,person_type = 1,sex = (case when NEW.xb = '男' then 1 when NEW.xb = '女' then 2 else 0 end),org_id = v_org_id,mobile = NEW.sjh,cert_type = (case when NEW.sfzjlxmc = '身份证' then 111 else 990 end),certificate_no = NEW.sfzh,status = 0,update_time = current_timestamp;return null;end;
$BODY$ language plpgsql;
--创建赛恩斯学院教职工临时表触发器
create trigger trigger_tb_temp_science_teacher after insert or update on public.tb_temp_science_teacher for each row execute procedure public.procedure_tb_temp_science_teacher();

触发器释义

1、先判断插入该人员的组织是否存在，存在则赋值对应的组织id，不存在则创建对应的组织节点

2、把临时表中的数据字段和标准视图的字段一一对应上，同时编写异常情况下处理的逻辑

3、把触发器和临时表绑定

在XXX的etl平台上开始推送数据