背景:对老系统进行升级,新系统上线,兼容并行运行一段时间,环境限制,不能使用数据库同步工具,使用数据库触发器进行兼容。
创建触发器
CREATE TRIGGER sync_tz_ly_event_trigger AFTER INSERT
OR UPDATE
OR DELETE ON tz_ly_event FOR EACH ROW
EXECUTE FUNCTION sync_tz_ly_event_records ( );
创建触发函数
CREATE OR REPLACE FUNCTION "sync_tz_ly_event_records"()
RETURNS "pg_catalog"."trigger" AS $BODY$ BEGIN
IF
TG_OP = 'INSERT' THEN
INSERT INTO training_course_new ( record_id,class_name )
VALUES
( NEW.id,NEW.title );
ELSIF TG_OP = 'UPDATE' THEN
UPDATE training_course_new
SET class_name = NEW.title
WHERE
record_id = CAST(OLD.id AS TEXT);
ELSIF TG_OP = 'DELETE' THEN
DELETE
FROM
training_course_new
WHERE
record_id = CAST(OLD.id AS TEXT);
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
CREATE OR REPLACE FUNCTION "sync_tz_ly_event_records"()
RETURNS "pg_catalog"."trigger" AS $BODY$ BEGIN
IF
TG_OP = 'INSERT' THEN-- 同心迁移活动表
INSERT INTO training_course_new (
record_id,
class_name,
dept_name,
evt_time,
address,
evt_scale,
train_man_num,
CONTENT,
poster_code,
reg_bgn_date,
reg_end_date,
area_code,
DOMAIN,
publish_status,
audit_by,
audit_date,
audit_comment,
-- 因数据缺少,使用报名时间作为活动时间
start_time,
end_time,
is_apply,
ly_user_id,
create_user,
create_time,
modify_user,
modify_time,
sort,
-- 活动类型
TYPE,
-- 截止时间
expire_time,
-- 报道时间为活动开始时间
report_time
)
VALUES
(
NEW.ID,
NEW.title,
NEW.host_unit,
NEW.evt_time,
NEW.evt_address,
NEW.evt_scale,
NEW.reg_max_num,
NEW.description,
NEW.poster_url,
NEW.reg_bgn_date,
NEW.reg_end_date,
NEW.area_code,
NEW.DOMAIN,
NEW.STATE,
NEW.audit_by,
NEW.audit_date,
NEW.audit_comment,
-- 因数据缺少,使用报名时间作为活动时间
-- bgn_date,
-- end_date,
NEW.reg_bgn_date,
NEW.reg_end_date,
NEW.is_apply,
NEW.ly_user_id,
NEW.created_by,
NEW.created_date,
NEW.last_modified_by,
NEW.last_modified_date,
NEW.sort,
-- 活动类型
2,
-- 截止时间
NEW.reg_end_date,
-- 因数据缺少,报道时间,用报名结束时间
NEW.reg_end_date
);
-- 更新create_org_id,create_org_name字段 ,创建人可见改为部门可见
UPDATE training_course_new tcn
SET tcn.create_org_id = dept.dept_id,
tcn.create_org_name = dept.dept_name
FROM
training_course_new course
LEFT JOIN sys_users users ON users."id" = course.create_user
LEFT JOIN sys_dept dept ON dept.dept_id = users.org_id
WHERE
tcn.record_id = course.record_id
AND tcn.record_id = CAST ( NEW.ID AS TEXT );
-- 活动新闻拆分
INSERT INTO training_course_new_news ( training_course_id, is_news, news_content, news_state, news_date, news_author )
VALUES
(
NEW.ID,
NEW.is_news,
NEW.news_content,
CASE
WHEN NEW.news_state = 0 THEN
2
WHEN NEW.news_state = 1 THEN
4 ELSE NULL
END,
NEW.news_date,
NEW.news_author
);
ELSIF TG_OP = 'UPDATE' THEN
UPDATE training_course_new
SET class_name = NEW.title,
dept_name = NEW.host_unit,
evt_time = NEW.evt_time,
address = NEW.evt_address,
evt_scale = NEW.evt_scale,
train_man_num = NEW.reg_max_num,
CONTENT = NEW.description,
poster_code = NEW.poster_url,
reg_bgn_date = NEW.reg_bgn_date,
reg_end_date = NEW.reg_end_date,
area_code = NEW.area_code,
DOMAIN = NEW.DOMAIN,
publish_status = NEW.STATE,
audit_by = NEW.audit_by,
audit_date = NEW.audit_date,
audit_comment = NEW.audit_comment,
start_time = NEW.reg_bgn_date,
end_time = NEW.reg_end_date,
is_apply = NEW.is_apply,
ly_user_id = NEW.ly_user_id,
create_user = NEW.created_by,
create_time = NEW.created_date,
modify_user = NEW.last_modified_by,
modify_time = NEW.last_modified_date,
sort = NEW.sort,
TYPE = 2,
expire_time = NEW.reg_end_date,
report_time = NEW.reg_end_date
WHERE
record_id = CAST ( OLD.ID AS TEXT );
-- 新闻表更新
UPDATE training_course_new_news
SET is_news = NEW.is_news,
news_content = NEW.news_content,
news_state =
CASE
WHEN NEW.news_state = 0 THEN
2
WHEN NEW.news_state = 1 THEN
4 ELSE NULL
END,
news_date = NEW.news_date,
news_author = NEW.news_author
WHERE
training_course_id = CAST ( OLD.ID AS TEXT );
ELSIF TG_OP = 'DELETE' THEN
DELETE
FROM
training_course_new
WHERE
record_id = CAST ( OLD.ID AS TEXT );
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100