Using AUTONOMOUS transactions

Let's understand the concept and the usage part of the AUTONOMOUS transaction with an example.

In an organization, the HR director wants to make sure all the salary updates are captured for audit purposes. To fulfill this request, the application developer provides an AUTONOMOUS -based code to capture the salary updates and the HR director who performs the change.

The salary, which is greater than 400,000 should only be updated by the HR director after the executive committee's approval is received, but the attempt should be captured in case anyone other than the director tries to update it.

To implement an autonomous transaction, use the AUTONOMOUS keyword while creating the procedure. The AUTONOMOUS procedure runs in its own session independent of the calling procedure. A successful AUTONOMOUS procedure commits implicitly at the end of the execution and an unsuccessful one will roll back the changes.

CREATE OR REPLACE PROCEDURE
UpdateSalary (IN empCode VARCHAR(6),
IN newSalary DECIMAL (9,2))
LANGUAGE SQL
BEGIN
DECLARE oldSalary DECIMAL(9,2);
DECLARE eSal DECIMAL(9,2);
DECLARE QueryTime TIMESTAMP;
SET QueryTime= CURRENT TIMESTAMP;
SELECT salary INTO eSal FROM EMPLOYEE WHERE empNo=empCode;
SET oldSalary=eSal;
CALL logData ('Tim Wilc', empCode, QueryTime, oldSalary, newSalary );
CALL noLog ('Tim Wilc', empCode, QueryTime, oldSalary, newSalary );
UPDATE EMPLOYEE SET SALARY=newSalary WHERE EMPNO=empcode;
IF newSalary > 400000 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END@

This sample demonstrates how an AUTONOMOUS transaction differs from the standard stored procedure transaction. When Tim Wilc updates the salary of CHRISTINE to 280000 in the employee table of the sample database, both the procedures caught the change. However, while updating the salary of CHRISTINE to 480000, this activity is only caught in the AUTONOMOUS transaction, as it executes the procedure, independent of the invoking procedure.