Handler, Transaction and procedure

A simple example of usage of handler, transaction and procedure

CREATE TABLE CTEMP (ID INT);

CREATE TABLE DTEMP (ID INT PRIMARY KEY);

DELIMITER //
CREATE PROCEDURE insertcd
(a int, b int)
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
ROLLBACK;
START TRANSACTION;

insert into ctemp values(a);
insert into dtemp values(b);



COMMIT;
END;//

DELIMITER ;

CALL INSERTCD(2,2);

SELECT *FROM CTEMP;

Shows one row

SELECT *FROM DTEMP;

Shows one row

CALL INSERTCD(2,2);

SELECT *FROM CTEMP;

Shows one row (insert is successful for second row for table ctemp but handler as rollback it becuase of insert statement for table dtemp fails)

SELECT *FROM DTEMP;

Shows one row