Re: Committing an Inner Transaction question without affecting an Outer Transaction.

DaLoverhino wrote:

> Hello.
>
> I'd like to be able to do something like the following:
>
> begin transaction
>
> -- Do stuff
> exec myproc;
> -- Do stuff
> exec myproc;
>
> commit; --OUTER BLOCK COMMIT
>
>
> Now, myproc looks like this:
>
> create procedure myproc as
> begin
> insert into MyTable ....;
> commit; --PROC COMMIT
> end;
>
>
> I want to make sure that the insert into MyTable happens all the time,
> when the proc gets called from the outer block, regardless of whether
> the outer block commits or not. Furthermore, I don't want the PROC
> COMMIT to commit any DML's issued in the outer block prior to the proc
> commit.
>
> How do I go about doing this?
>
> thanks.

http://www.psoug.org
click on Morgan's Library
click on AUTONOMOUS TRANSACTION

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)