We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database trigger:
CREATE OR REPLACE TRIGGER user_login_as
AFTER LOGON
ON SCHEMA
BEGIN
INSERT INTO user_login_log(username, log_date, action) VALUES (user, sysdate, 'User has logged in');
END user_login_as;
The trigger does not contain autonomous transaction procedure with commit inside that, so who is commiting the insert? This triggger works like a charm and inserts new record into log table after user logon. It smells like hidden Oracle functionality and I can't find any reference in Oracle docs about that. I'm using Oracle11g.
Best Answer
You get an autonomous transaction context for these triggers automatically.
From the
CREATE TRIGGER
docs:What happens if the trigger fails depends on the exact trigger/event. See Exception Handling in Triggers. In particular, a logon trigger that fails with an exception might very well lock non-dba users out - the exception makes the login fail unless the user has specific privileges.