By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 1,963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Triggers commit and rollback through a proc.

P: 55
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
Share this Question
Share on Google+
9 Replies


debasisdas
Expert 5K+
P: 8,127
try to use pragma autonomous transaction in trigger..
Mar 20 '09 #2

amitpatel66
Expert 100+
P: 2,367
You can also SET AUTOCOMMIT ON but this is dangerous
Mar 20 '09 #3

P: 55
@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
Expert 100+
P: 2,367
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

P: 55
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
Expert 5K+
P: 8,127
@eeriehunk
To understand all that you need to understand what a AUTONOMOUS TRANSACTION is. and its uses.
Mar 23 '09 #7

P: 55
@debasisdas
Great ! I will get on to research more on Autonomous Transaction. Thanks,
Aj
Mar 24 '09 #8

amitpatel66
Expert 100+
P: 2,367
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
Expert 100+
P: 145
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.