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
