?
Solved

MySQL Trigger

Posted on 2016-09-14
8
Medium Priority
?
48 Views
Last Modified: 2016-09-15
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.
0
Comment
Question by:bundles35
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41798454
If I recall correctly, it would definitely need to be a BEFORE trigger as you want to modify the insertion data.  The problem is that if the transaction is backed out, the data inserted as a result of the BEFORE trigger will still likely be committed as the inserting application isn't aware of the trigger.

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
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41798456
And I am presuming this has to be real-time transaction and not a periodic batch update ?
0
 

Author Comment

by:bundles35
ID: 41798482
This is real-time
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 15

Accepted Solution

by:
WalkaboutTigger earned 2000 total points
ID: 41798526
I am thinking something like

DELIMITER $$

DROP TRIGGER IF EXISTS Field2_BI $$
CREATE TRIGGER Field2_BI BEFORE INSET ON TABLE1 FOR EACH ROW
BEGIN

  IF NOT (NEW.FIELD2 <=> "-2") THEN
    BEGIN
      DECLARE FIELD1 TEXT DEFAULT NULL;      -- using
      DECLARE FIELD2 INT DEFAULT NULL;     -- the
      SELECT FIELD1, FIELD2
        FROM TABLE2
       WHERE FIELD1 = NEW.FIELD1
        INTO my_FIELD1, my_FIELD2;
      SET NEW.FIELD1 = my_FIELD1, NEW.FIELD2 = my_FIELD2;
    END;
  END IF;

END $$
DELIMITER ;

Open in new window

0
 

Author Comment

by:bundles35
ID: 41798676
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?
0
 

Author Comment

by:bundles35
ID: 41799870
I was able to solve this.  I figured what was supposed to be different.  Here is the final code:

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 ;

Open in new window


Thank you for your solution to this.
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41800124
Now, of course, the question is what should happen when SessionID does not equal -2? :)

Always happy to help.
0
 

Author Closing Comment

by:bundles35
ID: 41800158
Thank you again for your help
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question