Triggers commit and rollback through a proc. | Member | | Join Date: Sep 2007
Posts: 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
|  | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,508
| | | re: Triggers commit and rollback through a proc.
try to use pragma autonomous transaction in trigger..
|  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Triggers commit and rollback through a proc.
You can also SET AUTOCOMMIT ON but this is dangerous
| | Member | | Join Date: Sep 2007
Posts: 55
| | | re: Triggers commit and rollback through a proc.
Quote:
Originally Posted by debasisdas  try to use pragma autonomous transaction in trigger.. 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
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Triggers commit and rollback through a proc.
As also suggested in the above post, that SET AUTOCOMMIT ON will also do the job. Check below: -
-
SQL> SET AUTOCOMMIT ON
-
SQL> ed
-
Wrote file afiedt.buf
-
-
1 create or replace trigger t after insert on emp for each row
-
2 begin
-
3 if inserting then
-
4 insert into emp1 values(:new.empid,:new.empname,:new.salary,:new.mgrid,:new.deptno);
-
5 end if;
-
6* end;
-
SQL> /
-
-
Trigger created.
-
-
SQL> insert into emp values(20,'B',1000,2,30,SYSDATE);
-
-
1 row created.
-
-
Commit complete.
-
SQL> select * from emp1;
-
-
EMPID
-
----------
-
EMPNAME
-
--------------------------------------------------------------------------------
-
SALARY MGRID DEPTNO
-
---------- ---------- ----------
-
20
-
B
-
1000 2 30
-
-
-
SQL> rollback;
-
-
Rollback complete.
-
-
SQL> select * from emp1;
-
-
EMPID
-
----------
-
EMPNAME
-
--------------------------------------------------------------------------------
-
SALARY MGRID DEPTNO
-
---------- ---------- ----------
-
20
-
B
-
1000 2 30
-
-
-
SQL>
-
| | Member | | Join Date: Sep 2007
Posts: 55
| | | re: Triggers commit and rollback through a proc.
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
| | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,508
| | | re: Triggers commit and rollback through a proc.
Quote:
Originally Posted by eeriehunk Hi amitpatel66,
I didn't quite understand one aspect of this code you posted.......................................Shoul dn't it be deleted and rolled back to its previous state. To understand all that you need to understand what a AUTONOMOUS TRANSACTION is. and its uses.
| | Member | | Join Date: Sep 2007
Posts: 55
| | | re: Triggers commit and rollback through a proc.
Quote:
Originally Posted by debasisdas To understand all that you need to understand what a AUTONOMOUS TRANSACTION is. and its uses. Great ! I will get on to research more on Autonomous Transaction. Thanks,
Aj
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Triggers commit and rollback through a proc.
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.
|  | Expert | | Join Date: Apr 2007
Posts: 141
| | | re: Triggers commit and rollback through a proc.
I vote for autonomous transaction but i think committing through triggers should be limited to auditing purposes only.
|  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
