regarding triggers

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

regarding triggers

Postby rajasekhar » Thu Sep 26, 2013 6:24 am

Hi..why can't we use commit,rollback in triggers...share me reasons
rajasekhar
Member
 
Posts: 1
Joined: Thu Sep 26, 2013 6:19 am

Re: regarding triggers

Postby Tim... » Thu Sep 26, 2013 6:43 pm

Hi.

A trigger runs as "part" of the current transaction. If it were allowed to commit or rollback,it would affect the transaction that triggered it. Imagine this.

Code: Select all
BEGIN
  INSERT INTO t1 VALUES (1,2,3);
 
  UPDATE t2
  SET    col1 = 1
  WHERE  col2 = 5;
 
  IF SQL%ROWCOUNT = 0 THEN
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;
END;
/


The insert happens. If the update touched zero rows, the previous insert it rolled back otherwise it is committed. The two DMLs form the transaction.

Imagine if t1 had a trigger with a commit. You would not be able to rollback the insert to t1 in this example, as it would have been committed by the trigger.

This is why a commit/rollback in a trigger is not allowed. It would break the transactions!

If you need to perform a specific action in trigger that needs a commit, like logging, then that action can be performed in a procedure defined as an autonomous transaction.

http://www.oracle-base.com/articles/mis ... ctions.php

Autonomous transactions can be very dangerous and confusing to other coders, so be careful how they are used.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 2 guests