Type: TransactionType

The type of transaction to be set or retrieved for the next transaction.

This function sets the transaction type for the next transaction that starts when you are using Microsoft SQL Server. The transaction type determines the extent of locking that is performed on data in SQL Server tables and indexes. It also determines whether modifications to data can occur within the transaction. The following basic transaction types are available:

• Browse
  
• Snapshot
  
• UpdateNoLocks
  
• Update
  
• Report
  

The Report transaction type maps to one of the other basic transaction types. For more information about the behavior of the transaction types, see TransactionType Property.

Using CURRENTTRANSACTIONTYPE to set a transaction type from within a currently active transaction does not affect the transaction type. You must set the transaction type before a transaction starts, which occurs at the first database call in a trigger or in a codeunit. If you set the current transaction type to a less isolated transaction behavior, for example, if you try to change an Update transaction to a Browse transaction, the function call is ignored. If you try to change the current transaction type to a more isolated transaction behavior, for example, from Browse to Update, you will receive an error message.

In Example 1, CURRENTTRANSACTIONTYPE is used to set the required behavior of the next transaction in the database.

	Copy Code
//Example 1 CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::UpdateNoLocks;

In Example 2, CURRENTTRANSACTIONTYPE is used to return the transaction type setting for the current transaction.

	Copy Code
//Example 2 IF CURRENTTRANSACTIONTYPE = TRANSACTIONTYPE::UpdateNoLocks THEN...

Examples 3 and 4 show how to use the CURRENTTRANSACTIONTYPE function to set the transaction type for two separate transactions.

When you set the transaction type as in Example 3, you will get an update (or write) transaction using SERIALIZABLE behavior, which means that modifications are allowed within this transaction. SQL Server will guarantee the serializability of the transaction by placing the appropriate locks when you read from the table in the database.

	Copy Code
//Example 3 CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Update;

Examples 3 and 4 show how to use the CURRENTTRANSACTIONTYPE function to set the transaction type for two separate transactions.

When you set the transaction type as in Example 4, you will get a read-only, non-locking transaction. This means that no modifications are allowed within this transaction and that SQL Server does not add any locks. It is also possible to read any uncommitted data.

	Copy Code
//Example 4 CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Browse;

In Example 5, the initial transaction type is UpdateNoLocks. This is usually the default transaction type for a trigger. When you try to change the transaction type from UpdateNoLocks to Browse, which is a less isolated transaction type, the function call is ignored. The current transaction type remains UpdateNoLocks. After the first Table.GET line, again, when you try to change the transaction type from UpdateNoLocks to Browse, the function call is ignored. The current transaction type remains UpdateNoLocks. Next, when you try to change the transaction type from UpdateNoLocks to Update, which is a more isolated transaction type, the function call causes an error message to be generated. The current transaction type remains UpdateNoLocks. After the COMMIT, you can set a new transaction type and change it again if it is required. After the second Table.GET line, the CURRENTTRANSACTIONTYPE call is ignored. The transaction type remains Update.

	Copy Code
//Example 5 OnInsert CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Browse; Table.GET; CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Browse; CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Update; COMMIT; CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Browse; CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Update; Table.GET; CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Browse; OnInsert.RETURN COMMIT;

In Example 6, the initial transaction type is UpdateNoLocks. The first two times that you try to change to a less isolated transaction type, the function call is ignored. Finally, when you try to change to a more isolated transaction type, the function call causes an error message to be generated.

	Copy Code
//Example 6 CODEUNIT CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Snapshot; Report 1 CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Browse; Report 2 CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Update;

Concepts