MySQL Stored Procedure mit Transaktion

jmar83

SQL-Guru
Beiträge
146
Hallo zusammen

Es geht weder

Code:
USE relay;

DROP PROCEDURE IF EXISTS spex_delete_node;

DELIMITER ||
CREATE DEFINER=`root`@`localhost` PROCEDURE `call spex_delete_node`(IN `_id` INT)
proc_label:BEGIN

START TRANSACTION;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        EXIT PROCEDURE;
    END;

    DELETE FROM `device_location` WHERE `location_id`=_id;
    DELETE FROM `vswitch_location` WHERE `location_id`=_id;
    DELETE FROM `rights_level_permission` WHERE `location_id`=_id;
    DELETE FROM `location` WHERE `location_id`=_id;
    DELETE FROM `shared_things` WHERE `things_type`= 1  AND  `things_id`=_id OR `things_type`= 2  AND  `things_id`=_id;
    COMMIT;

END;
||
DELIMITER ;

noch

Code:
USE relay;

DROP PROCEDURE IF EXISTS spex_delete_node;

DELIMITER ||
CREATE DEFINER=`root`@`localhost` PROCEDURE `call spex_delete_node`(IN `_id` INT)
proc_label:BEGIN

START TRANSACTION;
    DELETE FROM `device_location` WHERE `location_id`=_id;
    DELETE FROM `vswitch_location` WHERE `location_id`=_id;
    DELETE FROM `rights_level_permission` WHERE `location_id`=_id;
    DELETE FROM `location` WHERE `location_id`=_id;
    DELETE FROM `shared_things` WHERE `things_type`= 1  AND  `things_id`=_id OR `things_type`= 2  AND  `things_id`=_id;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        EXIT PROCEDURE;
    END;


    COMMIT;

END;
||
DELIMITER ;

Das Tool, mit dem ich die SP versuche reinzuladen ist "HeidiSQL".

MySQL : transaction within a stored procedure

Danke für die Feedbacks.
 
Werbung:
Werbung:
Noch ne Frage, falls das jemand weiss: Für was ist "EXIT PROCEDURE;" innerhalb des Rollbacks? Muss das explizit so dastehen?
Danke für die Feedbacks.
 
Zurück
Oben