• State Not Answered
  • Replies 6 replies
  • Subscribers 12 subscribers
  • Views 6171 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

Parents
  • 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.

Reply
  • 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.

Children
No Data

© 2024 Appian. All rights reserved.