Sfrgttk

Was it really inappropriate to write a pull request for the company I interviewed with?

Are E natural minor and B harmonic minor related?

Can I negotiate a patent idea for a raise, under French law?

Sampling from Gaussian mixture models, when are the sampled data independent?

How do spaceships determine each other's mass in space?

Is there a logarithm base for which the logarithm becomes an identity function?

What do you call someone who likes to pick fights?

Idiom for feeling after taking risk and someone else being rewarded

Help! My Character is too much for her story!

How do we create new idioms and use them in a novel?

Why restrict private health insurance?

Why does Central Limit Theorem break down in my simulation?

What does the Digital Threat scope actually do?

Short scifi story where reproductive organs are converted to produce "materials", pregnant protagonist is "found fit" to be a mother

Is "cogitate" used appropriately in "I cogitate that success relies on hard work"?

How can I portion out frozen cookie dough?

Why is there an extra space when I type "ls" on the Desktop?

What can I do if someone tampers with my SSH public key?

How to make sure I'm assertive enough in contact with subordinates?

If nine coins are tossed, what is the probability that the number of heads is even?

What should I do when a paper is published similar to my PhD thesis without citation?

Are all players supposed to be able to see each others' character sheets?

Has a sovereign Communist government ever run, and conceded loss, on a fair election?

Difference between `nmap local-IP-address` and `nmap localhost`

Rollback any duplicate occured using EXIT HANDLER

stored procedure returns ERROR 1305 (42000): FUNCTION does not existCannot create stored procedureHow long will a temporary MEMORY table persist if I don't drop it (MySQL)Rollback parent procedure on failure in MySQLMultiple SELECT subqueries in an INSERT statement in a stored procedureMySQL Cluster lock wait timeout exceeded when trying to deal with huge amount of queriesmySql while loop ending after a insertDatabase Trees with MySqlCall to default values (MySQL) in a procedureMysql trigger to update if date match and insert if no match all BEFORE INSERT

0

I have two INT integer types in my table that set as UNIQUE KEY to avoid duplication. I wanted to rollback any duplicate occur in my stored procedure using EXIT HANDLER.

CREATE TABLE curriculum

(

  id INT PRIMARY KEY AUTO_INCREMENT,

  gradelevel_id INT,

  schoolyear_id INT,

  CONSTRAINT uc_curriculum UNIQUE (gradelevel_id, schoolyear_id)

)



DELIMITER //

CREATE PROCEDURE insertCurriculum(

   IN pIN_gradelevelId INT, 

   IN pIN_schoolyearId INT

)

BEGIN



  DECLARE EXIT HANDLER FOR SQLEXCEPTION

  BEGIN

     ROLLBACK;

        SELECT "Duplicate keys found";

     RESIGNAL;

  END;



  INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (pIN_gradelevelId, pIN_schoolyearId);



END //

DELIMITER ;

I inserted manually in my curriculum table for the test.

INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (1, 1);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1`

FIRST TRY:

Calling my first stored procedure that has duplicate it automatically rollback.

call insertCurriculum(1, 1);

call insertCurriculum(2, 2);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1`

SECOND TRY:

Calling my first stored procedure that has no duplicate it allows the first stored procedure to be executed and insert into my table. Even the second stored procedure has duplicate. I wanted to rollback this. How can I achieve this? Any tips will greatly appreciated!

call insertCurriculum(2, 2);

call insertCurriculum(1, 1);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1

 2           2              2`

mysql

share|improve this question

asked Jun 21 '17 at 4:25

FrancisunoxxFrancisunoxx

1011

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

0

I have two INT integer types in my table that set as UNIQUE KEY to avoid duplication. I wanted to rollback any duplicate occur in my stored procedure using EXIT HANDLER.

CREATE TABLE curriculum

(

  id INT PRIMARY KEY AUTO_INCREMENT,

  gradelevel_id INT,

  schoolyear_id INT,

  CONSTRAINT uc_curriculum UNIQUE (gradelevel_id, schoolyear_id)

)



DELIMITER //

CREATE PROCEDURE insertCurriculum(

   IN pIN_gradelevelId INT, 

   IN pIN_schoolyearId INT

)

BEGIN



  DECLARE EXIT HANDLER FOR SQLEXCEPTION

  BEGIN

     ROLLBACK;

        SELECT "Duplicate keys found";

     RESIGNAL;

  END;



  INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (pIN_gradelevelId, pIN_schoolyearId);



END //

DELIMITER ;

I inserted manually in my curriculum table for the test.

INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (1, 1);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1`

