Home

COMMIT and ROLLBACK should not be called from non-autonomous transaction triggers

Description

    The rule "COMMIT and ROLLBACK should not be called from non-autonomous transaction triggers" states that when writing PL/SQL code, COMMIT and ROLLBACK should not be used in triggers that are not autonomous transactions. Autonomous transactions are transactions that are independent of the current transaction and can be committed or rolled back without affecting the current transaction. Non-autonomous transactions are transactions that are dependent on the current transaction and any changes made to them will affect the current transaction. Therefore, when writing PL/SQL code, it is important to ensure that COMMIT and ROLLBACK are not called from non-autonomous transaction triggers to avoid any unexpected changes to the current transaction.

Key Benefits

  • Consistency: COMMIT and ROLLBACK should not be called from non-autonomous transaction triggers, ensuring that all transactions are consistent and complete.
  • Data Integrity: COMMIT and ROLLBACK should not be called from non-autonomous transaction triggers, ensuring that data integrity is maintained.
  • Auditability: COMMIT and ROLLBACK should not be called from non-autonomous transaction triggers, providing a clear audit trail for all transactions.

 

Non-compliant Code Example

CREATE TRIGGER trgCustomerAdded --Non compliant code (COMMIT is called from non-autonomous transaction triggers)
BEFORE INSERT ON CUSTOMERS FOR EACH ROW
BEGIN
   INSERT INTO CUSTOMERS VALUES(:new.pFirstName, :new.pLastName);
   COMMIT;
END;

Compliant Code Example

CREATE TRIGGER trgCustomerAdded --Compliant code (COMMIT is called from autonomous transaction triggers)
BEFORE INSERT ON CUSTOMERS FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO CUSTOMERS VALUES(:new.pFirstName, :new.pLastName);
   COMMIT;  -- allowed only in autonomous triggers
END;
Visual Expert 2024
 VEPLSQLRULE28