mysql存储过程中控制事务引发的问题

[yuqi508]

下面的语句在@1处，发生错误，为什么不能发生事务回滚？

main:BEGIN
DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
ROLLBACK;

START TRANSACTION;

INSERT INTO file_table(name,type,location)
VALUE(i_name,i_type,i_location);

--@1---
INSERT INTO log_table(LOGNAME, LOGMSG,USERID,LOGDATE)
VALUES('ff',null,i_userid,NOW());

COMMIT;

END main

[faisun]

在 @1 处增加判断影响的行数,若为 0 ,则回滚.
不会自动的

[懒得去死]

main:BEGIN
DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  
ROLLBACK;
declare i int;
set i = 0;
START TRANSACTION;

INSERT INTO file_table(name,type,location)
  VALUE(i_name,i_type,i_location);  
SAVEPOINT my_sa;
set i = 1;
        --@1---
INSERT INTO log_table(LOGNAME, LOGMSG,USERID,LOGDATE)
VALUES('ff',null,i_userid,NOW());
set i = 2;
if i = 2 then
commit;    
else if i = 1 then
rollback to savepoint my_sa;
release savepoint my_sa;
else
  ROLLBACK;
end if;
 
END main