<< FOR SELECT INTO ... DO | FB 2.5 Language Reference | LEAVE >>

IN AUTONOMOUS TRANSACTION

Available in: PSQL

Added in: 2.5

Description

Code running in an autonomous transaction will be committed immediately upon successful completion, regardless of how the parent transaction finishes. This is useful if you want to make sure that certain actions will not be rolled back, even if an error is raised later.

Syntax

 IN AUTONOMOUS TRANSACTION DO <psql-statement>

Example

 create trigger tr_connect on connect
 as
 begin
    -- make sure log message is always preserved:
    in autonomous transaction do
       insert into log (msg) values ('User ' || current_user || ' connects.');
    if (current_user in (select username from blocked_users)) then
    begin
       -- again, log message must be preserved and event posted, so:
       in autonomous transaction do
       begin
          insert into log (msg) values ('User ' || current_user || ' refused.');
          post_event 'Connection attempt by blocked user.';
       end
       -- now we can safely except:
       exception ex_baduser;
    end
 end

Notes:

• Autonomous transactions have the same isolation level as their parent transaction.
• Because the autonomous transaction is completely independent of its parent, care must be taken to avoid deadlocks.
• If an exception occurs within the autonomous transaction, the work will be rolled back.

back to top of page
<< FOR SELECT INTO ... DO | FB 2.5 Language Reference | LEAVE >>