4 Replies Latest reply on Jun 13, 2008 12:36 PM by (BoomTk1)

    Can anyone help with a custom trigger?

      Hi - I know this is a tough one and appreciate anyone's help!

      I'm trying to create a custom trigger that confirms 2 fields belong to the same record. If they do, proceed with insert transaction.

      I have gone through the tutorial (from the old Interakt Discussion Board tutorial at http://www.interaktonline.com/Documentation/MXKollection/075000_tutorialdiscussionboard.h tm) on how to create a custom trigger that checks to see whether a duplicate message and subject exist in the table and if so, stop the insert. I have this working fine, using my own table and fields.

      Can anyone help me to change the script here to confirm that both fields belong to the same record? Here is what I have right now:

      $query = "SELECT * FROM dan_camptrans_login_familyid_confirmid WHERE family_id = ".KT_escapeForSql($tNG->getColumnValue("family_id"),$tNG->getColumnType("family_id"))." AND confirm_id = ".KT_escapeForSql($tNG->getColumnValue("familyconfirm_id"),$tNG->getColumnType("familycon firm_id"));
      $result = $tNG->connection->Execute($query);
      if(!$result) {
      $error = new tNG_error("Could not access database!",array(),array());
      return $error;
      } else {
      if($numberOfRecords = $result->recordCount()) {
      $uniqueFailed = new tNG_error("There is already a message with the same subject and content!",array(),array());
      return $uniqueFailed;
      } else {
      return NULL;
      }
      }

      Any help would be SUPER appreciated!
      Dan
        • 1. Re: Can anyone help with a custom trigger?
          Level 1
          BoomTk1,

          Can you please explaing "I'm trying to create a custom trigger that confirms 2 fields belong to the same record." in a little more detail.

          Also, you say "I have this working fine, using my own table and fields", so I'm not sure what you need changed on the sample code you provided.

          Please clarify what you mean by "confirm that both fields belong to the same record" as I'm not sure what you mean. If you are doing an insert transaction, then there is no record in the database yet so I don't think the phrase "same record" applies.

          Are you just trying to check for duplicate records matching 2 different fields before Inserting a new record? If so, what is wrong with the code you already have.

          Please give some more information and I will try to do my best to help.

          Thanks,

          Shane
          • 2. Re: Can anyone help with a custom trigger?
            Level 1
            Thank you very much Shane for responding.

            Right now, if I fill out the form and enter any numbers in the family_id and familyconfirm_id fields that are currently NOT in the dan_camptrans_login_familyid_confirmid table, the form submits fine. If however, both fields already exist within the same record, the error is returned.

            I guess what I am looking for is the opposite. When the form is submitted, the table dan_camptrans_login_familyid_confirmid is checked. If both field entries are not within the same record, return the error.

            Basically, I am sending out a user ID and confirmation number via mail to users. When they register they need to enter in both of these numbers and only if they match the records will the registration proceed.

            The main recordset/form that this custom trigger is attached to is one for my user registration table. This custom trigger and associated table is only to confirm the user registering has the correct information to register.

            Thanks again - I hope that I am explaining this clearly.
            Dan
            • 3. Re: Can anyone help with a custom trigger?
              Level 1
              Dan,

              So does the table dan_camptrans_login_familyid_confirmid just store data to check the user ID and confirmation number? Then if the user ID and confirmation number are correct you are doing an Insert transaction to a "DIFFERENT" table from dan_camptrans_login_familyid_confirmid?

              If this is the case, you need be able to pass the primary key of the record in table dan_camptrans_login_familyid_confirmid that you want to check the user ID and confirmation number against. So this primary key would probably be passed with the registration link in the email.

              Please confirm if you are working with 2 different tables, one to store the user ID and confirmation number for checking and another to store the registration information if the check is successful.

              Shane
              • 4. Re: Can anyone help with a custom trigger?
                Level 1
                Hi Shane - thank you - yes I am working with two tables and have found out the answer to creating the custom trigger. The guys over at phpfreaks were able to give me some excellent guidance. Here is what finally worked:

                $query = "SELECT * FROM dan_camptrans_login_familyid_confirmid WHERE family_id = ".KT_escapeForSql($tNG->getColumnValue("family_id"),$tNG->getColumnType("family_id"))." AND confirm_id = ".KT_escapeForSql($tNG->getColumnValue("familyconfirm_id"),$tNG->getColumnType("familycon firm_id"));
                $result = $tNG->connection->Execute($query);
                if(!$result) {
                $error = new tNG_error("Could not access database!",array(),array());
                return $error;
                } else {
                if($result->recordCount() == 1) {
                return NULL;
                } else {
                $uniqueFailed = new tNG_error("Sorry! The Family ID and confirmation number do not match our records!",array(),array());
                return $uniqueFailed;

                Thank you again for your help and time!
                Dan