Using COMMIT Transactions

Using COMMIT Transactions

Leveraging SQLScript in Stored Procedures & User Defined Functions through the use of COMMIT

You will learn

In this exercise will show the impact of a runtime error on DML statements and how to prevent it using COMMIT.

Details

Please note - This tutorial is based on SPS11

Step 1: Edit previous procedure

Return to the SQL tab and change the filter value for the first input parameter. Here you are adding a filter on a column which we know does not exists in hopes of causing an error and transaction rollback.

As you can see not only was the new record inserted into the log.errors table, but also “Chuck Norris” found its way into our log.messages table. The complete transaction will be committed, meaning any modification happened in this transaction will be persisted. A better solution for this are the autonomous transaction.