Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Question: Tracking row level changes (not using audit trail logging)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Question: Tracking row level changes (not using audit trail logging)

    Hi everyone - silly question I think....

    I thought AA had built-in functionality that you can add 4 fields to a table to track: who created and updated the row, and date time it was created and updated and it would automatically update the fields with operations. I know the date/time fields can use current_timestamp as default, but not sure about passing the who "who" created/updated last the row.

    I may be confused and just need to add the info manually but I couldn't find a reference anywhere in the forum or documentation.

    Thanks

  • #2
    We did something like that in a project years ago. If you are using the UX, you can write the data back to the database using the after dialog validate server side event.
    You can also run an Ajax callback to do the same thing.
    Hope that helps.
    Jay
    Jay Talbott
    Lexington, KY

    Comment


    • #3
      Are you using a SQL backend?
      Mike Reed
      Phoenix, AZ

      Comment


      • #4
        Mike asked, but with a SQL backend you can duplicate your primary table, add a couple of columns for date and update type, and then add after insert, after update, and after delete triggers to the table with something like this:

        Code:
        DECLARE @origProcId INT;
        SET @origProcId = (SELECT ID FROM Inserted)
        
        INSERT INTO hist_origProcess
        SELECT 'Update', GETDATE(), o.*
        FROM origProcess o
        WHERE o.id = @origProcId
        This may be overkill for your needs though. The biggest thing to remember is that anytime you change the primary table with a new column, etc, you have to change the history table as well.

        Comment


        • #5
          All of my tables have these fields.
          Added timedate
          Addby varchar40
          Addip varchar40
          Modified time date
          Modby varchar40
          Modip varchar40
          Deleted tinyint 1, default set to zero

          When a record is added to the table, the Added and Modified fields are filled in using the now() function. Using session variables such as session.userid and session.userip, the other fields are populated.

          Whenever an update is made, only the modified, modby and modip fields are changed.
          If the record is being deleted, then before the delete process is run, a callback is run to update the modified, modby, modip fields and sets the deleted field to 1, then you know the details on who and when the record was deleted.

          The second part is to have a second table, which is an exact duplicate of the primary table and use the sql trigger commands to add a record to the duplicate table whenever an insert, update or delete is done to the primary table.
          For example, I have a table named member with a corresponding table named member_log. There are 3 triggers set for the member table that adds the record to the member_log table every time an insert, update or delete occurs.

          By doing that you have a complete log of everything that went on with every record.

          Two caveats though. If you change or update or add any fields in the primary table, you must also make the same exact change to the 'log' table. They need to be exact duplicates, structure wise.
          Second is that the 'log' table cannot not have a primary key nor should any fields have any rules or defaults set for it.

          If you need to find out what the history was for a given record, then go to the 'log' file and do a query based on the primary id. You will have a complete record of everything that was done, including a record that was deleted.
          Mike Reed
          Phoenix, AZ

          Comment


          • #6
            What database are you using?

            Comment


            • #7
              Originally posted by pettechservices View Post
              Hi everyone - silly question I think....

              I thought AA had built-in functionality that you can add 4 fields to a table to track: who created and updated the row, and date time it was created and updated and it would automatically update the fields with operations. I know the date/time fields can use current_timestamp as default, but not sure about passing the who "who" created/updated last the row.

              I may be confused and just need to add the info manually but I couldn't find a reference anywhere in the forum or documentation.

              Thanks
              AA has the capability through the project settings to create and use an audit log table which stores details of all data changes including loggedin user, datestamp, tablename, primarykey etc. The changes are stored in a json string and include new/old values. This works seemlessly if you are using standard ux components to complete CRUD operations (believe it also works on grids), If you are using xbasic for operations anywhere there are also build in helper functions that you can "wrap" around the code to tap into this feature. I have it working on all applications and it just works!
              Glen Schild



              My Blog

              Comment


              • #8
                Hi Glen

                I use it too - and it works great EXCEPT I have been unable to determine why it works on some databases but not others. On servers where there is one database it works fine. On those where there are more than one database nothing gets written to the audit table. Let me explain: the named connection string I specified in project properties is conn1. On 2 servers there is just one database, and the connection to them is conn1. But on 2 other servers there are 2 databases each: conn1, and conn2 are the connection strings. The databases that use conn1 all work; the databases that use conn2 do not work. I suspect that this due to my specifying conn1 in project properties. I am not sure how to resolve this. Do you have any ideas?
                Last edited by drgarytraub; 12-21-2020, 02:55 PM.
                Gary S. Traub, Ph.D.

                Comment


                • #9
                  Originally posted by drgarytraub View Post
                  Hi Glen

                  I use it too - and it works great EXCEPT I have been unable to determine why it works on some databases but not others. On servers where there is one database it works fine. On those where there are more than one database nothing gets written to the audit table. Let me explain: the named connection string I specified in project properties is conn1. On 2 servers there is just one database, and the connection to them is conn1. But on 2 other servers there are 2 databases each: conn1, and conn2 are the connection strings. The databases that use conn1 all work; the databases that use conn2 do not work. I suspect that this due to my specifying conn1 in project properties. I am not sure how to resolve this. Do you have any ideas?
                  Oooh that's a good one, let me have a play
                  Glen Schild



                  My Blog

                  Comment


                  • #10
                    Hi Glen,

                    Any thoughts on this?
                    Gary S. Traub, Ph.D.

                    Comment


                    • #11
                      thanks everyone.. just getting back into this part after getting diverted.. I am using MySQL currently..

                      Comment


                      • #12
                        using mySQL makes keeping track of activity a lot easier thanks to the ease of triggers.
                        You have to decide what info you need, and how much follow-up work you want to do.
                        I have one project where I keep track of who, when field, old value, and new value.
                        Others I simply copy the old record into a log table, and add values for who did it and when.
                        Gregg
                        https://paiza.io is a great site to test and share sql code

                        Comment


                        • #13
                          Ok, thanks. do you have a sample trigger that shows how you process the old values and new ones and put them into a log table? Thanks!

                          Comment


                          • #14
                            There's a lot here because of everything involved and I don't really have time to shorten things.
                            I'm more than glad to answer questions.
                            Sadly, this system eliminates all formatting.

                            Code:
                            DROP TRIGGER `working`.`wo_logorderchanges`;
                            CREATE TRIGGER `working`.`wo_logorderchanges` AFTER UPDATE
                            ON working.workingorders FOR EACH ROW
                            
                            BEGIN
                            DECLARE logtime datetime;
                            DECLARE actiondate date;
                            DECLARE deldriver int;
                            
                            SET @loggedtime = (SELECT max(lstupdated) FROM workingorders);
                            
                            -- if old.date_statement is not null and new.date_statement is null then
                            -- set new.date_statement = old.date_statement;
                            -- end if;
                            -- if new.date_statement <> old.date_statement or old.date_statement is null then
                            IF new.date_statement IS NOT NULL
                            AND ( new.date_statement <> old.date_statement
                            OR old.date_statement IS NULL)
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (gl.mydtformat(now()),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'date_statement',
                            old.date_statement,
                            new.date_statement,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            
                            -- if old.driver_pay_date is not null and new.driver_pay_date is null then
                            -- set new.driver_pay_date = old.driver_pay_date;
                            -- end if;
                            -- if new.driver_pay_date <> old.driver_pay_date or old.driver_pay_date is null then
                            IF new.driver_pay_date IS NOT NULL
                            AND ( new.driver_pay_date <> old.driver_pay_date
                            OR old.driver_pay_date IS NULL)
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (gl.mydtformat(now()),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'driver_pay_date',
                            old.driver_pay_date,
                            new.driver_pay_date,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            
                            IF new.pickup_date <> old.pickup_date
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'PICKUP_DATE',
                            old.pickup_date,
                            new.pickup_date,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.status <> old.status
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'status',
                            old.status,
                            new.status,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.status IN ('d', 'n', 'r') AND old.status IN ('d', 'n', 'r')
                            THEN
                            IF new.ID_VERIFIER <> old.ID_VERIFIER
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'ID_VERIFIER',
                            old.ID_VERIFIER,
                            new.ID_VERIFIER,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.BILL_OF_LADING <> old.BILL_OF_LADING
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'BILL_OF_LADING',
                            old.BILL_OF_LADING,
                            new.BILL_OF_LADING,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.CALLER <> old.CALLER
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'CALLER',
                            old.CALLER,
                            new.CALLER,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.CALLER_PHONE <> old.CALLER_PHONE
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'CALLER_PHONE',
                            old.CALLER_PHONE,
                            new.CALLER_PHONE,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.amt_received <> old.amt_received
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (gl.mydtformat(now()),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'AMT_RECEIVED',
                            old.amt_received,
                            new.amt_received,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.amt_credit <> old.amt_credit
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (gl.mydtformat(now()),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'AMT_CREDIT',
                            old.amt_credit,
                            new.amt_credit,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.CREDIT_DATE <> old.CREDIT_DATE
                            OR (new.credit_date IS NOT NULL AND old.credit_date IS NULL)
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (gl.mydtformat(now()),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'CREDIT_DATE',
                            old.CREDIT_DATE,
                            new.CREDIT_DATE,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.amt_debit <> old.amt_debit
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (gl.mydtformat(now()),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'amt_debit',
                            old.amt_debit,
                            new.amt_debit,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            
                            -- ETB23
                            
                            
                            
                            IF new.TIME_READY <> old.TIME_READY
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'TIME_READY',
                            old.TIME_READY,
                            new.TIME_READY,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.TIME_CLOSE <> old.TIME_CLOSE
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'TIME_CLOSE',
                            old.TIME_CLOSE,
                            new.TIME_CLOSE,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.BILLTO_REC_NUMBER <> old.BILLTO_REC_NUMBER
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'BILLTO_REC_NUMBER',
                            old.BILLTO_REC_NUMBER,
                            new.BILLTO_REC_NUMBER,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.BILLTO_NAME <> old.BILLTO_NAME
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'BILLTO_NAME',
                            old.BILLTO_NAME,
                            new.BILLTO_NAME,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.AUTHORIZATION <> old.AUTHORIZATION
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'AUTHORIZATION',
                            old.AUTHORIZATION,
                            new.AUTHORIZATION,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.ORIGIN_NAME <> old.ORIGIN_NAME
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'ORIGIN_NAME',
                            old.ORIGIN_NAME,
                            new.ORIGIN_NAME,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.ORIGIN_STREET <> old.ORIGIN_STREET
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'ORIGIN_STREET',
                            old.ORIGIN_STREET,
                            new.ORIGIN_STREET,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.ORIGIN_CITY <> old.ORIGIN_CITY
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'ORIGIN_CITY',
                            old.ORIGIN_CITY,
                            new.ORIGIN_CITY,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.ORIGIN_STATE <> old.ORIGIN_STATE
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'ORIGIN_STATE',
                            old.ORIGIN_STATE,
                            new.ORIGIN_STATE,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.ORIGIN_ZIP <> old.ORIGIN_ZIP
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'ORIGIN_ZIP',
                            old.ORIGIN_ZIP,
                            new.ORIGIN_ZIP,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.ORIGIN_PHONE <> old.ORIGIN_PHONE
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'ORIGIN_PHONE',
                            old.ORIGIN_PHONE,
                            new.ORIGIN_PHONE,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.DEST_NAME <> old.DEST_NAME
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DEST_NAME',
                            old.DEST_NAME,
                            new.DEST_NAME,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.DEST_STREET <> old.DEST_STREET
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DEST_STREET',
                            old.DEST_STREET,
                            new.DEST_STREET,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.DEST_CITY <> old.DEST_CITY
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DEST_CITY',
                            old.DEST_CITY,
                            new.DEST_CITY,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.DEST_STATE <> old.DEST_STATE
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DEST_STATE',
                            old.DEST_STATE,
                            new.DEST_STATE,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.DEST_ZIP <> old.DEST_ZIP
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DEST_ZIP',
                            old.DEST_ZIP,
                            new.DEST_ZIP,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.DEST_PHONE <> old.DEST_PHONE
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DEST_PHONE',
                            old.DEST_PHONE,
                            new.DEST_PHONE,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            
                            IF new.DISPATCH_TIME <> old.DISPATCH_TIME
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DISPATCH_TIME',
                            old.DISPATCH_TIME,
                            new.DISPATCH_TIME,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.TIME_PICKUP <> old.TIME_PICKUP
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'TIME_PICKUP',
                            old.TIME_PICKUP,
                            new.TIME_PICKUP,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.TIME_DELIVER <> old.TIME_DELIVER
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'TIME_DELIVER',
                            old.TIME_DELIVER,
                            new.TIME_DELIVER,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.CLEAR_TIME <> old.CLEAR_TIME
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'CLEAR_TIME',
                            old.CLEAR_TIME,
                            new.CLEAR_TIME,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.SIGNATURE <> old.SIGNATURE
                            OR (new.signature IS NOT NULL AND old.signature IS NULL)
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'SIGNATURE',
                            old.SIGNATURE,
                            new.SIGNATURE,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            
                            IF new.PICKUP_DRIVER <> old.PICKUP_DRIVER
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'PICKUP_DRIVER',
                            old.PICKUP_DRIVER,
                            new.PICKUP_DRIVER,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.PICKUP_COMM_PCT <> old.PICKUP_COMM_PCT
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'PICKUP_COMM_PCT',
                            old.PICKUP_COMM_PCT,
                            new.PICKUP_COMM_PCT,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.PICKUP_COMMISSION <> old.PICKUP_COMMISSION
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'PICKUP_COMMISSION',
                            old.PICKUP_COMMISSION,
                            new.PICKUP_COMMISSION,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.DELIVERY_DRIVER <> old.DELIVERY_DRIVER
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DELIVERY_DRIVER',
                            old.DELIVERY_DRIVER,
                            new.DELIVERY_DRIVER,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.DELIVERY_COMM_PCT <> old.DELIVERY_COMM_PCT
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DELIVERY_COMM_PCT',
                            old.DELIVERY_COMM_PCT,
                            new.DELIVERY_COMM_PCT,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.DELIVERY_COMMISSION <> old.DELIVERY_COMMISSION
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DELIVERY_COMMISSION',
                            old.DELIVERY_COMMISSION,
                            new.DELIVERY_COMMISSION,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.SPLIT <> old.SPLIT
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'SPLIT',
                            old.SPLIT,
                            new.SPLIT,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.TIME1 <> old.TIME1
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'TIME1',
                            old.TIME1,
                            new.TIME1,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.TIME2 <> old.TIME2
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'TIME2',
                            old.TIME2,
                            new.TIME2,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.TOS <> old.TOS
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'TOS',
                            old.TOS,
                            new.TOS,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.SERVICE <> old.SERVICE
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'SERVICE',
                            old.SERVICE,
                            new.SERVICE,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.RATE_TABLE <> old.RATE_TABLE
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'RATE_TABLE',
                            old.RATE_TABLE,
                            new.RATE_TABLE,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.PIECES <> old.PIECES
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'PIECES',
                            old.PIECES,
                            new.PIECES,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.WEIGHT <> old.WEIGHT
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'WEIGHT',
                            old.WEIGHT,
                            new.WEIGHT,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.NOTE <> old.NOTE
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'NOTE',
                            old.note,
                            new.note,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.DESCRIPTION <> old.DESCRIPTION
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'DESCRIPTION',
                            old.DESCRIPTION,
                            new.DESCRIPTION,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            
                            IF new.FEE1 <> old.FEE1
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE1',
                            old.FEE1,
                            new.FEE1,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE2 <> old.FEE2
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE2',
                            old.FEE2,
                            new.FEE2,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE3 <> old.FEE3
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE3',
                            old.FEE3,
                            new.FEE3,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE4 <> old.FEE4
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE4',
                            old.FEE4,
                            new.FEE4,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE5 <> old.FEE5
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE5',
                            old.FEE5,
                            new.FEE5,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE6 <> old.FEE6
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE6',
                            old.FEE6,
                            new.FEE6,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE7 <> old.FEE7
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE7',
                            old.FEE7,
                            new.FEE7,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE8 <> old.FEE8
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE8',
                            old.FEE8,
                            new.FEE8,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE9 <> old.FEE9
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE9',
                            old.FEE9,
                            new.FEE9,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE10 <> old.FEE10
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE10',
                            old.FEE10,
                            new.FEE10,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE11 <> old.FEE11
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE11',
                            old.FEE11,
                            new.FEE11,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE12 <> old.FEE12
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE12',
                            old.FEE12,
                            new.FEE12,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE_DESC1 <> old.FEE_DESC1
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE_DESC1',
                            old.FEE_DESC1,
                            new.FEE_DESC1,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE_DESC2 <> old.FEE_DESC2
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE_DESC2',
                            old.FEE_DESC2,
                            new.FEE_DESC2,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE_DESC3 <> old.FEE_DESC3
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE_DESC3',
                            old.FEE_DESC3,
                            new.FEE_DESC3,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE_PCT1 <> old.FEE_PCT1
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE_PCT1',
                            old.FEE_PCT1,
                            new.FEE_PCT1,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.FEE_PCT2 <> old.FEE_PCT2
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'FEE_PCT2',
                            old.FEE_PCT2,
                            new.FEE_PCT2,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.AMT_CHARGED <> old.AMT_CHARGED
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'AMT_CHARGED',
                            old.AMT_CHARGED,
                            new.AMT_CHARGED,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.MISC_CHARGE1 <> old.MISC_CHARGE1
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MISC_CHARGE1',
                            old.MISC_CHARGE1,
                            new.MISC_CHARGE1,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.MISC_CHARGE2 <> old.MISC_CHARGE2
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MISC_CHARGE2',
                            old.MISC_CHARGE2,
                            new.MISC_CHARGE2,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.MISC_CHARGE3 <> old.MISC_CHARGE3
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MISC_CHARGE3',
                            old.MISC_CHARGE3,
                            new.MISC_CHARGE3,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            
                            IF new.COMMENT1 <> old.COMMENT1
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'COMMENT1',
                            old.COMMENT1,
                            new.COMMENT1,
                            coalesce(new.billto_rec_number, new.btchar));
                            
                            IF new.MEMO_DATE1 <> old.MEMO_DATE1
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MEMO_DATE1',
                            old.MEMO_DATE1,
                            new.MEMO_DATE1,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            END IF;
                            
                            IF new.COMMENT2 <> old.COMMENT2
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'COMMENT2',
                            old.COMMENT2,
                            new.COMMENT2,
                            coalesce(new.billto_rec_number, new.btchar));
                            
                            IF new.MEMO_DATE2 <> old.MEMO_DATE2
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MEMO_DATE2',
                            old.MEMO_DATE2,
                            new.MEMO_DATE2,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            END IF;
                            
                            IF new.COMMENT3 <> old.COMMENT3
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'COMMENT3',
                            old.COMMENT3,
                            new.COMMENT3,
                            coalesce(new.billto_rec_number, new.btchar));
                            
                            IF new.MEMO_DATE3 <> old.MEMO_DATE3
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MEMO_DATE3',
                            old.MEMO_DATE3,
                            new.MEMO_DATE3,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            END IF;
                            
                            IF new.COMMENT4 <> old.COMMENT4
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'COMMENT4',
                            old.COMMENT4,
                            new.COMMENT4,
                            coalesce(new.billto_rec_number, new.btchar));
                            
                            IF new.MEMO_DATE4 <> old.MEMO_DATE4
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MEMO_DATE4',
                            old.MEMO_DATE4,
                            new.MEMO_DATE4,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            END IF;
                            
                            IF new.COMMENT5 <> old.COMMENT5
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'COMMENT5',
                            old.COMMENT5,
                            new.COMMENT5,
                            coalesce(new.billto_rec_number, new.btchar));
                            
                            IF new.MEMO_DATE5 <> old.MEMO_DATE5
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MEMO_DATE5',
                            old.MEMO_DATE5,
                            new.MEMO_DATE5,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            END IF;
                            
                            IF new.COMMENT6 <> old.COMMENT6
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'COMMENT6',
                            old.COMMENT6,
                            new.COMMENT6,
                            coalesce(new.billto_rec_number, new.btchar));
                            
                            IF new.MEMO_DATE6 <> old.MEMO_DATE6
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MEMO_DATE6',
                            old.MEMO_DATE6,
                            new.MEMO_DATE6,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            END IF;
                            
                            IF new.COMMENT7 <> old.COMMENT7
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'COMMENT7',
                            old.COMMENT7,
                            new.COMMENT7,
                            coalesce(new.billto_rec_number, new.btchar));
                            
                            IF new.MEMO_DATE7 <> old.MEMO_DATE7
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MEMO_DATE7',
                            old.MEMO_DATE7,
                            new.MEMO_DATE7,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            END IF;
                            
                            IF new.COMMENT8 <> old.COMMENT8
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'COMMENT8',
                            old.COMMENT8,
                            new.COMMENT8,
                            coalesce(new.billto_rec_number, new.btchar));
                            
                            IF new.MEMO_DATE8 <> old.MEMO_DATE8
                            THEN
                            INSERT INTO wo_orderchangelog(noted,
                            logtime,
                            changeauthor,
                            sponsor,
                            tag,
                            pu_date,
                            fieldname,
                            oldvalue,
                            newvalue,
                            acct)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            @loggedtime,
                            new.id_verifier,
                            new.sponsor,
                            new.tag,
                            new.pickup_date,
                            'MEMO_DATE8',
                            old.MEMO_DATE8,
                            new.MEMO_DATE8,
                            coalesce(new.billto_rec_number, new.btchar));
                            END IF;
                            END IF;
                            END IF;
                            
                            IF concat(gl.today(), ' ', new.time_pickup) <= now()
                            THEN
                            SET actiondate = gl.today();
                            ELSE
                            SET actiondate = gl.yesterday();
                            END IF;
                            
                            IF new.time_pickup <> '00:00' AND new.time_pickup <> old.time_pickup
                            THEN
                            INSERT INTO automanifest(noted,
                            tag,
                            sponsor,
                            changeauthor,
                            idnum,
                            stdate,
                            sttime,
                            actiontype,
                            stname,
                            staddress)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            new.tag,
                            new.sponsor,
                            new.id_verifier,
                            new.pickup_driver,
                            actiondate,
                            new.time_pickup,
                            'P',
                            new.origin_name,
                            new.origin_street);
                            END IF;
                            
                            IF concat(gl.today(), ' ', new.time_deliver) <= now()
                            THEN
                            SET actiondate = gl.today();
                            ELSE
                            SET actiondate = gl.yesterday();
                            END IF;
                            
                            IF new.delivery_driver > 0
                            THEN
                            SET deldriver = new.delivery_driver;
                            ELSE
                            SET deldriver = new.pickup_driver;
                            END IF;
                            
                            IF new.time_deliver <> '00:00' AND new.time_deliver <> old.time_deliver
                            THEN
                            INSERT INTO automanifest(noted,
                            tag,
                            sponsor,
                            changeauthor,
                            idnum,
                            stdate,
                            sttime,
                            actiontype,
                            stname,
                            staddress,
                            pod)
                            VALUES (date_format(now(), '%Y-%m-%d %H:%i:%s'),
                            new.tag,
                            new.sponsor,
                            new.id_verifier,
                            deldriver,
                            actiondate,
                            new.time_deliver,
                            'D',
                            new.dest_name,
                            new.dest_street,
                            new.signature);
                            END IF;
                            END;
                            Gregg
                            https://paiza.io is a great site to test and share sql code

                            Comment


                            • #15
                              Just in case my original response is in limbo, here is a sample update script in a text file.
                              It is tedious to setup, but once working it makes things so much easier.
                              Attached Files
                              Gregg
                              https://paiza.io is a great site to test and share sql code

                              Comment

                              Working...
                              X