10 Replies Latest reply on Aug 19, 2008 8:38 AM by (shane_mosier)

    custom trigger

      Hi--
      I am inserting into 2 tables sc_tblBioInfo and sc_tblSemesterInfo. I have 2 fields 'Plantoenroll' and 'year' that are inserted into 2 different fields into sc_tblSemesterInfo. That works fine.

      I also need to insert 'Plantoenroll' and 'year' combined into 1 field called 'Plantoenroll' in sc_tblBioInfo.

      I have created a custom trigger to combine them and insert into sc_tblBioInfo:

      function Trigger_Custom(&$tNG) {
      $Plantoenroll = $tNG->getColumnValue("Plantoenroll");
      $year = $tNG->getColumnValue("year");
      $newSubject = $Plantoenroll . $year;
      $ins_sc_tblBioInfo->addColumn("Plantoenroll","STRING_TYPE","VALUE",$newSubject);
      return null;
      }

      I am getting an error "Column Plantoenroll is not part of the current transaction." How do I make Column Plantoenroll part of the current transaction?

      Is there a better way to insert 2 fields into a table and also insert those 2 fields as 1 field into a different table?

      Thanks for any help.
      --rayne
        • 1. Re: custom trigger
          Level 1
          You can add it as a hidden field to the form, or you can look it up with another SQL query within your trigger, using the primary key from the table used for the form.
          • 2. Re: custom trigger
            Level 1
            Thanks for the suggestions. I tried to add it as a hidden field: <br /> <br /> <form> <input name="Plantoenroll" type="hidden" id="Plantoenroll" value="<?php echo $_POST['Plantolenroll'] . $_POST['year'] ?>" /> <br /> <br />but that didn't seem to work. Is that how to add it as a hidden field? <br />--rayne</form>
            • 3. Re: custom trigger
              Level 1
              Rayne,

              If I understand correctly, you want to insert/update a different table than the table the ADDT form is inserting to.

              The form on your page inserts a new record into sc_tblSemesterInfo table. You also want to insert a new value based on combining two fields into one and insert or update the new value in the sc_tblBioInfo table.

              Unfortunately, you cannot use:

              $ins_sc_tblBioInfo->addColumn("Plantoenroll","STRING_TYPE","VALUE",$newSubject);

              to update a different database table than the one the form is associated with.

              The correct code would be:

              $tNG->addColumn("Plantoenroll","STRING_TYPE","VALUE",$newSubject);

              but using this in a trigger only allows you to add Columns to the trasaction database table that the form is inserting to. You cannot tell it to update a different database table like this.

              The key question is whether you need to do an Update to an existing record in sc_tblBioInfo, or are you a doing fresh an insert in the sc_tblBioInfo table?

              You need to do a custom trigger that does a database call to do an insert or update operation on the sc_tblBioInfotable table. After you combine the values into a new value, are you needing to insert a new record into the sc_tblBioInfotable table, or are you needing to update an existing record in the sc_tblBioInfotable table?

              Shane
              • 4. Re: custom trigger
                Level 1
                Hi Shane--
                Thanks for the reply. I am inserting into both tables, not updating either. I have a form that is filled out and it inserts some of the fields into sc_tblBioInfo ("name", "address", "plan_to_enroll") and some of the fields into sc_tblSemesterInfo (semester, year). These table are related through "studentID". "Semester" and "year" of sc_tblSemesterInfo make up the "Plan_to_enroll" field of sc_tblBioInfo.

                So I need to insert "semester" and "year" separately into sc_tblSemesterInfo and "semester" + "year" as "plantoenroll" into sc_tblBioInfo.

                I didn't design the tables this way but I am stuck with it. I renamed the field in this email to make it (hopefully) clearer.

                Thanks for the help.
                --rayne
                • 5. Re: custom trigger
                  Level 1
                  Rayne,

                  Did you use the ADDT "Insert Into Two Tables" wizard for this one?

                  I haven't used that myself, so I'm not sure what the code it generates looks like.

                  However, if this code you wrote in your custom trigger is correct:

                  $ins_sc_tblBioInfo->addColumn("Plantoenroll","STRING_TYPE","VALUE",$newSubject);

                  It actually needs to be:

                  $ins_sc_tblBioInfo->addColumn("plan_to_enroll","STRING_TYPE","VALUE",$newSubject);

                  Because if plan_to_enroll is the name of the database field, Plantoenroll will not work here.

                  This error "Column Plantoenroll is not part of the current transaction." basically means that ADDT can't match column "Plantoenroll" to an existing column named "Plantoenroll" in your sc_tblBioInfo database table.

                  So if the name of your table field in the sc_tblBioInfo database table is "plan_to_enroll", you need to replace "Plantoenroll" with "plan_to_enroll" in this code:

                  $ins_sc_tblBioInfo->addColumn("plan_to_enroll","STRING_TYPE","VALUE",$newSubject);

                  Hope this helps.

                  Shane
                  • 6. Re: custom trigger
                    Level 1
                    Hi Shane--
                    Sorry for the confusion. The actual field names and table names are:
                    "Semester" and "year" in table sc_tblSemesterInfo
                    "Plantoenroll" in table sc_tblBioInfo

                    I need to insert "Semester" and "year" separately into sc_tblSemesterInfo (which works) and "Semester" + "year" as "Plantoenroll" into sc_tblBioInfo (which doesn't work).

                    The actual custom trigger:

                    function Trigger_Custom(&$tNG) {
                    $Semester = {Semester};
                    $year = {year};
                    $newSubject = $Semester . $year;
                    $ins_sc_tblBioInfo->addColumn("Plantoenroll","STRING_TYPE","VALUE",$newSubject);
                    return null;
                    }

                    The above trigger gives the error message: Column Plantoenroll is not part of the currect transaction... but "Plantoenroll" is the field name in sc_tblBioInfo.

                    Yes I originally used the ADDT "Insert into Two Tables" wizard to set the form up with "Semester" and "year" being inserted into table sc_tblSemesterInfo. Now I am trying to add a custom trigger to insert "Semester" + "year" as "Plantoenroll" into sc_tblBioInfo.

                    Thanks for all your help.
                    --rayne
                    • 7. Re: custom trigger
                      Level 1
                      Rayne,

                      Did you try replacing:

                      $ins_sc_tblBioInfo->addColumn("Plantoenroll","STRING_TYPE","VALUE",$newSubject);

                      with

                      $tNG->addColumn("Plantoenroll","STRING_TYPE","VALUE",$newSubject);

                      If that does not work, please post the ADDT portion of the code. I've never used the "Insert into Two Tables" Wizard, so I'm not sure what the code would look like.

                      Shane
                      • 8. Re: custom trigger
                        mohnkhan Level 1
                        Hi,
                        I am no php wizard but this is what i understood from the conversation.
                        u want to insert into two tables..

                        which are linked by a key in some fashion (pk fk)
                        u can use insert into two tables from ADDT.

                        if u want to manually do this then
                        u can use a trigger..
                        in the trigger u can directly use SQL Insert into table clause..

                        with field names from transaction..

                        if some fields are not in the current transaction and u want to put some default values or foreign keys then u can further select them from other tables and insert them in the same trigger itself.

                        hope this helps..

                        mohnkhan
                        http://www.mohitech.com
                        • 9. Re: custom trigger
                          No, you cannot do a native SQL query.
                          • 10. Re: custom trigger
                            Level 1
                            Hello,

                            I use this kind of SQL query all the time in custom triggers:

                            $query_UpdateUsersTable = "UPDATE `shopper_users_usr` SET `username_usr` = '$username', `password_usr` = '$password', `name_usr` = '$name' WHERE shopper_id_usr = $insertID;";
                            $updateQuery = mysql_query($query_UpdateUsersTable);

                            You can't do this though:

                            $RecordsetShopper = mysql_query($query_RecordsetShopper, $main) or die(mysql_error());

                            It has to be like this:

                            $RecordsetShopper = mysql_query($query_RecordsetShopper);

                            Other wise you will have MySQL errors.

                            Shane