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.
bundles35Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Darrell PorterEnterprise Business Process ArchitectCommented:
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
Darrell PorterEnterprise Business Process ArchitectCommented:
And I am presuming this has to be real-time transaction and not a periodic batch update ?
0
bundles35Author Commented:
This is real-time
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Darrell PorterEnterprise Business Process ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bundles35Author Commented:
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
bundles35Author Commented:
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
Darrell PorterEnterprise Business Process ArchitectCommented:
Now, of course, the question is what should happen when SessionID does not equal -2? :)

Always happy to help.
0
bundles35Author Commented:
Thank you again for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.