Autonomous_Transaction

why it is working without PRAGMA AUTONOMOUS_TRANSACTION

Can anyone explain me why it is still logs my error and provide a
sample where it does not log the code if I do not specify the PRAGMA
AUTONOMOUS_TRANSACTION directive, please?

The error is being Inserted in Log table since you are handling it as an Exception handling. You need to understand the behavior of AUTONOMOUS transaction as being an Independent piece of code which executes even if the main calling proc/pkg fails. It's not being handled as a part of Exception Handling. As shown in below demo you can see proc marked as AUTONOMOUS is called in BEGIN block directly rather than in Exception block to understand the behavior.

DECLARE
    l_salary   NUMBER;
--Private Proc marking as Autonomous transaction 
procedure nested_block ‬ 
   as 
   pragma AUTONOMOUS_TRANSACTION;
   BEGIN
     UPDATE emp
     SET salary=salary+15000
     WHERE emp_no=1002;
    COMMIT;
    END;‭
--Main Block    ‬
BEGIN
SELECT salary 
INTO l_salary 
FROM emp 
WHERE emp_no=1001; 

Dbms_output.put_line('Before Salary of 1001 is'||l_salary); 

SELECT salary 
INTO l_salary 
FROM emp WHERE emp_no=1002;

Dbms_output.put_line('Before Salary of 1002 is '|| 1_salary);

UPDATE emp
    SET
        salary = salary + 5000
WHERE emp_no = 1001;

--Calling Autonomous transaction
nested_block;

--And rolling back previous updates.
ROLLBACK;

SELECT salary INTO
    l_salary
FROM emp
WHERE emp_no = 1001;

dbms_output.put_line('After Salary of 1001 is'|| l_salary);

SELECT salary 
INTO  l_salary
FROM emp
WHERE emp_no = 1002;

dbms_output.put_line('After Salary of 1002 is ' || l_salary);

end;

Output:

The output will have the Update done in Autonomous transaction. Updates done in the main block will be rolledback but not the one which is done in private proc marked as Autonomous

Before Salary of 1001 is 15000 
Before Salary of 1002 is 10000 
After  Salary of 1001 is 15000 
After  Salary of 1002 is 25000 

AUTONOMOUS_TRANSACTION

IMO Autonomous Transactions are particularly adapted to logging: they run independently from the main session, meaning you can write in a table, commit or rollback changes without affecting the main transaction.

They also add little overhead: if you run big statements and add an autonomous transaction between each statement the performance cost will be negligible.

There is also a side-effect that you may find interesting: since the autonomous transactions are in independant sessions from the calling transaction, you can follow the progression of your main process as it is running. You don't have to wait for the main transaction to finish: you can query the logging table as it is filled by the autonomous transactions.

pragma autonomous_transaction in a trigger

Using an autonomous transaction for anything other than logging that you want to be preserved when the parent transaction rolls back is almost certainly an error. This is not a good use of an autonomous transaction.

What happens, for example, if I update a row in t1 but my transaction rolls back. The t2 changes have already been made and committed so they don't roll back. That generally means that the t2 data is now incorrect. The whole point of transactions is to ensure that a set of changes is atomic and is either completely successful or completely reverted. Allowing code to be partially successful is almost never a good idea.

I'm hard-pressed to see what using an autonomous transaction buys you here. You'll often see people incorrectly using autonomous transactions to incorrectly work around mutating trigger errors. But the code you posted wouldn't generate a mutating trigger error unless there was a row-level trigger on t2 that was also trying to update t1 or some similar mechanism that was introducing a mutating table. If that's the case, though, using an autonomous transaction is generally even worse because the autonomous transaction then cannot see the changes being made in the parent transaction which almost certainly causes the code to behave differently than you would desire.

Do autonomous transactions have their own session in Oracle?

A session can have multiple transactions, so this is all done within a single session.

SQL> create table t ( n number);

Table created.

