This forum is currently locked. You can't register or post questions at this time. (read more)

regarding triggers

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

rajasekhar
Member
Posts: 1
Joined: Thu Sep 26, 2013 6:19 am

regarding triggers

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

Hi..why can't we use commit,rollback in triggers...share me reasons

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests