Mysql Transaction (Mysql)

Question

Mysql Transaction (Mysql)

Navigation

#1 by (3 votes)

1

I have a concern as I can improve this query. Well, I'm learning to use Transaction. one of my doubts is how can I use the rollback on error

DELIMITER $$
CREATE PROCEDURE sp_insert_transaction(
IN _id_usuario INT,
IN _id_proveedor INT,
IN _fecha_ingreso INT)
BEGIN
SET
    AUTOCOMMIT = 0 ;
START TRANSACTION
    ;
INSERT INTO tb_ingreso(
    id_usuario,
    id_proveedor,
    fecha_ingreso
)
VALUES(
_id_usuario,
_id_proveedor,
_fecha_ingreso
) ;
SELECT
LAST_INSERT_ID() ;
   COMMIT;
END $$
DELIMITER
;

mysql c# insert

asked by Ronald Funes 04.05.2018 в 17:47

source

1 answer

Add values of a two-dimensional array and throw your total PHP Make preloader last 5sec

score 3 · Accepted Answer

You can use a try / catch and when there is an error you call the rollback. I also add a query to get the error

BEGIN TRY  
    --consulta con error
END TRY  
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  

    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;