mysql同步并联两张表_Mysql同一主机两张表结构相同的表数据同步-----触发器

1、执行过程：1)、################################插入

DELIMITER//

CREATE TRIGGER `insert_BI_AppointmentOrder_trigger` AFTER insert ON `BI_AppointmentOrder` FOR EACH ROW BEGIN

--检查当前环境，避免递归.

IF @disable_insert_trigger_o IS NULL THEN

--设置禁用触发器标志.

SET @disable_insert_trigger_o = 1;--插入目标表

INSERT BI_AppointmentOrder_2018 VALUES(new.`Id`, new.`MapOrderId`, new.`UniquenessCode`, new.`DutySourceId`, new.`HospitalId`, new.`DepartmentId`, new.`DoctorId`, new.`UserId`, new.`PatientId`, new.`DutySourceSequenceId`, new.`PatientIdNo`, new.`PatientName`, new.`DutyDate`, new.`MobileNo`, new.`SubmitChannelId`, new.`CancelChannelId`, new.`Status`, new.`ErrorStatus`, new.`AgentId`, new.`CancelAgentId`, new.`OrderCancelType`, new.`SubmitIp`, new.`CancelIp`, new.`MedicalInsurance`, new.`OrderCode`, new.`RecognitionCode`, new.`CancelTime`, new.`OfferTime`, new.`ReplaceWay`, new.`OfferAddress`, new.`MedicalRecordId`, new.`HospitalCardId`, new.`MedicareCardId`, new.`ReimbursementCategoryId`, new.`SmsVerifyCode`, new.`IsFirstTime`, new.`HasPowerHospitalCard`, new.`ChildrenName`, new.`ChildrenBirthday`, new.`ChildrenGender`, new.`ChildrenIdNo`, new.`KinshipName`, new.`JytCardId`, new.`CreateTime`);END IF;--恢复禁用触发器标志.

SET @disable_insert_trigger_o = NULL;END;//DELIMITER ;2)、#############################更新

DELIMITER//

CREATE TRIGGER `update_BI_AppointmentOrder_trigger` AFTER UPDATE ON `BI_AppointmentOrder` FOR EACH ROW BEGIN

--检查当前环境，避免递归.

IF @disable_update_trigger_o IS NULL THEN

--设置禁用触发器标志.

SET @disable_update_trigger_o = 1;--更新目标表

UPDATE BI_AppointmentOrder_2018 SET `MapOrderId`=new.`MapOrderId`, `UniquenessCode`=new.`UniquenessCode`, `DutySourceId`=new.`DutySourceId`, `HospitalId`=new.`HospitalId`, `DepartmentId`=new.`DepartmentId`, `DoctorId`= new.`DoctorId`, `UserId`=new.`UserId`, `PatientId`=new.`PatientId`, `DutySourceSequenceId`=new.`DutySourceSequenceId`, `PatientIdNo`=new.`PatientIdNo`, `PatientName`=new.`PatientName`, `DutyDate`=new.`DutyDate`, `MobileNo`=new.`MobileNo`, `SubmitChannelId`=new.`SubmitChannelId`, `CancelChannelId`=new.`CancelChannelId`, `Status`=new.`Status`, `ErrorStatus`=new.`ErrorStatus`, `AgentId`=new.`AgentId`, `CancelAgentId`=new.`CancelAgentId`, `OrderCancelType`=new.`OrderCancelType`, `SubmitIp`=new.`SubmitIp`, `CancelIp`=new.`CancelIp`, `MedicalInsurance`=new.`MedicalInsurance`, `OrderCode`= new.`OrderCode`, `RecognitionCode`=new.`RecognitionCode`, `CancelTime`=new.`CancelTime`, `OfferTime`=new.`OfferTime`, `ReplaceWay`=new.`ReplaceWay`, `OfferAddress`=new.`OfferAddress`, `MedicalRecordId`=new.`MedicalRecordId`, `HospitalCardId`=new.`HospitalCardId`, `MedicareCardId`=new.`MedicareCardId`, `ReimbursementCategoryId`=new.`ReimbursementCategoryId`, `SmsVerifyCode`=new.`SmsVerifyCode`, `IsFirstTime`=new.`IsFirstTime`, `HasPowerHospitalCard`=new.`HasPowerHospitalCard`, `ChildrenName`=new.`ChildrenName`, `ChildrenBirthday`=new.`ChildrenBirthday`, `ChildrenGender`=new.`ChildrenGender`, `ChildrenIdNo`=new.`ChildrenIdNo`, `KinshipName`=new.`KinshipName`, `JytCardId`=new.`JytCardId`, `CreateTime`=new.`CreateTime` WHERE Id =old.Id ;END IF;--恢复禁用触发器标志.

SET @disable_update_trigger_o = NULL;END;//DELIMITER ;3)、#######################################删除

DELIMITER//

CREATE TRIGGER`delete_BI_AppointmentOrder_trigger`

AFTERDELETE ON `BI_AppointmentOrder` FOR EACH ROW begin

--检查当前环境，避免递归.

IF @disable_delete_trigger_o IS NULL THEN

--设置禁用触发器标志.

SET @disable_delete_trigger_o = 1;--删除目标表

DELETE FROM BI_AppointmentOrder_2018 WHERE Id =old.Id ;END IF;--恢复禁用触发器标志.

SET @disable_delete_trigger_o = NULL;END;//DELIMITER ;