I am new in oracle using oracle sql developer in windows7.I am little bit confuse in that I am using commit in a trigger as given :
create or replace trigger comt after insert on tbl_city declare pragma autonomous_transaction; begin commit; dbms_output.put_line('Value is committed'); end;
Now when I perform an
insert into tbl_city values (1,'XYZ',1);
in tbl_city---->trigger fires properly and gives an output stream
.Value is committed
But If I perform
now --->there are the data rollbacked in table.
why this is happen ?I think after commit(which is in trigger associated at insert to table)there should no any rollback in table.
Please give me solution.
pragma autonomous_transaction here works to commit the particular trnsaction,
In Triggers " Do All transaction or else nothing" ... (i.e )Rollback to previous stage, so commit some changes alone they will use pragma autonomous_transaction , to Commit the Transaction.
Autonomous transaction opens a separate transaction which is no different from transactions, say, opened by other sessions. It is executed in a separate context and same as any other transaction doesn't see any (and therefore can neither commit nor rollback) uncommitted changes made by invoking transaction. All it can do is commit/rollback own changes.
For your Better Understand of Autonomous transaction., follow My Code, feel the magic of Autonomous transaction.
Step1: Execute this block without giving commit.
Create Table emp1(sal number);
for i in 1..5 loop
INSERT INTO emp1
Step 2: Execute this Block used Autonomous transaction.
for i in 1..10 loop
INSERT INTO emp1
Totally 15 rows u have inserted into emp table on SAL column right.
Now Give ROLLBACK...
And issue select statement now,
Select * from emp1;
you can get to know still the 10 rows remaing .. that's the use of PRAGMA AUTONOMOUS_TRANSACTION.
for commiting the session wise transactions...
Pragma autonomous_transaction means, Commit only this transaction ( where you have specified this PRAGMA), by suspending all the other transactions.
NO - NO - NO!
It doesn't mean 'COMMIT' anything at all. And it doesn't SUSPEND 'all the other transactions'.
The pragma only causes the suspension of the CURRENT transaction and the creation of a new one.
It is up to that new transaction to perform EITHER a COMMIT or a ROLLBACK before completing.
Autonomous transactions can be rolled back just like any other transaction.
Lets say we have transaction T1.
Somewhere in T1 we are calling Pragma autonomous_transaction, lets call it T2.
While executing T1, as and when the execution reaches to T2, T1 gets suspended while T2 is getting executed. After T2, is completed, the control returns back to T1 again.