How to update a table in another database based on an update/insert to a table?
I have searched through many answers and am getting issues with a MySQL trigger. When a record is inserted/updated on DB1.p_264 I want it to automatically update/insert the same on DB2.p_264
The triggers have correct syntax; however the updates are not happening. (e.g. I have 155 records to start in DB2.p_264 and DB1.p_264. I insert 322 additional records into DB1.p_264 and still only have 155 records in DB2.p_264). Please suggest how I can change:
USE DB1;
DROP TRIGGER IF EXISTS DB1.trCreateDev2;
DELIMITER //
CREATE TRIGGER trCreateDev2
AFTER INSERT ON DB1.264_P
FOR EACH ROW BEGIN
INSERT INTO DB2.264_P
(Source_Name,
Source_Detail_Name,
P_ID,
P_NPI_Number,
P_First_Name,
P_Middle_Name,
P_Last_Name,
P_Full_Name,
P_Title,
P_Specialty,
Gender,
Birthdate,
P_Phone,
P_Email,
P_User_Name,
P_Religion,
P_Ethnicity,
P_Type,
P_Address_Line_1,
P_Address_Line_2,
P_City,
P_State,
P_ZIP,
Country,
P_Identifier_Type,
Identifier_Value,
Identifier_Status,
Identifier_Effective_Date,
P_Specialty_Type,
Specialty_Name,
Specialty_Status,
Specialty_Effective_Date)
VALUES
('CLIN_EVOLV_CNYCC_LIBERTYRESOURCE',
NEW.Source_Detail_Name,
NEW.P_ID,
NEW.P_NPI_Number,
NEW.P_First_Name,
NEW.P_Middle_Name,
NEW.P_Last_Name,
NEW.P_Full_Name,
NEW.P_Title,
NEW.P_Specialty,
NEW.Gender,
CASE
WHEN NEW.Birthdate like 'NUL%' THEN '0000-00-00 00:00:00'
WHEN NEW.Birthdate like '00:00.0%' THEN '0000-00-00 00:00:00'
ELSE NEW.Birthdate
END,
NEW.P_Phone,
NEW.P_Email,
NEW.P_User_Name,
NEW.P_Religion,
NEW.P_Ethnicity,
NEW.P_Type,
NEW.P_Address_Line_1,
NEW.P_Address_Line_2,
NEW.P_City,
NEW.P_State,
NEW.P_ZIP,
NEW.Country,
NEW.P_Identifier_Type,
NEW.Identifier_Value,
NEW.Identifier_Status,
CASE
WHEN NEW.Identifier_Effective_Date like 'NUL%' THEN '0000-00-00 00:00:00'
WHEN NEW.Identifier_Effective_Date like '00:00.0%' THEN '0000-00-00 00:00:00'
ELSE NEW.Identifier_Effective_Date
END,
NEW.P_Specialty_Type,
NEW.Specialty_Name,
NEW.Specialty_Status,
CASE
WHEN NEW.Specialty_Effective_Date like 'NUL%' THEN '0000-00-00 00:00:00'
WHEN NEW.Specialty_Effective_Date like '00:00.0%' THEN '0000-00-00 00:00:00'
ELSE NEW.Specialty_Effective_Date
END);
END//
DROP TRIGGER IF EXISTS DB1.trCreateDev;
DELIMITER //
CREATE TRIGGER trCreateDev
AFTER UPDATE ON DB1.264_P
FOR EACH ROW BEGIN
UPDATE DB2.264_P
SET
Source_Name = NEW.Source_Name,
Source_Detail_Name = NEW.Source_Detail_Name,
P_NPI_Number = NEW.P_NPI_Number,
P_First_Name = NEW.P_First_Name,
P_Middle_Name = NEW.P_Middle_Name,
P_Last_Name = NEW.P_Last_Name,
P_Full_Name = NEW.P_Full_Name,
P_Title = NEW.P_Title,
P_Specialty = NEW.P_Specialty,
Gender = NEW.Gender,
Birthdate = CASE
WHEN NEW.Birthdate like 'NUL%' THEN '0000-00-00 00:00:00'
WHEN NEW.Birthdate like '00:00.0%' THEN '0000-00-00 00:00:00'
ELSE Birthdate = NEW.Birthdate
END,
P_Phone = NEW.P_Phone,
P_Email = NEW.P_Email,
P_User_Name = NEW.P_User_Name,
P_Religion = NEW.P_Religion,
P_Ethnicity = NEW.P_Ethnicity,
P_Type = NEW.P_Type,
P_Address_Line_1 = NEW.P_Address_Line_1,
P_Address_Line_2 = NEW.P_Address_Line_2,
P_City = NEW.P_City,
P_State = NEW.P_State,
P_ZIP = NEW.P_ZIP,
Country = NEW.Country,
P_Identifier_Type = NEW.P_Identifier_Type,
Identifier_Value = NEW.Identifier_Value,
Identifier_Status = NEW.Identifier_Status,
Identifier_Effective_Date = CASE
WHEN NEW.Identifier_Effective_Date like 'NUL%' THEN '0000-00-00 00:00:00'
WHEN NEW.Identifier_Effective_Date like '00:00.0%' THEN '0000-00-00 00:00:00'
ELSE Identifier_Effective_Date = NEW.Identifier_Effective_Date
END,
P_Specialty_Type = NEW.P_Specialty_Type,
Specialty_Name = NEW.Specialty_Name,
Specialty_Status = NEW.Specialty_Status,
Specialty_Effective_Date = CASE
WHEN NEW.Specialty_Effective_Date like 'NUL%' THEN '0000-00-00 00:00:00'
WHEN NEW.Specialty_Effective_Date like '00:00.0%' THEN '0000-00-00 00:00:00'
ELSE Specialty_Effective_Date = NEW.Specialty_Effective_Date
END
WHERE P_ID = NEW.P_ID;
END//