mysql 触发器一个表改变另一个表也改变

mysql 触发器一个表改变另一个也改变

主表：

副表：

删除：

DELIMITER $$
USE `motooling`$$
DELIMITER ;
DROP TRIGGER IF EXISTS `pm_pg_member_delete`;
DELIMITER ;;
CREATE TRIGGER `pm_pg_member_delete` AFTER DELETE ON `user_company` FOR EACH ROW BEGIN
SELECT dep_id INTO @procId FROM department WHERE dep_code='Production_Dept' AND company_id=old.company_id;
SELECT dep_id INTO @depIds FROM department WHERE dep_id=old.dep_id AND CONCAT('|', all_upper_ids, '|') REGEXP CONCAT('|', @procId, '|');
IF old.dep_id IN (@depIds) THEN
DELETE FROM pm_pg_member WHERE pg_id=old.dep_id AND member_id=old.uid;
END IF;
END
;;
DELIMITER ;

添加：

DELIMITER $$
USE `motooling`$$
DELIMITER ;
DROP TRIGGER IF EXISTS `pm_pg_member_insert`;
DELIMITER ;;
CREATE TRIGGER `pm_pg_member_insert` AFTER INSERT ON `user_company` FOR EACH ROW BEGIN
SELECT dep_id INTO @procId FROM department WHERE dep_code='Production_Dept' AND company_id=new.company_id;
SELECT dep_id INTO @depIds FROM department WHERE dep_id=new.dep_id AND CONCAT('|', all_upper_ids, '|') REGEXP CONCAT('|', @procId, '|');
IF new.dep_id IN (@depIds) THEN
SELECT dep_id INTO @depId FROM user_company WHERE id=new.id;
SELECT uid INTO @uid FROM user_company WHERE id=new.id;
SELECT work_name INTO @workName FROM user_company WHERE id=new.id;
SELECT is_leader INTO @isLeader FROM user_company WHERE id=new.id;
SELECT pri INTO @pri FROM user_company WHERE id=new.id;
SELECT is_schedule INTO @isSchedule FROM user_company WHERE id=new.id;
SELECT skill_level_id INTO @skillLevelId FROM user_company WHERE id=new.id;
SELECT skill_level_name INTO @skillLevelName FROM user_company WHERE id=new.id;
SELECT furlough_start_date INTO @furloughStartDate FROM user_company WHERE id=new.id;
SELECT furlough_end_date INTO @furloughEndDate FROM user_company WHERE id=new.id;
SELECT is_show_effic INTO @isShowEffic FROM user_company WHERE id=new.id;
SELECT work_state INTO @workState FROM user_company WHERE id=new.id;
SELECT last_pop_id INTO @LastPopId FROM user_company WHERE id=new.id;
SELECT handler_id INTO @handlerId FROM user_company WHERE id=new.id;
SELECT created_at INTO @createdAt FROM user_company WHERE id=new.id;
SELECT updated_at INTO @updatedAt FROM user_company WHERE id=new.id;
SELECT STATUS INTO @status FROM user_company WHERE id=new.id;
INSERT INTO pm_pg_member (pg_id, member_id, member_name, is_leader, pri, is_schedule, skill_level_id, skill_level_name,
furlough_start_date, furlough_end_date, is_show_effic, member_status, last_pop_id,
handler_id, created_at, updated_at, STATUS) VALUES
(@depId,@uid,@workName,@isLeader,@pri,@isSchedule,@skillLevelId,@skillLevelName,@furloughStartDate,@furloughEndDate,@isShowEffic,@workState
,@LastPopId,@handlerId,@createdAt, @updatedAt,@status);
END IF;
END
;;
DELIMITER ;

修改：

DELIMITER $$
USE `motooling`$$
DELIMITER ;
DROP TRIGGER IF EXISTS `pm_pg_member_update`;
DELIMITER ;;
CREATE TRIGGER `pm_pg_member_update` AFTER UPDATE ON `user_company` FOR EACH ROW BEGIN
SELECT dep_id INTO @procId FROM department WHERE dep_code='Production_Dept' AND company_id=new.company_id;
SELECT dep_id INTO @depIds FROM department WHERE dep_id=new.dep_id AND CONCAT('|', all_upper_ids, '|') REGEXP CONCAT('|', @procId, '|');
IF new.dep_id IN (@depIds) THEN
UPDATE pm_pg_member SET pg_id=new.dep_id, member_id=new.uid, member_name=new.work_name, is_leader=new.is_leader, pri=new.is_leader, is_schedule=new.is_schedule,
skill_level_id=new.skill_level_id, skill_level_name=new.skill_level_name,
furlough_start_date=new.furlough_start_date, furlough_end_date=new.furlough_end_date, is_show_effic=new.is_show_effic,
member_status=new.work_state, last_pop_id=new.last_pop_id,
handler_id=new.handler_id, created_at=new.created_at, updated_at=new.updated_at, STATUS=new.status
WHERE pg_id=old.dep_id AND member_id=old.uid;
END IF;
END
;;
DELIMITER ;

注：删除只有old，新增只有new，修改既有old又有new

原文地址 https://blog.csdn.net/XCL18215166914/article/details/81564881

继续阅读与本文标签相同的文章

None
最新文章 最热文章
收藏 打印

特别推荐 2026年05月26日 星期二

精彩发现

热门标签

阻止表单提交 窗口 关闭浏览器 清除 CDATA css文件 光标位置 坐标 javascrpt !important 超链接 数字

相关文章

1
Spring Cloud Config-快速开始
1855人阅读
2
无人机袭击了委内瑞拉总统，我们该如何防范从天而降的杀人武器？
358人阅读
3
Ceph-ansible 部署Ceph
1139人阅读