asked on
MySQL Trigger
First, let me start off by saying I do not like the use of Triggers. That said, I do not know of any other option at this point. I need a temporary solution to the following problem and came up with the use of a Trigger.
The issue I have is I am calling an API and sending a request that will insert the following information into a mysql table. The API is limited and does not allow one of the columns fields to accept what I need. The data inserted comes in as -2 everytime and I need the data to be set from a related table. I though a Before Insert Trigger based on if the field = -2. The field would then be set with information from the related table based on the matching field. Here is some more info to clarify this:
Table 1 (Data being Inserted)
field 1 = "name"
field 2 = "-2"
Table 2
field 1 ="name" (matches field 1 on table 1)
field 2 = "75"
When an INSERT is performed, the Trigger will replace the incoming -2 data and use the related info from Table 2, field 2 using field 1 as a match.
I hope this information is enough for someone trying to help with this. I am having trouble figuring if this should be a BEFORE or AFTER Trigger as well as limiting it to working when field2 on Table 1 is = -2.
This is also temporary until the API being used can accept Field 2 as a variable on the original request.
The issue I have is I am calling an API and sending a request that will insert the following information into a mysql table. The API is limited and does not allow one of the columns fields to accept what I need. The data inserted comes in as -2 everytime and I need the data to be set from a related table. I though a Before Insert Trigger based on if the field = -2. The field would then be set with information from the related table based on the matching field. Here is some more info to clarify this:
Table 1 (Data being Inserted)
field 1 = "name"
field 2 = "-2"
Table 2
field 1 ="name" (matches field 1 on table 1)
field 2 = "75"
When an INSERT is performed, the Trigger will replace the incoming -2 data and use the related info from Table 2, field 2 using field 1 as a match.
I hope this information is enough for someone trying to help with this. I am having trouble figuring if this should be a BEFORE or AFTER Trigger as well as limiting it to working when field2 on Table 1 is = -2.
This is also temporary until the API being used can accept Field 2 as a variable on the original request.
MySQL Server
Last Comment
bundles35
And I am presuming this has to be real-time transaction and not a periodic batch update ?
ASKER
This is real-time
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
OK, Using the actual field names, this is what added to the DB Triggers:
DELIMITER $$
DROP TRIGGER IF EXISTS Field2_BI $$
CREATE TRIGGER Field2_BI BEFORE INSERT ON studentcourse FOR EACH ROW
BEGIN
IF NOT (NEW.SessionID <=> "-2") THEN
BEGIN
DECLARE CourseID TEXT DEFAULT NULL; -- using
DECLARE SessionID INT DEFAULT NULL; -- the
SELECT CourseID, SessionID
FROM coursesession
WHERE CourseID = NEW.CourseID
INTO CourseID, SessionID;
SET NEW.CourseID = CourseID, NEW.SessionID = SessionID;
END;
END IF;
END $$
DELIMITER ;
More information on tables:
studentcourse (table 1)
CourseID (field 1)
Session ID (field 2)
coursession (table 2)
CourseID
SessionID
does it matter that the field names are the same?
DELIMITER $$
DROP TRIGGER IF EXISTS Field2_BI $$
CREATE TRIGGER Field2_BI BEFORE INSERT ON studentcourse FOR EACH ROW
BEGIN
IF NOT (NEW.SessionID <=> "-2") THEN
BEGIN
DECLARE CourseID TEXT DEFAULT NULL; -- using
DECLARE SessionID INT DEFAULT NULL; -- the
SELECT CourseID, SessionID
FROM coursesession
WHERE CourseID = NEW.CourseID
INTO CourseID, SessionID;
SET NEW.CourseID = CourseID, NEW.SessionID = SessionID;
END;
END IF;
END $$
DELIMITER ;
More information on tables:
studentcourse (table 1)
CourseID (field 1)
Session ID (field 2)
coursession (table 2)
CourseID
SessionID
does it matter that the field names are the same?
ASKER
I was able to solve this. I figured what was supposed to be different. Here is the final code:
Thank you for your solution to this.
DELIMITER $$
DROP TRIGGER IF EXISTS Field2_BI $$
CREATE TRIGGER Field2_BI BEFORE INSERT ON studentcourse FOR EACH ROW
BEGIN
IF NEW.SessionID = "-2" THEN
BEGIN
DECLARE cID TEXT DEFAULT NULL; -- using
DECLARE sID INT DEFAULT NULL; -- the
SELECT CourseID, SessionID
FROM coursesession
WHERE CourseID = NEW.CourseID
INTO cID, sID;
SET NEW.CourseID = cID, NEW.SessionID = sID;
END;
END IF;
END $$
DELIMITER ;
Thank you for your solution to this.
Now, of course, the question is what should happen when SessionID does not equal -2? :)
Always happy to help.
Always happy to help.
ASKER
Thank you again for your help
Were it me, I would definitely perform data validation - suppose the application attempts to insert '-1' or 'khlkdjhlashd' - data can get corrupted in transition without detection. Any code you write needs to take the real world into consideration - malware and interference happen. :P