469,341 Members | 8,207 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,341 developers. It's quick & easy.

Triggers commit and rollback through a proc.

Hi All,
The important thing about triggers is that, you can't call Transaction Control Statements(commit/rollback) inside a trigger. But what if a trigger is fired and then it makes an entry into another table (like user, sysdate and more). So when will this insert gets committed ?
Or can I call a procedure after this insert statement inside a trigger which has a COMMIT ? will this commit the insert statement ?
Thanks
Aj
Mar 20 '09 #1
9 6646
debasisdas
8,127 Expert 4TB
try to use pragma autonomous transaction in trigger..
Mar 20 '09 #2
amitpatel66
2,367 Expert 2GB
You can also SET AUTOCOMMIT ON but this is dangerous
Mar 20 '09 #3
@debasisdas
Hi, Thanks for the reply. I went through pragma autonomous and I believe that is the only way we can do a commit through an internal process.
Regards,
Aj
Mar 22 '09 #4
amitpatel66
2,367 Expert 2GB
As also suggested in the above post, that SET AUTOCOMMIT ON will also do the job. Check below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SET AUTOCOMMIT ON
  3. SQL> ed
  4. Wrote file afiedt.buf
  5.  
  6.   1  create or replace trigger t after insert on emp for each row
  7.   2  begin
  8.   3  if inserting then
  9.   4  insert into emp1 values(:new.empid,:new.empname,:new.salary,:new.mgrid,:new.deptno);
  10.   5  end if;
  11.   6* end;
  12. SQL> /
  13.  
  14. Trigger created.
  15.  
  16. SQL> insert into emp values(20,'B',1000,2,30,SYSDATE);
  17.  
  18. 1 row created.
  19.  
  20. Commit complete.
  21. SQL> select * from emp1;
  22.  
  23.      EMPID
  24. ----------
  25. EMPNAME
  26. --------------------------------------------------------------------------------
  27.     SALARY      MGRID     DEPTNO
  28. ---------- ---------- ----------
  29.         20
  30. B
  31.       1000          2         30
  32.  
  33.  
  34. SQL> rollback;
  35.  
  36. Rollback complete.
  37.  
  38. SQL> select * from emp1;
  39.  
  40.      EMPID
  41. ----------
  42. EMPNAME
  43. --------------------------------------------------------------------------------
  44.     SALARY      MGRID     DEPTNO
  45. ---------- ---------- ----------
  46.         20
  47. B
  48.       1000          2         30
  49.  
  50.  
  51. SQL> 
  52.  
Mar 23 '09 #5
Hi amitpatel66,
That was a great example. Thanks.
I didn't quite understand one aspect of this code you posted. After the auto commit, the SELECT * FROM EMPL; returned one row. But when you rolled back, why is that the same select still returns the same row. Shouldn't it be deleted and rolled back to its previous state. What I can assume from this is because we haven't created a save point so nothing rolled back. If not, kindly explain.
Regards,
Aj
Mar 23 '09 #6
debasisdas
8,127 Expert 4TB
@eeriehunk
To understand all that you need to understand what a AUTONOMOUS TRANSACTION is. and its uses.
Mar 23 '09 #7
@debasisdas
Great ! I will get on to research more on Autonomous Transaction. Thanks,
Aj
Mar 24 '09 #8
amitpatel66
2,367 Expert 2GB
Once you SET AUTOCOMMIT ON, the INSERT operation performed in the TRIGGER will be COMMITED automatically. You can see in my example that when I am interting in to EMP table, the TRIGGER is Fired and it will INSERT the same Record in to EMP1 Table. And after these transactions, a COMMIT will get Executed Automatically. So all your Transactions are COMMITED. Thus when I queried from EMP1 first time it displayed one record, and even after ROLLBACK it displayed the same record that was inserted by a TRIGGER just to show you that it is AUTO COMMITED.
Mar 24 '09 #9
Saii
145 Expert 100+
I vote for autonomous transaction but i think committing through triggers should be limited to auditing purposes only.
Apr 17 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by DP | last post: by
1 post views Thread by anders_tung | last post: by
11 posts views Thread by Markus Breuer | last post: by
2 posts views Thread by janet | last post: by
7 posts views Thread by fyi85 | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.