• State Not Answered
  • Replies 6 replies
  • Subscribers 12 subscribers
  • Views 6172 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Data and Records

Data versioning - writing to two tables in a single transaction

harishn

Hello, 

Generally the question, is best way to do versioning with Appian on to DB? 

We are considering the following approach for implementation. Have a single table, which stores versions of data in the same table. Example, if row {id:'1',name:"test name", version:"1"} is updated then you have two rows one - version 1 and another version 2. How can I carry out both update and insert through a single transaction in Appian? This is the method in general we want to go ahead with. If i want to send both the CDT to write to data store, i will need to manipulate the cdts upfront, like get the old row id if present, increment version, then update and then insert.

Any other approaches please let us know.

Thank you,

Harish

  Discussion posts and replies are publicly visible

  • 0
    Certified Lead Developer
    Hi Harishn,
    I understand that you wanted to have the versioning on the database rows. If this is the requirement that you are trying to achieve you can do this by adding @Version annotation on the version field in the xsd. This will automatically save the version for each record . Please let me know if your requiremnet is something else so as to be able to provide you with better solution.
    Hope this helps!
  • 0

    Hi Harish,

    AFAIK , Maintain 2 tables one for

                 1)Transactional data

                 2)Audit history.

    For Insert write to Transnational and for update write to Audit(You can write insert & update request as well depending on the requirement), So that you can easily track it.

     

    If you want to track the Versioning of each field, you can simply add @Version annotation to the specific field in XSD.

     

    I hope this might help for your requirement.

     

    Thanks

    Siva Chimata

     

  • 0
    A Score Level 1
    Hi,
    There is a similar post which might help --> community.appian.com/.../48631

    Thanks.
  • 0
    A Score Level 1
    Hi Harish,

    I agree with @Shiva approach. Instead of creating this in the upfront, you can try use the trigger on the table. This could be very easy and can give faster results instead of writing same data in the two different tables (transaction, audit table). This way with one single update it will also insert the record in the audit table in the background.

    Hope this may help..
  • 0
    If you go ahead with triggers, try to keep them as simple as possible. If you google "mysql trigger best practices" you will see lots of articles cautioning against using them. Complicated triggers have two issues:

    1) They can hurt performance when writing to the table. If you plan to do many inserts to this table, the triggers will fire on each one.

    2) The logic is somewhat hidden from your front end developers who many not be aware the triggers exist or even know how to check for them.


    That said, IMO triggers can be a good tool for your use case. As obscure as a trigger can be, expecting the dev team to just remember to update the history table every time they update the main table is asking for trouble.
  • 0 in reply to harrisont606

     and @shanmukha Thanks for the reply, infact I wanted to go with triggers, but we can not use triggers in current implementation. 

     

     and  Thanks for the reply, I am not sure how I can achieve this with the @Version annotation. My understanding is that it only provides optimistic lock and do not save the old verison of the object within the table. I tried testing using a simple process. 

    As  you have mentioned, we may go in the approach of one single table that has both audit and transaction data, and update and insert rows on each update - update old existing row as old and insert new row. 

    My understanding is that it would be best to be done in one single transaction/ single step, now I have a sub process which has checks and replicates data for updates and insert. Doing this seems the way to go.

© 2024 Appian. All rights reserved.