当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Rollback transaction on error

问题描述:

I am using this query to add rows in two different column

 START TRANSACTION;

SET @c=0;

INSERT INTO tbl_chart (acode,adesc) VALUES (2,'3');

INSERT INTO tbl_pics (ID ,adesc) VALUES (1,'1');

select ROW_COUNT() into @c ;

IF ( c > 0) THEN

COMMIT ;

ELSE

ROLLBACK;

END IF

and trying to check row_count and save its value in variable c .

But this query gives error and it also saves the data after showing the error .

where is the problem ?

And the error is this :

 /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF ( c > 0) THEN

COMMIT' at line 1 */

网友答案:

Taken from another post MYSQL If statement in transaction causing error

MySQL doesn't recognize a statement beginning with the keyword IF as as a valid SQL statement.

The IF statement works only in the context of a compound statement (that is, a block of statements enclosed between BEGIN and END. Currently, the compound statement is only supported in the context of a stored program (stored procedure, function or trigger.)

网友答案:

As Rahul already stated, IF statements work only in a stored procedure, a function or a trigger.

I'd just like to add, that you don't need to count at all if or how many rows were inserted. The following is an example of how to solve this in a stored procedure. It's your choice though, if you rather implement the logic behind it in your application code.

drop procedure if exists sp_my_transaction;
delimiter $$
create procedure sp_my_transaction()
begin

declare continue handler for sqlexception
begin
   rollback;
   exit procedure;
end;


start transaction;
   INSERT INTO tbl_chart (acode,adesc)  VALUES (2,'3');
   INSERT INTO tbl_pics  (ID ,adesc) VALUES (1,'1');
commit;

end $$
delimiter ;

You would then execute the procedure with

call sp_my_transaction();

The logic behind it is, that you define with

declare continue handler for sqlexception

what to do, when an error occurs. If this happens and you don't have a continue handler, the procedure would simply abort and the transaction would still be open. The code after the point where the error occurs never gets reached in this case, so your transaction won't be committed either.

分享给朋友:
您可能感兴趣的文章:
随机阅读: