Mysql transaction commit and rollback example

Question

Following mysql query is giving the following error.

QUERY -

    set @rollback = 0; 
    start transaction;    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @rollback = 1;
    INSERT INTO `tablea` (`date`) VALUES (NOW());
    INSERT INTO `tableb` (`date`) VALUES (NOW());
    INSERT INTO `tablec` (`date`) VALUES (NOW());
    IF @rollback THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;

error :

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 'DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @rollback = 1' at line 1

The statements are within a stored procedure? See http://stackoverflow.com/a/19908197/1316440 for an example. — wchiquito, Jan 26 '15 at 13:46

score 0 · Answer 1 · answered Oct 02 '15 at 23:57

0

DECLARE can only be used between BEGIN and END statement which can only appear within a Stored Procedure, Function, Trigger, or Event definition.

See: http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-compound-statements.html

answered Oct 02 '15 at 23:57

Crater

349
4
5

Mysql transaction commit and rollback example

1 Answers1