Is possible to do a ROLLBACK in a MySQL trigger?
Just that is the question: is possible to do a ROLLBACK in a MySQL trigger?
If answer is yes, then, please, explain how.
mysql triggers
asked Jul 9 '11 at 10:03
Ivan
7,459114882
add a comment |
Just that is the question: is possible to do a ROLLBACK in a MySQL trigger?
If answer is yes, then, please, explain how.
mysql triggers
asked Jul 9 '11 at 10:03
Ivan
7,459114882
add a comment |
Just that is the question: is possible to do a ROLLBACK in a MySQL trigger?
If answer is yes, then, please, explain how.
mysql triggers
asked Jul 9 '11 at 10:03
Ivan
7,459114882
Just that is the question: is possible to do a ROLLBACK in a MySQL trigger?
If answer is yes, then, please, explain how.
mysql triggers
mysql triggers
asked Jul 9 '11 at 10:03
Ivan
7,459114882
asked Jul 9 '11 at 10:03
Ivan
7,459114882
asked Jul 9 '11 at 10:03
Ivan
7,459114882
asked Jul 9 '11 at 10:03
Ivan
7,459114882
asked Jul 9 '11 at 10:03
Ivan
7,459114882
7,459114882
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
If the trigger raises an exception, that will abort the transaction, effectively rolling back. Will this work for you?
answered Jul 9 '11 at 10:44
Flimzy
37.2k96496
add a comment |
I've found out that this functionnality exists since MySQL 5.5 and do not works in earlier releases.
The trigger does no rollback or commit.
To initiate any rollback, you have to raise an exception. Thus your insert/update/delete command will abort.
The rollback or commit action has to be raised around your SQL command.
To raise your exception, in your XXX's trigger (eg.) :
create trigger Trigger_XXX_BeforeInsert before insert on XXX
for each row begin
if [Test]
then
SIGNAL sqlstate '45001' set message_text = "No way ! You cannot do this !";
end if ;
end ;
edited Oct 10 '12 at 22:20
Mick MacCallum
112k35255263
answered Jul 20 '12 at 18:31
BartmanDilaw
109110
add a comment |
From: http://dev.mysql.com/doc/refman/5.1/en/trigger-syntax.html
The trigger cannot use statements that
explicitly or implicitly begin or end
a transaction such as START
TRANSACTION, COMMIT, or ROLLBACK.
and
For transactional tables, failure of a
statement should cause rollback of all
changes performed by the statement.
Failure of a trigger causes the
statement to fail, so trigger failure
also causes rollback. For
nontransactional tables, such rollback
cannot be done, so although the
statement fails, any changes performed
prior to the point of the error remain
in effect.
answered Jul 9 '11 at 10:47
Mchl
51.1k792109
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f6634093%2fis-possible-to-do-a-rollback-in-a-mysql-trigger%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
If the trigger raises an exception, that will abort the transaction, effectively rolling back. Will this work for you?
answered Jul 9 '11 at 10:44
Flimzy
37.2k96496
add a comment |
If the trigger raises an exception, that will abort the transaction, effectively rolling back. Will this work for you?
answered Jul 9 '11 at 10:44
Flimzy
37.2k96496
add a comment |
If the trigger raises an exception, that will abort the transaction, effectively rolling back. Will this work for you?
answered Jul 9 '11 at 10:44
Flimzy
37.2k96496
If the trigger raises an exception, that will abort the transaction, effectively rolling back. Will this work for you?
answered Jul 9 '11 at 10:44
Flimzy
37.2k96496
answered Jul 9 '11 at 10:44
Flimzy
37.2k96496
answered Jul 9 '11 at 10:44
Flimzy
37.2k96496
answered Jul 9 '11 at 10:44
Flimzy
37.2k96496
37.2k96496
add a comment |
add a comment |
I've found out that this functionnality exists since MySQL 5.5 and do not works in earlier releases.
The trigger does no rollback or commit.
To initiate any rollback, you have to raise an exception. Thus your insert/update/delete command will abort.
The rollback or commit action has to be raised around your SQL command.
To raise your exception, in your XXX's trigger (eg.) :
create trigger Trigger_XXX_BeforeInsert before insert on XXX
for each row begin
if [Test]
then
SIGNAL sqlstate '45001' set message_text = "No way ! You cannot do this !";
end if ;
end ;
edited Oct 10 '12 at 22:20
Mick MacCallum
112k35255263
answered Jul 20 '12 at 18:31
BartmanDilaw
109110
add a comment |
I've found out that this functionnality exists since MySQL 5.5 and do not works in earlier releases.
The trigger does no rollback or commit.
To initiate any rollback, you have to raise an exception. Thus your insert/update/delete command will abort.
The rollback or commit action has to be raised around your SQL command.
To raise your exception, in your XXX's trigger (eg.) :
create trigger Trigger_XXX_BeforeInsert before insert on XXX
for each row begin
if [Test]
then
SIGNAL sqlstate '45001' set message_text = "No way ! You cannot do this !";
end if ;
end ;
edited Oct 10 '12 at 22:20
Mick MacCallum
112k35255263
answered Jul 20 '12 at 18:31
BartmanDilaw
109110
add a comment |
I've found out that this functionnality exists since MySQL 5.5 and do not works in earlier releases.
The trigger does no rollback or commit.
To initiate any rollback, you have to raise an exception. Thus your insert/update/delete command will abort.
The rollback or commit action has to be raised around your SQL command.
To raise your exception, in your XXX's trigger (eg.) :
create trigger Trigger_XXX_BeforeInsert before insert on XXX
for each row begin
if [Test]
then
SIGNAL sqlstate '45001' set message_text = "No way ! You cannot do this !";
end if ;
end ;
edited Oct 10 '12 at 22:20
Mick MacCallum
112k35255263
answered Jul 20 '12 at 18:31
BartmanDilaw
109110
I've found out that this functionnality exists since MySQL 5.5 and do not works in earlier releases.
The trigger does no rollback or commit.
To initiate any rollback, you have to raise an exception. Thus your insert/update/delete command will abort.
The rollback or commit action has to be raised around your SQL command.
To raise your exception, in your XXX's trigger (eg.) :
create trigger Trigger_XXX_BeforeInsert before insert on XXX
for each row begin
if [Test]
then
SIGNAL sqlstate '45001' set message_text = "No way ! You cannot do this !";
end if ;
end ;
edited Oct 10 '12 at 22:20
Mick MacCallum
112k35255263
answered Jul 20 '12 at 18:31
BartmanDilaw
109110
edited Oct 10 '12 at 22:20
Mick MacCallum
112k35255263
edited Oct 10 '12 at 22:20
Mick MacCallum
112k35255263
edited Oct 10 '12 at 22:20
Mick MacCallum
112k35255263
112k35255263
answered Jul 20 '12 at 18:31
BartmanDilaw
109110
answered Jul 20 '12 at 18:31
BartmanDilaw
109110
answered Jul 20 '12 at 18:31
BartmanDilaw
109110
109110
add a comment |
add a comment |
From: http://dev.mysql.com/doc/refman/5.1/en/trigger-syntax.html
The trigger cannot use statements that
explicitly or implicitly begin or end
a transaction such as START
TRANSACTION, COMMIT, or ROLLBACK.
and
For transactional tables, failure of a
statement should cause rollback of all
changes performed by the statement.
Failure of a trigger causes the
statement to fail, so trigger failure
also causes rollback. For
nontransactional tables, such rollback
cannot be done, so although the
statement fails, any changes performed
prior to the point of the error remain
in effect.
answered Jul 9 '11 at 10:47
Mchl
51.1k792109
add a comment |
From: http://dev.mysql.com/doc/refman/5.1/en/trigger-syntax.html
The trigger cannot use statements that
explicitly or implicitly begin or end
a transaction such as START
TRANSACTION, COMMIT, or ROLLBACK.
and
For transactional tables, failure of a
statement should cause rollback of all
changes performed by the statement.
Failure of a trigger causes the
statement to fail, so trigger failure
also causes rollback. For
nontransactional tables, such rollback
cannot be done, so although the
statement fails, any changes performed
prior to the point of the error remain
in effect.
answered Jul 9 '11 at 10:47
Mchl
51.1k792109
add a comment |
From: http://dev.mysql.com/doc/refman/5.1/en/trigger-syntax.html
The trigger cannot use statements that
explicitly or implicitly begin or end
a transaction such as START
TRANSACTION, COMMIT, or ROLLBACK.
and
For transactional tables, failure of a
statement should cause rollback of all
changes performed by the statement.
Failure of a trigger causes the
statement to fail, so trigger failure
also causes rollback. For
nontransactional tables, such rollback
cannot be done, so although the
statement fails, any changes performed
prior to the point of the error remain
in effect.
answered Jul 9 '11 at 10:47
Mchl
51.1k792109
From: http://dev.mysql.com/doc/refman/5.1/en/trigger-syntax.html
The trigger cannot use statements that
explicitly or implicitly begin or end
a transaction such as START
TRANSACTION, COMMIT, or ROLLBACK.
and
For transactional tables, failure of a
statement should cause rollback of all
changes performed by the statement.
Failure of a trigger causes the
statement to fail, so trigger failure
also causes rollback. For
nontransactional tables, such rollback
cannot be done, so although the
statement fails, any changes performed
prior to the point of the error remain
in effect.
answered Jul 9 '11 at 10:47
Mchl
51.1k792109
answered Jul 9 '11 at 10:47
Mchl
51.1k792109
answered Jul 9 '11 at 10:47
Mchl
51.1k792109
answered Jul 9 '11 at 10:47
Mchl
51.1k792109
51.1k792109
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f6634093%2fis-possible-to-do-a-rollback-in-a-mysql-trigger%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
