MySQL Forums
Forum List  »  Triggers

How to update a table in another database based on an update/insert to a table?
Posted by: Cassie Breen
Date: March 06, 2018 03:39PM

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//

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.