Database Transactions

Idea created by restored18 on Apr 21, 2016
    Active
    Score21

    All modern SQL databases allow you to run queries in a in a transaction state. Under this state, queries are sent to the server, but not executed until a commit command is run. This allows a developer to create/edit multiple records across several tables but roll back the queries if an error is encountered. The transaction routine ensures that all the records are created/edited or none at all.

     

    The above example is a simplification, but it illustrates the need. In the FileMaker community, we have created multiple ways of doing transactions, either through quirky relationships or portal-laden layouts. My idea is to create a native way to implement transactions using script steps. The steps would be as follows:

    • Begin Transaction
    • Revert Transaction
    • Commit Transaction

    After running the Begin Transaction step, you could run Set Fields, New Record, Delete Record etc and if an error is encountered in the process, you could run Revert Transaction to revert all edited, created or deleted records to their state before the transaction began. If no error occurs, Commit Transaction could be run which would save all the changes to the records.

     

    Implementing a transaction method along these lines would make it much easier to maintain database integrity and create a better experience for the user.