Triggers commit and rollback through a proc.

[eeriehunk]

Hi All,
The important thing about triggers is that, you can't call Transaction Control Statements(comm it/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

[debasisdas]

try to use pragma autonomous transaction in trigger..

[amitpatel66]

You can also SET AUTOCOMMIT ON but this is dangerous

[eeriehunk]

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

[amitpatel66]

As also suggested in the above post, that SET AUTOCOMMIT ON will also do the job. Check below:

[code=oracle]

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.emp id,:new.empname ,:new.salary,:n ew.mgrid,:new.d eptno);
5 end if;
6* end;
SQL> /

Trigger created.

SQL> insert into emp values(20,'B',1 000,2,30,SYSDAT E);

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>
[/code]

[eeriehunk]

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

[debasisdas]

Hi amitpatel66,
I didn't quite understand one aspect of this code you posted......... ............... ............... Shouldn'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.

[eeriehunk]

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

[amitpatel66]

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.

[Saii]

I vote for autonomous transaction but i think committing through triggers should be limited to auditing purposes only.