On Mon, Apr 25, 2011 at 1:45 PM, homerjones1941 <homer@...> wrote:

> Is it possible to use transactions in a stored procedure? I've looked it up in Helen's book and done the Google thing, but I am not able to make it work.

As others have said, you can do it with autonomous transactions in 2.5
or EXECUTE STATEMENT in earlier versions,
but not directly. The original design for stored procedures intended
them to be like other DML statements - they
do work, but they don't affect the state of the client transactions.
The database client determines what actions
will become permanent. A procedure should not commit or rollback
client actions as a side-effect.

>
> Basically, the stored procedure reads (and summarizes) info from an event log table, and then creates a "summary" record in a separate table. If the new record is created, I want to mark a flag field in the source, event log table so that the source records will not be processed a second time.

I don't see why you need to commit anything in that procedure. Your
transaction will see the results
of the procedure without their being committed, because that procedure
is just part of your transaction.

>
> I've failed at creating the transaction in a stored procedure so tried to do the above in Delphi. I failed there. Obviously, I'm just learning about Firebird transactions (a lot different than MS Access).

Yes it is.


Good luck,

Ann