Solved

MySQL Trigger

Posted on 2016-09-14
8
43 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 15

Accepted Solution

by:
WalkaboutTigger earned 500 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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

726 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