Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

I want to run an update query against a production database and as good little developer I am trying to make it as safe as possible. I am looking to do the following

BEGIN TRANSACTION
    UPDATE table_x SET col_y = 'some_value'
    .
    .
    .
IF (@@error <> 0)
BEGIN
    ROLLBACK
END
ELSE
BEGIN
    COMMIT
END

The above should work in SQL Server but I need this to work against a MySQL database.

EDIT: Sorry, there is more than 1 statement to execute. Yes I am aware of not needing to wrap a single query in a transaction.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
664 views
Welcome To Ask or Share your Answers For Others

1 Answer

CREATE PROCEDURE prc_test()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
  END;
  START TRANSACTION;
    INSERT
    INTO t_test VALUES ('test', 'test');
    INSERT
    INTO no_such_table
    VALUES ('no');
  COMMIT;
END;

CALL prc_test();

SELECT *
FROM t_test;

0 rows fetched.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...