FIRST TRY:

Calling my first stored procedure that has duplicate it automatically rollback.

call insertCurriculum(1, 1);

call insertCurriculum(2, 2);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1`

SECOND TRY:

Calling my first stored procedure that has no duplicate it allows the first stored procedure to be executed and insert into my table. Even the second stored procedure has duplicate. I wanted to rollback this. How can I achieve this? Any tips will greatly appreciated!

call insertCurriculum(2, 2);

call insertCurriculum(1, 1);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1

 2           2              2`

mysql

share|improve this question

asked Jun 21 '17 at 4:25

FrancisunoxxFrancisunoxx

1011

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

I have two INT integer types in my table that set as UNIQUE KEY to avoid duplication. I wanted to rollback any duplicate occur in my stored procedure using EXIT HANDLER.

CREATE TABLE curriculum

(

  id INT PRIMARY KEY AUTO_INCREMENT,

  gradelevel_id INT,

  schoolyear_id INT,

  CONSTRAINT uc_curriculum UNIQUE (gradelevel_id, schoolyear_id)

)



DELIMITER //

CREATE PROCEDURE insertCurriculum(

   IN pIN_gradelevelId INT, 

   IN pIN_schoolyearId INT

)

BEGIN



  DECLARE EXIT HANDLER FOR SQLEXCEPTION

  BEGIN

     ROLLBACK;

        SELECT "Duplicate keys found";

     RESIGNAL;

  END;



  INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (pIN_gradelevelId, pIN_schoolyearId);



END //

DELIMITER ;

I inserted manually in my curriculum table for the test.

INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (1, 1);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1`

FIRST TRY:

Calling my first stored procedure that has duplicate it automatically rollback.

call insertCurriculum(1, 1);

call insertCurriculum(2, 2);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1`

SECOND TRY:

Calling my first stored procedure that has no duplicate it allows the first stored procedure to be executed and insert into my table. Even the second stored procedure has duplicate. I wanted to rollback this. How can I achieve this? Any tips will greatly appreciated!

call insertCurriculum(2, 2);

call insertCurriculum(1, 1);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1

 2           2              2`

mysql

share|improve this question

asked Jun 21 '17 at 4:25

FrancisunoxxFrancisunoxx

1011

CREATE TABLE curriculum

(

  id INT PRIMARY KEY AUTO_INCREMENT,

  gradelevel_id INT,

  schoolyear_id INT,

  CONSTRAINT uc_curriculum UNIQUE (gradelevel_id, schoolyear_id)

)



DELIMITER //

CREATE PROCEDURE insertCurriculum(

   IN pIN_gradelevelId INT, 

   IN pIN_schoolyearId INT

)

BEGIN



  DECLARE EXIT HANDLER FOR SQLEXCEPTION

  BEGIN

     ROLLBACK;

        SELECT "Duplicate keys found";

     RESIGNAL;

  END;



  INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (pIN_gradelevelId, pIN_schoolyearId);



END //

DELIMITER ;

INSERT INTO curriculum (gradelevel_id, schoolyear_id) VALUES (1, 1);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1`

call insertCurriculum(1, 1);

call insertCurriculum(2, 2);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1`

call insertCurriculum(2, 2);

call insertCurriculum(1, 1);



SELECT * FROM curriculum



`id  |  gradelevel_id | schoolyear_id

 1           1              1

 2           2              2`

share|improve this question

asked Jun 21 '17 at 4:25

FrancisunoxxFrancisunoxx

1011

share|improve this question

asked Jun 21 '17 at 4:25

FrancisunoxxFrancisunoxx

1011

asked Jun 21 '17 at 4:25

FrancisunoxxFrancisunoxx

1011

asked Jun 21 '17 at 4:25

FrancisunoxxFrancisunoxx

1011

1 Answer
1

active

oldest

votes

0

Your question is very unclear, but maybe I can still answer it.

Why is it unclear?

What do you mean with duplicates exactly? In your examples there are none. A duplicate relates to rows. You would have a duplicate in your table, if you tried to insert (1, 1) two times.

If you meant with duplicates, that it shouldn't be allowed to have the same value in both columns, then you will have to catch that with a trigger. Create a before insert trigger and have the code something along the line if (new.gradelevel_id = new.schoolyear_id) then signal...; end if;

Anyway...(some more explaining)

In your procedure there won't be any rollback, cause there's nothing to roll back. A rollback statement only makes sense, when your transaction consists of multiple statements. To have multiple statements in a transaction you either have the auto_commit variable set to 0 or you explicitly start a transaction with START TRANSACTION; When you didn't do either, a single statement is also a transaction. When a statement fails, it is undone, you won't see it partially applied on the data. And like I said, the ROLLBACK is meant for the other statements in the transaction that have already executed.

Finally, a hint:

You might also want to have a look at INSERT IGNORE (you have already fulfilled the prerequisite of having a unique index). This way there won't be any errors when duplicates occur and your code keeps running without interruption.

share|improve this answer

answered Jun 21 '17 at 6:51

tombomtombom

2,34511222

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f176841%2frollback-any-duplicate-occured-using-exit-handler%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Your question is very unclear, but maybe I can still answer it.

Why is it unclear?

What do you mean with duplicates exactly? In your examples there are none. A duplicate relates to rows. You would have a duplicate in your table, if you tried to insert (1, 1) two times.

If you meant with duplicates, that it shouldn't be allowed to have the same value in both columns, then you will have to catch that with a trigger. Create a before insert trigger and have the code something along the line if (new.gradelevel_id = new.schoolyear_id) then signal...; end if;

Anyway...(some more explaining)

In your procedure there won't be any rollback, cause there's nothing to roll back. A rollback statement only makes sense, when your transaction consists of multiple statements. To have multiple statements in a transaction you either have the auto_commit variable set to 0 or you explicitly start a transaction with START TRANSACTION; When you didn't do either, a single statement is also a transaction. When a statement fails, it is undone, you won't see it partially applied on the data. And like I said, the ROLLBACK is meant for the other statements in the transaction that have already executed.

Finally, a hint:

You might also want to have a look at INSERT IGNORE (you have already fulfilled the prerequisite of having a unique index). This way there won't be any errors when duplicates occur and your code keeps running without interruption.

share|improve this answer

answered Jun 21 '17 at 6:51

tombomtombom

2,34511222

add a comment | 

0

Your question is very unclear, but maybe I can still answer it.

Why is it unclear?

What do you mean with duplicates exactly? In your examples there are none. A duplicate relates to rows. You would have a duplicate in your table, if you tried to insert (1, 1) two times.

If you meant with duplicates, that it shouldn't be allowed to have the same value in both columns, then you will have to catch that with a trigger. Create a before insert trigger and have the code something along the line if (new.gradelevel_id = new.schoolyear_id) then signal...; end if;

Anyway...(some more explaining)

In your procedure there won't be any rollback, cause there's nothing to roll back. A rollback statement only makes sense, when your transaction consists of multiple statements. To have multiple statements in a transaction you either have the auto_commit variable set to 0 or you explicitly start a transaction with START TRANSACTION; When you didn't do either, a single statement is also a transaction. When a statement fails, it is undone, you won't see it partially applied on the data. And like I said, the ROLLBACK is meant for the other statements in the transaction that have already executed.

Finally, a hint:

You might also want to have a look at INSERT IGNORE (you have already fulfilled the prerequisite of having a unique index). This way there won't be any errors when duplicates occur and your code keeps running without interruption.

share|improve this answer

answered Jun 21 '17 at 6:51

tombomtombom

2,34511222

add a comment | 

Your question is very unclear, but maybe I can still answer it.

Why is it unclear?

What do you mean with duplicates exactly? In your examples there are none. A duplicate relates to rows. You would have a duplicate in your table, if you tried to insert (1, 1) two times.

If you meant with duplicates, that it shouldn't be allowed to have the same value in both columns, then you will have to catch that with a trigger. Create a before insert trigger and have the code something along the line if (new.gradelevel_id = new.schoolyear_id) then signal...; end if;

Anyway...(some more explaining)

In your procedure there won't be any rollback, cause there's nothing to roll back. A rollback statement only makes sense, when your transaction consists of multiple statements. To have multiple statements in a transaction you either have the auto_commit variable set to 0 or you explicitly start a transaction with START TRANSACTION; When you didn't do either, a single statement is also a transaction. When a statement fails, it is undone, you won't see it partially applied on the data. And like I said, the ROLLBACK is meant for the other statements in the transaction that have already executed.

Finally, a hint:

You might also want to have a look at INSERT IGNORE (you have already fulfilled the prerequisite of having a unique index). This way there won't be any errors when duplicates occur and your code keeps running without interruption.

share|improve this answer

answered Jun 21 '17 at 6:51

tombomtombom

2,34511222

share|improve this answer

answered Jun 21 '17 at 6:51

tombomtombom

2,34511222

answered Jun 21 '17 at 6:51

tombomtombom

2,34511222

answered Jun 21 '17 at 6:51

tombomtombom

2,34511222