Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
this answer is not elaborated , please any one can explain why we can not use commit inside a trigger ? –  user25349 Jun 25 '13 at 18:51
    
Sorry @NickChammas, meant to keep your reply intact, clicked too quickly. –  Aaron Bertrand Jun 25 '13 at 19:19
add comment

1 Answer

up vote 11 down vote accepted

You get an autonomous transaction context for these triggers automatically.

From the CREATE TRIGGER docs:

One or more particular states of the database that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. For each of these triggering events, the database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

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.

share|improve this answer
    
Thanks, missed that :) –  Centurion Nov 25 '12 at 15:32
    
It's interesting what happens if there occurs an exception in trigger body. I assume Oracle performs "silent" rollback in that case? –  Centurion Nov 25 '12 at 15:36
    
Edited with a bit more information for that. –  Mat Nov 25 '12 at 15:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.