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

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