Triggered!

I must be getting old or something. I know my faculties are probably a bit past their sell by date and that I’m probably getting a little mellow in my old age but I’m not quite so far gone that I support Brexit or read the Daily Fail. Imagine my surprise then when a developer presented me with this and I couldn’t work out what was wrong with it

CREATE OR REPLACE TRIGGER AI_AUDIT
AFTER INSERT ON PROD.ACCOUNTS_TABLE
FOR EACH ROW
DECLARE
my_user sys.dba_users.username%TYPE;
BEGIN
SELECT USER INTO my_user FROM DUAL;
INSERT INTO PROD.ACCOUNT_AUDIT (
OLD_BALANCE,
NEW_BALANCE,
UPDATED_BY,
UPDATE_DATE)
VALUES (
:old.account_balance,
:new.account_balance,
my_user,
sysdate);
COMMIT;
END;

The error they were getting was pretty self explanatory ORA04092: cannot COMMIT in a trigger
But why not? All you want to do is insert a row into an audit table right?
I’m sure I’ve done this before, it’s how a lot of auditing used to work in the bad old days

Well the answer really stares you in the face. Oracle doesn’t know the point at which this trigger is going to fire within your original transaction. It could be at the beginning, half way through or at the end. You may want to move on and update a whole bunch of other tables – it doesn’t know so it can’t allow you to commit or rollback as that would stop the calling transaction dead in its tracks

Consequently, by default oracle won’t allow a commit inside a trigger

Or will it……..

Step up this little beauty

PRAGMA AUTONOMOUS_TRANSACTION. Add this to the trigger code as

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
my_user sys.dba_users.username%TYPE;
etc………

And a miracle happens – you can commit!

What’s happening here is that this little jobbie allows the trigger to execute in a transaction of its own, independent of whatever caused it to fire. So the trigger can commit, roll back or generally go as mental as it wants without affecting the calling transaction. Commits and rollbacks don’t get propagated back

I can’t believe I didn’t spot it straight away. I really must be getting soft in my advancing years as instead of berating said developer for using such an antiquated, obvious and inefficient mechanism for auditing I actually helped fix it for them

The next thing you know I’ll be talking about bowel movements or why cousin Sheila should never have married that yobbo…….