5 Replies Latest reply on Oct 16, 2008 12:55 PM by Günter Schenk

    transfer single record from table1 to table2

      Hi!

      I have created a dynamic table, listing mysql records from database table1. I would like to add a link to the record listing of data in table1 that would take the user to a page showing a detailed view of the record selected. I guess I could do that with an update record behavior!?

      BUT:

      Once the user has inspected the data displayed in the form I would like the record not to be updated in table1 but rather inserted as a new record into table2 and at the same time have the record deleted from table1.

      So in short I would transfer the record from table1 to table2 with the user being able to make modifications to the data in the proceedings.

      BTW: table1 and table 2 are identical in structure, they only have different names.

      Is there an easy way to do this? I would have to create a custom script otherwise extracting the data from table one, inserting to table2 deleting from table1 and add verifications to the transfer steps to make sure that no data will be lost...

      Thanks Juergen
        • 1. Re: transfer single record from table1 to table2
          Günter Schenk Level 4
          Hi Jürgen,

          I think you´ll have to establish a series of Custom Triggers for this which will have to contain user-defined "INSERT INTO" respectively "DELETE FROM" SQL statements using the transaction values, as the update page´s main transaction is tied to table 1. The DELETE FROM... Custom Trigger will certainly have to be the very last one in that series

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: transfer single record from table1 to table2
            Level 1
            Hi Günter,

            I came up with kind of a similar plan. I did create another page to which I added an "insert record" transaction. The destination of the form data is table2.

            I then filled each field of the insert record form with data from a record set that does pull out the data from table1 filtered by "id". I did use the default value for each field to make the data from table1 the default data in this form. The data is shown to the user and any needed changes can be made...

            If I click insert the data is added to table2 which is great.

            I am a bit lost how to delete the data from table1 though. I did add a delete record transaction. If I keep the defaults, the record is deleted straight away from table1 without even presenting me the new form and without adding the record to table2, which is not what I want.
            So I thought I change the "Start trigger event" to be "Form Variable" and made that "KT_insert1" which is the name of the submit button of the insert form. But that doesn't work since the entry in table1 is never deleted. How do I identify the correct trigger? I want to press the button and between insert and redirect I would like to delete the entry from table1.

            Cheers Juergen
            • 3. Re: transfer single record from table1 to table2
              Günter Schenk Level 4
              Hi Jürgen,

              -------
              I want to press the button and between insert and redirect I would like to delete the entry from table1
              -------

              usually by defining a custom DELETE FROM... SQL statement in a Custom Trigger that´s of course tied to the existing Insert transaction, but set to AFTER in order to not be executed at the same time

              Cheers,
              Günter Schenk
              Adobe Community Expert, Dreamweaver
              • 4. Re: transfer single record from table1 to table2
                Level 1
                Hi Guenter,

                thanks for the hint with the Custom trigger. I actually had no idea how to use them so I googled for addt cutom trigger and it came up with one of your tutorials using cutom triggers on a login page.

                I stole some of the code, modified it and I now use this or the custom trigger started AFTER with priority 50:

                if ( isset($_GET['id']) && !empty($_GET['id']) ) {
                $delete_pending_record = "DELETE FROM pending WHERE id =".$_GET["id"];
                $delete_result = $tNG->connection->execute($delete_pending_record);
                if(!$delete_result) {
                $deleteError = new tNG_error("Error deleting pending record", array(), array());
                return $deleteError;
                } else {
                return NULL;
                }
                }

                Now my setup does what I want it.

                Thanks again
                Juergen
                • 5. Re: transfer single record from table1 to table2
                  Günter Schenk Level 4
                  Hi Jürgen,

                  -----
                  I stole some of the code
                  -----

                  what ? how dare you ? :-)

                  BTW, ADDT´s helpfile also provides a variety of Custom Trigger examples

                  Cheers,
                  Günter Schenk
                  Adobe Community Expert, Dreamweaver