CODEWITHSUNDEEP
transactionsfirebirdfirebird-3.0firebird-psql
Fabrizio
Fabrizio

Reputation: 8053

How to check if running in autonomous transaction?

In a trigger, I need to do some stuffs only when the code is not running from autonomous transaction.

Is there a way to detect if the code is running in the context of an autonomous transaction?

Upvotes: 1

Views: 252

Answers (1)

Fabrizio
Fabrizio

Reputation: 8053

I found an alternative solution by using a context variable due to avoid the infinite recursion.

CREATE OR ALTER TRIGGER d_logtran
ACTIVE ON TRANSACTION START POSITION 0
AS
DECLARE VARIABLE v_val VARCHAR(1);
DECLARE VARIABLE v_idconnection bigint_t;
DECLARE VARIABLE v_idtransaction bigint_t;
DECLARE VARIABLE v_username shortdescription_t;
BEGIN
  v_val = RDB$GET_CONTEXT('USER_SESSION', 'LOCK');

  IF (v_val = 'T') THEN
    EXIT;

  RDB$SET_CONTEXT('USER_SESSION', 'LOCK', 'T');

  v_idconnection = CURRENT_CONNECTION;
  v_idtransaction = CURRENT_TRANSACTION;
  v_username = CURRENT_USER;

  IN AUTONOMOUS TRANSACTION DO
    BEGIN
      INSERT INTO log_transaction
        (idconnection, idtransaction, username)
      VALUES
        (:v_idconnection, :v_idtransaction, :v_username);
    END

  RDB$SET_CONTEXT('USER_SESSION', 'LOCK', NULL);
END

Upvotes: 1

Related Questions