Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62

    Thumbs up Unanswered: COmmit inside a trigger

    hi,

    What is the reason we cannot give commit, rollback , savepoint..

    inside a trigger?



    thanx.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: COmmit inside a trigger

    Because it wouldn't make sense? Actually you can, if you declare an autonomous transaction in your trigger, but you should only do this if you want your trigger's changes to persist even if the triggering statement is rolled back. An example might be to log attempts to update a record, whether successful or not:

    PHP Code:
    SQLcreate trigger emp_log
      2  before update on emp
      3  
    for each row
      4  
    declare
      
    5    pragma autonomous_transaction;
      
    6  begin
      7    insert into emp_log 
    (empnousernamechange_date values (:new.empnousersysdate );
      
    8    commit;
      
    9end;
    SQL> /

    Trigger created.

    SQLselect from emp_log;

    no rows selected

    SQL
    update emp set sal=sal*2 where ename='KING';

    1 row updated.

    SQLrollback;

    Rollback complete.

    SQLselect from emp_log;

         
    EMPNO USERNAME                       CHANGE_DATE
    ---------- ------------------------------ -----------
          
    7839 TANDREWS                       24-FEB-2004 
    In most other cases, triggered actions should be committed or not according to whether the triggering action is committed or not.

  3. #3
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    thanx for your reply.

    but again

    'Because it wouldn't make sense?' you wrote this..

    i know we can use autonomus transaction, but my question is..

    in a normal trigger ; after performing the actions inside that trigger ;
    will it get commited automatically??
    i meant im calling a procedure from a trigger which tries to insert datas to a table...if im not using commit, will it get commited?

    hope i didnt confused you.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, any DML performed by the trigger, or by procedures called by the trigger, is part of the same transaction as the triggering statement. It is either all committed, or all rolled back, with the triggering statement. This ensures that database consistency is maintained.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •