Re: Stored Procedures

On Tue, Sep 21, 2004 at 07:41:31AM -0400, Kent Anderson wrote:
> We are currently switching to stored procedures for a lot of our database
> activity. The question has come up about the transactional nature of the
> stored procedures. I was wondering if stored procedures can have
> transactions in them or if you must start the transaction in your code and
> call the stored procedure from there to get the safety of a transaction?

There's only one transaction (whether it's an explicit transaction block
or an implicit one), and the query that invokes the stored procedure is
already running inside it. So the stored procedure always has the
safety of it, and it can't get out (except by raising an error and
aborting the whole thing). The transaction can only be committed
_after_ the stored procedure has finished succesfully.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Jajaja! Solo hablaba en serio!