Can we use commit in triggers? [message #35848] |
Fri, 19 October 2001 06:52 |
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 |
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
----------------------------------------------------------------------
|
|
|