Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL Trigger

Posted on 2016-09-14
8
Medium Priority
?
50 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

610 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