Home » SQL & PL/SQL » SQL & PL/SQL » Can we use commit in triggers?
Can we use commit in triggers? [message #35848] Fri, 19 October 2001 06:52 Go to next message
anoop
Messages: 15
Registered: October 2001
Junior Member
What is the reason that we can't use commit in triggers?

----------------------------------------------------------------------
Re: Can we use commit in triggers? [message #35965 is a reply to message #35848] Fri, 26 October 2001 03:32 Go to previous message
Mwakuye
Messages: 1
Registered: October 2001
Junior Member
Yes, With Oracle 8i .. u can now do a Commit or Rollback from within a Trigger...a pre-condition is that u use the new:
PRAGMA AUTONOMOUS TRANSACTION 'thing' in the Declare Section.

Eg: create or replace trigger my_trg_air
after insert on my_table for each row
Declare

PRAGMA AUTONOMOUS_TRANSACTION ;

Begin
insert into my_table_history
values(:new.field,.......) ;

--now u can actually do a Commit Here!!!
COMMIT;
--u can go on and further rollback if u r not satisfied
exception
when my_defined_exception then
rollback;
raise_application_error(-20001,'i don't like it');

end;
/

Hope that Helps.

Tschüs
Kwa Heri
Bye

Pasko M



----------------------------------------------------------------------
Previous Topic: Documenting advice
Next Topic: URGENT-Difference between PL/SQL and SQL*PLUS
Goto Forum:
  


Current Time: Sat Apr 04 17:41:58 CDT 2020