Calling COMMIT or ROLLBACK from within a trigger will lead to an ORA-04092 exception, unless the trigger has
its own autonomous transaction.
Noncompliant code example
SET SERVEROUTPUT ON
CREATE TABLE accounts(
balance NUMBER
);
INSERT INTO accounts VALUES(0);
CREATE TABLE log(
message VARCHAR2(100)
);
CREATE TRIGGER beforeLogger
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
INSERT INTO log VALUES('Attempt to update the value from ' || :OLD.balance || ' to ' || :NEW.balance);
COMMIT; -- Noncompliant, will fail with a ORA-04092
END;
/
-- We want to be able to log any attempt to update the "accounts" table
BEGIN
UPDATE accounts SET balance = 100;
ROLLBACK; -- Ultimately, this update is rolled back, however we still want to log it
END;
/
SELECT * FROM log;
DROP TRIGGER beforeLogger;
DROP TABLE log;
DROP TABLE accounts;
Compliant solution
SET SERVEROUTPUT ON
CREATE TABLE accounts(
balance NUMBER
);
INSERT INTO accounts VALUES(0);
CREATE TABLE log(
message VARCHAR2(100)
);
CREATE TRIGGER beforeLogger
BEFORE UPDATE ON accounts
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log VALUES('Attempt to update the value from ' || :OLD.balance || ' to ' || :NEW.balance);
COMMIT; -- Compliant, commits the trigger's autonomous transaction, not the main one
END;
/
-- We want to be able to log any attempt to update the "accounts" table
BEGIN
UPDATE accounts SET balance = 100;
ROLLBACK; -- Ultimately, this update is rolled back, however we still want to log it
END;
/
SELECT * FROM log;
DROP TRIGGER beforeLogger;
DROP TABLE log;
DROP TABLE accounts;
