Home

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

Rule description

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

 

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 2020
 VEPLSQLRULE28