PRAGMA AUTONOMOUS_TRANSACTION - Oracle to SQL Server Migration

In Oracle, an autonomous transaction is an independent transaction started by another transaction (the main transaction).

When the autonomous transaction performs a commit or roll back, its committed changes are visible to other transactions immediately, but it does not affect the main transaction that is still in progress. At the same time, the autonomous transaction does not see any uncommitted changes made by the main transaction.

Why to Use Autonomous Transactions

Autonomous transactions are usually used for logging and auditing in the database.

For example, you may want to log all activities performed by a user and save the log to a database table. But when a user performs a roll back, all your log records will be also rolled back. An autonomous transaction can help you overcome this.

Autonomous Transactions in Oracle

Let's record all attempts to insert a row to the cities table:

Oracle:

  -- Define a table
  CREATE TABLE cities
  ( 
     name VARCHAR2(90),
     state CHAR(2)
  );
 
  -- Define log
  CREATE TABLE log
  (
     created DATE DEFAULT SYSDATE,
     item VARCHAR2(1000)
  );
 
  -- Define a trigger to log insert operations
  CREATE OR REPLACE TRIGGER tr_cities
    AFTER INSERT ON cities FOR EACH ROW
  DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO log (item) VALUES ('Attempt to insert cities: ' || :new.name);
    COMMIT;
  END;
  /

Now let's insert a few rows:

Oracle: (Session 1):

  INSERT INTO cities VALUES ('San Diego', 'CA');
  INSERT INTO cities VALUES ('Boston', 'MA');

This transaction is not committed yet, but if you start another session you can already see log records, but not data:

Oracle (Session 2):

  SELECT * FROM cities;
  -- no rows selected
 
  SELECT * FROM log;
  -- 15-JUN-12 Attempt to insert cities: San Diego
  -- 15-JUN-12 Attempt to insert cities: Boston

Now if you commit inserts in Session 1, insert a new row and then roll it back, you can see the following content in cities and log tables:

Oracle: (Session 1):

  -- Commit first 2 inserted rows
  COMMIT;
 
  INSERT INTO cities VALUES ('San Jose', 'CA');
  ROLLBACK;
 
  SELECT * FROM cities;
  -- San Diego  CA
  -- Boston       MA
 
  SELECT * FROM log;
  -- 15-JUN-12 Attempt to insert cities: San Diego
  -- 15-JUN-12 Attempt to insert cities: Boston
  -- 15-JUN-12 Attempt to insert cities: San Jose

You can see that the log table contains information on all actions.

Implementing Autonomous Transactions in SQL Server

SQL Server does not directly support autonomous transactions, but you can implement them using a linked server.

A linked server in SQL Server allows you to start an independent transaction, and you can create a loopback linked server, a server that points to itself.

Firstly let's create a loopback linked server:

SQL Server:

   -- Add a loopback linked server
   EXEC sp_addlinkedserver @server = N'loopback', @srvproduct = N' ', @provider = N'SQLNCLI', 
                   @datasrc = @@SERVERNAME;
   GO

By default, a linked server participates in a distributed transaction, but you need independent transactions, so you have to set remote proc transaction promotion to false:

SQL Server:

   -- Set not to start a distributed transaction
   EXEC sp_serveroption loopback, N'remote proc transaction promotion', 'false' 
   GO
 
   -- Also enable RPC to call stored procedures
   EXEC sp_serveroption loopback, N'rpc out', 'true'
   GO

Let's create tables in SQL Server:

SQL Server:

  -- Define a table
  CREATE TABLE cities
  ( 
     name VARCHAR(90),
     state CHAR(2)
  );
 
  -- Define log
  CREATE TABLE log
  (
     created DATETIME DEFAULT GETDATE(),
     item VARCHAR(1000)
  );

Now you can create a procedure to log any activity and call it from a trigger through the loopback linked server:

SQL Server:

  -- Procedure to insert a log record
  CREATE PROCEDURE insert_log (@name VARCHAR(1000))
  AS
  BEGIN
    INSERT INTO log (item) VALUES ('Attempt to insert cities: ' + ISNULL(@name, ''));
  END
  GO  
 
  -- Define a trigger to log insert operations
  CREATE TRIGGER tr_cities ON cities
    AFTER INSERT
  AS
  BEGIN
    DECLARE @new_name VARCHAR(1000);
    DECLARE c_inserted CURSOR FOR SELECT name FROM inserted;
 
    OPEN c_inserted;
    FETCH c_inserted INTO @new_name;
 
    WHILE @@FETCH_STATUS = 0 BEGIN
        -- Log each record using loop back linked serve call
	EXECUTE loopback.test.dbo.insert_log @new_name;
 
	FETCH c_inserted INTO @new_name;
    END
 
    CLOSE c_inserted;
    DEALLOCATE c_inserted;
  END
  GO

Now let's start transaction in SQL Server, insert and commit a few rows, then insert another one and roll it back:

SQL Server:

  BEGIN TRANSACTION;
 
  INSERT INTO cities VALUES ('San Diego', 'CA');
  INSERT INTO cities VALUES ('Boston', 'MA');
  COMMIT;
 
  BEGIN TRANSACTION;
 
  INSERT INTO cities VALUES ('San Jose', 'CA');
  ROLLBACK;

Now let's query cities and log tables:

SQL Server:

  SELECT * FROM cities;
  -- San Diego  CA
  -- Boston       MA
 
  SELECT * FROM log;
  -- 2012-06-15 17:54:52.350   Attempt to insert cities: San Diego
  -- 2012-06-15 17:54:52.350   Attempt to insert cities: Boston
  -- 2012-06-15 17:55:02.313   Attempt to insert cities: San Jose

You can see that the log table in SQL Server contains information on all actions like an autonomous transaction in Oracle.

Transaction in Loopback Linked Server

You started the main transaction to insert records. Each insert statement fires a trigger that invokes a stored procedure through a loopback linked server call.

The procedure code:

SQL Server:

  -- Procedure to insert a log record
  CREATE PROCEDURE insert_log (@name VARCHAR(1000))
  AS
  BEGIN
    INSERT INTO log (item) VALUES ('Attempt to insert cities: ' + ISNULL(@name, ''));
  END
  GO

Since you specified not to promote distributed transaction, the linked server starts its own transaction that is in autocommit mode by default. So each insert to the log table is committed, and this does not affect the main transaction.

Last Revision: Oracle 11g and Microsoft SQL Server 2012