Solved

MySQL Trigger

Posted on 2016-09-14
8
35 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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