MySQL Forums :: Stored Procedures :: transaction in stored procedure

Advanced Search

transaction in stored procedure
Posted by: arun parajuli ()
Date: April 06, 2005 01:15AM

I am trying to write a stored procedure , which uses transaction,in MySql 5.0.2 alpha.

the following is a sample code.

create procedure test_db.test_proc(IN id int,IN name varchar(20) )
start transaction;
--insert into table test1;

-- use some SQL which generates error.

the table test is InnoDB

my broblem is
1) when error occurs, the procedure exits without rolling back( no commit either)the modifications and when i do rollback manually, it rolls back ( i.e the insertsion in the table test1 is undone). I want this automatic.
2)how can I know if any error occured or not ? I want to write a handler which can trap all errors, warnings etc so that i can use a rollback statement there

please somebody suggest me a solution.. Its killing me

Options: ReplyQuote

Subject Views Written By Posted
transaction in stored procedure 31322 arun parajuli 04/06/2005 01:15AM
Re: transaction in stored procedure 13737 Andrew Gilfrin 04/06/2005 02:27PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.