10 Replies Latest reply on Aug 8, 2009 11:17 PM by ikoniqoz

    Guidence needed for Custom Trigger

    ikoniqoz Level 1

      I have an ADDT Dynamic List and Form. (I love these things!) When I add/edit/delete a record using the form, I would like to update another (unrelated) database table and would like to know if that is possible using a Custom Trigger. I have read Ch. 16 of the ADDT Help manual and am a bit lost with the jargon  as I am not a power-php person.

       

      Here is what I need to do: Select from a different table a record by a value in a field and update another field in it. Then return to the Dynamic List page.

      The other solution which is an easy cop-out is once the transactions have been completed on the Form page, go to an intermediate PHP script that carries out the Select and Update process then return to the Dynamic List page.

       

      Any suggestions/comments welcome. Thanks.

        • 1. Re: Guidence needed for Custom Trigger
          Albert S. Level 3

          Hi,

           

          Try SB (Server Behaivors) -> Forms -> Advanced -> Link Transaction

           

          If you need more help, I will be happy to help. Just let me know.

           

           

          • 3. Re: Guidence needed for Custom Trigger
            ikoniqoz Level 1

            Hi Albert. Thanks for pointing out yet another hidden gem in ADDT.

             

            The help for this SB is a little vague.

             

            As a result I have no idea on how this will help.

             

            Here is the situation in a bit more detail.

             

            Lets say the Dynamic Form is used to enter News items. These are kept in a table for News Items. These Items are presented on a News page in the site. This page gets its content from the Pages table as well as the News table.

             

            There is no relationship between the two tables. However, the Pages table includes a field called updated which contains the date the "page" was last updated. Each News item also has an Updated field, but when the news item is updated I also want to update the updated field for the News Page.

             

            My first thought was to add a custom trigger to the News Items Form page that could grab the News Page record from the Pages table and update its updated field.

             

            So the Pages Table looks like this:

             

            Table name:pages_pag

            Columns:

            id_pag  - the primary key

            idpag_pag - the self foreign key

            menulabel_pag - the menu label

            template_pag - the template file used to display the page.

            ....

            ....

            ....

            pageheading_pag - the page heading

            content_pag - the page content

            updated_pag - the date created/updated

            ...

            etc etc......

             

            The News Table looks like this:

             

            Table name: news_nws

            Columns:

            id_nws

            heading_nws

            summary_nws

            details_nws

            updated_nws

            ...

            ...

            etc...

             

            Hope this explains what I am trying to achieve.

            • 4. Re: Guidence needed for Custom Trigger
              ikoniqoz Level 1

              Ha !

               

              Looks like our last posts crossed in the mail.

               

              Thanks for the pointer. I read this post a few days ago and it now makes more sense. I will give it a go over the weekend.

               

              Cheers.

              • 5. Re: Guidence needed for Custom Trigger
                Albert S. Level 3

                Hi,

                 

                Add a custom trigger to your news form.

                 

                Add a trigger similar to this.

                 

                $primary_key = ; //Here you will need to add the primarykey of the page entry you want to update.

                $date_value = $tNG->getColumnValue('updated_nws');

                $query="UPDATE pages_pag SET updated_pag='".$date_value."' WHERE id_pag = '".$primary_key."';
                $tNG->connection->execute($query);

                 

                 

                Hope that helps.

                • 6. Re: Guidence needed for Custom Trigger
                  ikoniqoz Level 1

                  Many thanks for your great help.

                   

                  So I create a new recordset on the form page to get the primary key I need and then in the custom trigger I can refer to the primary key obtained in the record set. Thats beautiful and simple. Pity ADDT is being phased out. Its such a great help to non-hard-core-coders like me.

                  • 7. Re: Guidence needed for Custom Trigger
                    ikoniqoz Level 1

                    Well, here is what happened

                     

                    I created a new recordset on the Form page and moved the RS from the place DW positioned it to be above the custom trigger which is as follows:

                     

                    //start Trigger_Custom trigger
                    function Trigger_Custom(&$tNG) {
                    // get the ID of the News Page
                    $primary_key = $row_rsNewsPage['id_pag'];
                    //get the value of the date from the transaction
                    $date_value = date("Y-m-d");
                    //Update the News Page updated date
                    $query="UPDATE pages_pag SET updated_pag='".$date_value."' WHERE id_pag = '".$primary_key."'";
                    $tNG->connection->execute($query);
                    }
                    //end Trigger_Custom trigger

                    I set the trigger to END 50 for insert, update & delete.

                     

                    Note the $date_value = date("Y-m-d"); line which I put there because the $date_value was not being changed using the tNG->getColumnValue method. And it still remains unchanged after the edit.

                     

                    HOWEVER - if I make the value of $primary_key equal to the actual value of the id_pag, in this case it is 8, then the UPDATE works and the News Page record is updated. PS: I also changed the UPDATE to be: $query="UPDATE pages_pag SET updated_pag = '$date_value' WHERE id_pag = $primary_key";  Not sure why you have wrapped the $date_value and $primary_key with extra quotes and periods.

                     

                    Sorry to be a pain with this. Have I screwed up something ?  I dont get any errors reported. It looks like the Custom Trigger is not getting the value of the id_pag from the rsNewsPage.

                    • 8. Re: Guidence needed for Custom Trigger
                      ikoniqoz Level 1

                      Correction to the 3rd last para which reads:

                       

                      Note the $date_value = date("Y-m-d"); line which I put there because the $date_value was not being changed using the tNG->getColumnValue method. And it still remains unchanged after the edit.

                       

                      This should be

                       

                      Note the $date_value = date("Y-m-d"); line which I put there because the updated_pag field was not being changed using the tNG->getColumnValue method. And it still remains unchanged after the edit.

                      • 9. Re: Guidence needed for Custom Trigger
                        Günter Schenk Level 4

                        Hi Wollombi,

                         

                        please consider that a Custom Trigger is an ordinary PHP function after all, and that any variable which has been declared outside a PHP function needs to be imported using the GLOBAL keyword, example:

                         

                        function whatever () {

                        global $row_rsNewsPage['id_pag'];

                         

                        other code here...

                         

                        }

                         

                        If this extra step gets omitted, trying to use a variable won´t trigger an error, but its value will be empty.

                         

                        Cheers,

                        Günter

                        • 10. Re: Guidence needed for Custom Trigger
                          ikoniqoz Level 1

                          Gunter

                          Many thanks for pointing that out. Thanks also to Albert for putting me on the right track.

                          Regards from the Wollombi Wombat.