MySQL Forums
Forum List  »  Stored Procedures

Re: Keep inserted rows in Temp table - after a rollback
Posted by: Anandkumar Sagar
Date: February 01, 2019 04:44AM

my sample stored procedure: when handler gets executed where it rollback data. i want retain data of temptable even after rollback. how can it be possible. in SQL server i can do it using table variable.

CREATE PROCEDURE CustomerCleanup()
BEGIN
CREATE TEMPORARY TABLE log(logmessage LONGTEXT);
BLOCK1:BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finishedTables = TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- GET DIAGNOSTICS 1 msg = MESSAGE_TEXT;
INSERT INTO temptable VALUES('Rolling back transaction ');
ROLLBACK ;
END;


START TRANSACTION;
//insert into table1
//insert into table2

INSERT INTO temptable(logMessage) VALUES ('log message '));
COMMIT;


END BLOCK1;
select * from temptable;
END;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Keep inserted rows in Temp table - after a rollback
1030
February 01, 2019 04:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.