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