SQL>
SQL> create or replace
  2  procedure p1 is
  3      pragma autonomous_transaction;
  4  begin
  5      insert into t values ( sys_context('userenv','sid'));
  6      commit;
  7  end;
  8  /

Procedure created.

SQL> create or replace
  2  procedure p2 is
  3      pragma autonomous_transaction;
  4  begin
  5      insert into t values ( sys_context('userenv','sid'));
  6      commit;
  7      p1;
  8  end;
  9  /

Procedure created.

SQL> create or replace
  2  procedure p3 is
  3      pragma autonomous_transaction;
  4  begin
  5      insert into t values ( sys_context('userenv','sid'));
  6      commit;
  7      p2;
  8  end;
  9  /

Procedure created.

SQL>
SQL> exec p3;

PL/SQL procedure successfully completed.

SQL> select * from t;

         N
----------
       982
       982
       982

Execute immediate 'alter trigger ... disable' with pragma autonomous_transaction in a trigger crashes Oracle Apex

Both row-level triggers fire BEFORE UPDATE on accounts table. As if Oracle can't decide it and tries to disable a trigger which is just firing to inform you that you're trying to do something you aren't supposed to and - kind of - ends up in an infinite loop.

So, which trigger will run first? As far as I can tell, Oracle doesn't specify it, but lets you decide it using follows and precedes. Something like this:

CREATE OR REPLACE TRIGGER accounts_name_surname_update_disable
   BEFORE UPDATE OF client_name, client_surname
   ON accounts
   FOR EACH ROW
   FOLLOWS accounts_insert_update                   --> this
BEGIN
   raise_application_error (
      num  => -20002,
      msg  => 'Updating client_name and client_surname is not permitted!');
END;

This trigger can be simplified (BTW, code you posted seems to be invalid; there's no such an INSERT statement - should've been SELECT):

CREATE OR REPLACE TRIGGER accounts_insert_update
   BEFORE INSERT OR UPDATE
   ON accounts
   FOR EACH ROW
   PRECEDES accounts_name_surname_update_disable    --> this
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   EXECUTE IMMEDIATE 'ALTER TRIGGER ACCOUNTS_NAME_SURNAME_UPDATE_DISABLE DISABLE';

   SELECT client_name, client_surname
     INTO :new.client_name, :new.client_surname
     FROM clients
    WHERE client_id = :new.client_id;

   EXECUTE IMMEDIATE 'ALTER TRIGGER ACCOUNTS_NAME_SURNAME_UPDATE_DISABLE ENABLE';
END;

---

On the other hand - even if that works - the whole process looks wrong to me. How will accounts_name_surname_update_disable trigger know that it should - or should not - fire and allow changes to those two columns? To me, only one trigger would suffice - the second one (but modified).

So: drop trigger accounts_name_surname_update_disable as it is useless.

Recreate the 2nd trigger: no need to disable/enable anything as that another trigger doesn't exist any more. Always fetch client name from clients (so, it doesn't matter that someone might type those values - trigger will override them):

CREATE OR REPLACE TRIGGER accounts_insert_update
   BEFORE INSERT OR UPDATE
   ON accounts
   FOR EACH ROW
BEGIN
   SELECT client_name, client_surname
     INTO :new.client_name, :new.client_surname
     FROM clients
    WHERE client_id = :new.client_id;
END;

---

Finally, is that all? I don't think so. The whole data model is wrong. ACCOUNTS table should contain only CLIENT_ID column which is a foreign key, looking at CLIENTS.CLIENT_ID:

create table clients 
  (client_id        number primary key,
   client_name      varchar2(20) not null,
   client_surname   varchar2(20) not null
  );
  
create table accounts
  (account_id       number primary key,
   client_id        number constraint fk_acc_cli references clients (client_id)
  );

Therefore, when accounts needs to know client's name, query will perform a join and fetch that data:

select a.account_id, c.client_name
from accounts a join clients c on c.client_id = a.client_id
where a.account_id = 1234;

And that should solve your problem.

---

Related Topics