472,333 Members | 2,307 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,333 software developers and data experts.

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 ?
Mar 20 '09 #1
9 6762
8,127 Expert 4TB
try to use pragma autonomous transaction in trigger..
Mar 20 '09 #2
2,367 Expert 2GB
You can also SET AUTOCOMMIT ON but this is dangerous
Mar 20 '09 #3
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.
Mar 22 '09 #4
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
  3. SQL> ed
  4. Wrote file afiedt.buf
  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> /
  14. Trigger created.
  16. SQL> insert into emp values(20,'B',1000,2,30,SYSDATE);
  18. 1 row created.
  20. Commit complete.
  21. SQL> select * from emp1;
  23.      EMPID
  24. ----------
  26. --------------------------------------------------------------------------------
  27.     SALARY      MGRID     DEPTNO
  28. ---------- ---------- ----------
  29.         20
  30. B
  31.       1000          2         30
  34. SQL> rollback;
  36. Rollback complete.
  38. SQL> select * from emp1;
  40.      EMPID
  41. ----------
  43. --------------------------------------------------------------------------------
  44.     SALARY      MGRID     DEPTNO
  45. ---------- ---------- ----------
  46.         20
  47. B
  48.       1000          2         30
  51. SQL> 
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.
Mar 23 '09 #6
8,127 Expert 4TB
To understand all that you need to understand what a AUTONOMOUS TRANSACTION is. and its uses.
Mar 23 '09 #7
Great ! I will get on to research more on Autonomous Transaction. Thanks,
Mar 24 '09 #8
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
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

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

Similar topics

by: DP | last post by:
Is there any command exists as "commit transaction" ? begin transaction < DML1 stmts > begin transaction < DML2 stmts > rollback transaction...
by: anders_tung | last post by:
Hi, I have a procedure which will call 3 functions. First function will update a record. Second function will delete a record. Third function...
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the...
by: janet | last post by:
HI, I had a question on auto-commit in DB2 EEE V8 on AIX V5. Here is my example.. there are two script A.sh , B.SQL A.sh is following:
by: Re_endian_miss | last post by:
I know even less about databases than I thought... I always thought that the idea behind a transaction was that the actual table in question does...
by: vijai kumar via DBMonster.com | last post by:
Hi, I am using UDB v 8.1 on win2000. I have setup the ODBC datasources. My application is connecting to the database and performing DML...
by: fyi85 | last post by:
I have 8.1.5 on Windows 2003, when I do from CLP with auto commit off: db2 update table set column=something and then db2 terminate and then db2...
by: Ralf Assmann | last post by:
Hi there, we have the following problem using a DB2 version 7 on z/OS, referring also <a...
by: debasisdas | last post by:
trigger sample code Ex#10 ======================= INSTEAD OF TRIGGER ---------------------------------------- create or replace trigger mytrig...
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.