MYSQL trigger with update
up vote
-1
down vote
favorite
I am trying to build a trigger to update a table field by referring to another table, after the same table has been updated. I have tried the following pieces of code but keep getting error #1064 for every attempt. Could someone suggest what is wrong.
quote
attempt 1.
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
===end attempt 1
attempt 2
BEGIN
UPDATE
a
SET
a.earned =
CASE WHEN a.incactualn < b.n1 THEN '0'
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
FROM
incent a
INNER JOIN
inc_prog b
ON
a.Incent_name = b.incname && a.period = b.finyear
END
======= end attempt 2
UNQUOTE
mysql triggers
asked Nov 12 at 4:21
V. Rangan
31
add a comment |
up vote
-1
down vote
favorite
I am trying to build a trigger to update a table field by referring to another table, after the same table has been updated. I have tried the following pieces of code but keep getting error #1064 for every attempt. Could someone suggest what is wrong.
quote
attempt 1.
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
===end attempt 1
attempt 2
BEGIN
UPDATE
a
SET
a.earned =
CASE WHEN a.incactualn < b.n1 THEN '0'
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
FROM
incent a
INNER JOIN
inc_prog b
ON
a.Incent_name = b.incname && a.period = b.finyear
END
======= end attempt 2
UNQUOTE
mysql triggers
asked Nov 12 at 4:21
V. Rangan
31
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
I am trying to build a trigger to update a table field by referring to another table, after the same table has been updated. I have tried the following pieces of code but keep getting error #1064 for every attempt. Could someone suggest what is wrong.
quote
attempt 1.
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
===end attempt 1
attempt 2
BEGIN
UPDATE
a
SET
a.earned =
CASE WHEN a.incactualn < b.n1 THEN '0'
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
FROM
incent a
INNER JOIN
inc_prog b
ON
a.Incent_name = b.incname && a.period = b.finyear
END
======= end attempt 2
UNQUOTE
mysql triggers
asked Nov 12 at 4:21
V. Rangan
31
I am trying to build a trigger to update a table field by referring to another table, after the same table has been updated. I have tried the following pieces of code but keep getting error #1064 for every attempt. Could someone suggest what is wrong.
quote
attempt 1.
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
===end attempt 1
attempt 2
BEGIN
UPDATE
a
SET
a.earned =
CASE WHEN a.incactualn < b.n1 THEN '0'
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
FROM
incent a
INNER JOIN
inc_prog b
ON
a.Incent_name = b.incname && a.period = b.finyear
END
======= end attempt 2
UNQUOTE
mysql triggers
mysql triggers
asked Nov 12 at 4:21
V. Rangan
31
asked Nov 12 at 4:21
V. Rangan
31
asked Nov 12 at 4:21
V. Rangan
31
asked Nov 12 at 4:21
V. Rangan
31
asked Nov 12 at 4:21
V. Rangan
31
31
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
You only put the word CASE at the beginning of a CASE expression. Just delete the second and subsequent copies. So your first trigger becomes:
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END;
END
In your second attempt you are missing the END at the end of the CASE expression, as well as the (SELECT and ) around the subquery:
BEGIN
UPDATE a
SET a.earned = (SELECT
CASE WHEN a.incactualn < b.n1 THEN '0'
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
FROM incent a
INNER JOIN inc_prog b ON a.Incent_name = b.incname && a.period = b.finyear);
END
answered Nov 12 at 4:31
Nick
21.9k71435
Thanks, Nick. Appears to have worked. Delighted.
– V. Rangan
Nov 12 at 4:43
@V.Rangan if it did solve your problem please consider marking the answer accepted (the check mark under the up/down vote arrows next to the answer). See stackoverflow.com/help/someone-answers
– Nick
Nov 12 at 5:11
I am getting the error "Can't update table 'incent' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." when this trigger fires, as the same table is being updated. Would you suggest creating a temporary table to overcome this ?
– V. Rangan
Nov 12 at 7:14
@V.Rangan are you trying to use this trigger on an UPDATE on tableincent?
– Nick
Nov 12 at 7:56
Yes. I was. By using the trigger you corrected. Then saw the glitch. Worked around the same by updating the field during the first update of the same table. Thanks anyway. You helped me learn a couple.
– V. Rangan
Nov 14 at 5:40
|
show 1 more 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',
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53255931%2fmysql-trigger-with-update%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
You only put the word CASE at the beginning of a CASE expression. Just delete the second and subsequent copies. So your first trigger becomes:
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END;
END
In your second attempt you are missing the END at the end of the CASE expression, as well as the (SELECT and ) around the subquery:
BEGIN
UPDATE a
SET a.earned = (SELECT
CASE WHEN a.incactualn < b.n1 THEN '0'
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
FROM incent a
INNER JOIN inc_prog b ON a.Incent_name = b.incname && a.period = b.finyear);
END
answered Nov 12 at 4:31
Nick
21.9k71435
Thanks, Nick. Appears to have worked. Delighted.
– V. Rangan
Nov 12 at 4:43
@V.Rangan if it did solve your problem please consider marking the answer accepted (the check mark under the up/down vote arrows next to the answer). See stackoverflow.com/help/someone-answers
– Nick
Nov 12 at 5:11
I am getting the error "Can't update table 'incent' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." when this trigger fires, as the same table is being updated. Would you suggest creating a temporary table to overcome this ?
– V. Rangan
Nov 12 at 7:14
@V.Rangan are you trying to use this trigger on an UPDATE on tableincent?
– Nick
Nov 12 at 7:56
Yes. I was. By using the trigger you corrected. Then saw the glitch. Worked around the same by updating the field during the first update of the same table. Thanks anyway. You helped me learn a couple.
– V. Rangan
Nov 14 at 5:40
|
show 1 more comment
up vote
0
down vote
accepted
You only put the word CASE at the beginning of a CASE expression. Just delete the second and subsequent copies. So your first trigger becomes:
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END;
END
In your second attempt you are missing the END at the end of the CASE expression, as well as the (SELECT and ) around the subquery:
BEGIN
UPDATE a
SET a.earned = (SELECT
CASE WHEN a.incactualn < b.n1 THEN '0'
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
FROM incent a
INNER JOIN inc_prog b ON a.Incent_name = b.incname && a.period = b.finyear);
END
answered Nov 12 at 4:31
Nick
21.9k71435
Thanks, Nick. Appears to have worked. Delighted.
– V. Rangan
Nov 12 at 4:43
@V.Rangan if it did solve your problem please consider marking the answer accepted (the check mark under the up/down vote arrows next to the answer). See stackoverflow.com/help/someone-answers
– Nick
Nov 12 at 5:11
I am getting the error "Can't update table 'incent' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." when this trigger fires, as the same table is being updated. Would you suggest creating a temporary table to overcome this ?
– V. Rangan
Nov 12 at 7:14
@V.Rangan are you trying to use this trigger on an UPDATE on tableincent?
– Nick
Nov 12 at 7:56
Yes. I was. By using the trigger you corrected. Then saw the glitch. Worked around the same by updating the field during the first update of the same table. Thanks anyway. You helped me learn a couple.
– V. Rangan
Nov 14 at 5:40
|
show 1 more comment
up vote
0
down vote
accepted
up vote
0
down vote
accepted
You only put the word CASE at the beginning of a CASE expression. Just delete the second and subsequent copies. So your first trigger becomes:
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END;
END
In your second attempt you are missing the END at the end of the CASE expression, as well as the (SELECT and ) around the subquery:
BEGIN
UPDATE a
SET a.earned = (SELECT
CASE WHEN a.incactualn < b.n1 THEN '0'
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
FROM incent a
INNER JOIN inc_prog b ON a.Incent_name = b.incname && a.period = b.finyear);
END
answered Nov 12 at 4:31
Nick
21.9k71435
You only put the word CASE at the beginning of a CASE expression. Just delete the second and subsequent copies. So your first trigger becomes:
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END;
END
In your second attempt you are missing the END at the end of the CASE expression, as well as the (SELECT and ) around the subquery:
BEGIN
UPDATE a
SET a.earned = (SELECT
CASE WHEN a.incactualn < b.n1 THEN '0'
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
FROM incent a
INNER JOIN inc_prog b ON a.Incent_name = b.incname && a.period = b.finyear);
END
answered Nov 12 at 4:31
Nick
21.9k71435
answered Nov 12 at 4:31
Nick
21.9k71435
answered Nov 12 at 4:31
Nick
21.9k71435
answered Nov 12 at 4:31
Nick
21.9k71435
21.9k71435
Thanks, Nick. Appears to have worked. Delighted.
– V. Rangan
Nov 12 at 4:43
@V.Rangan if it did solve your problem please consider marking the answer accepted (the check mark under the up/down vote arrows next to the answer). See stackoverflow.com/help/someone-answers
– Nick
Nov 12 at 5:11
I am getting the error "Can't update table 'incent' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." when this trigger fires, as the same table is being updated. Would you suggest creating a temporary table to overcome this ?
– V. Rangan
Nov 12 at 7:14
@V.Rangan are you trying to use this trigger on an UPDATE on tableincent?
– Nick
Nov 12 at 7:56
Yes. I was. By using the trigger you corrected. Then saw the glitch. Worked around the same by updating the field during the first update of the same table. Thanks anyway. You helped me learn a couple.
– V. Rangan
Nov 14 at 5:40
|
show 1 more comment
Thanks, Nick. Appears to have worked. Delighted.
– V. Rangan
Nov 12 at 4:43
@V.Rangan if it did solve your problem please consider marking the answer accepted (the check mark under the up/down vote arrows next to the answer). See stackoverflow.com/help/someone-answers
– Nick
Nov 12 at 5:11
I am getting the error "Can't update table 'incent' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." when this trigger fires, as the same table is being updated. Would you suggest creating a temporary table to overcome this ?
– V. Rangan
Nov 12 at 7:14
@V.Rangan are you trying to use this trigger on an UPDATE on tableincent?
– Nick
Nov 12 at 7:56
Yes. I was. By using the trigger you corrected. Then saw the glitch. Worked around the same by updating the field during the first update of the same table. Thanks anyway. You helped me learn a couple.
– V. Rangan
Nov 14 at 5:40
Thanks, Nick. Appears to have worked. Delighted.
– V. Rangan
Nov 12 at 4:43
Thanks, Nick. Appears to have worked. Delighted.
– V. Rangan
Nov 12 at 4:43
@V.Rangan if it did solve your problem please consider marking the answer accepted (the check mark under the up/down vote arrows next to the answer). See stackoverflow.com/help/someone-answers
– Nick
Nov 12 at 5:11
@V.Rangan if it did solve your problem please consider marking the answer accepted (the check mark under the up/down vote arrows next to the answer). See stackoverflow.com/help/someone-answers
– Nick
Nov 12 at 5:11
I am getting the error "Can't update table 'incent' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." when this trigger fires, as the same table is being updated. Would you suggest creating a temporary table to overcome this ?
– V. Rangan
Nov 12 at 7:14
I am getting the error "Can't update table 'incent' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." when this trigger fires, as the same table is being updated. Would you suggest creating a temporary table to overcome this ?
– V. Rangan
Nov 12 at 7:14
@V.Rangan are you trying to use this trigger on an UPDATE on table
incent?– Nick
Nov 12 at 7:56
@V.Rangan are you trying to use this trigger on an UPDATE on table
incent?– Nick
Nov 12 at 7:56
Yes. I was. By using the trigger you corrected. Then saw the glitch. Worked around the same by updating the field during the first update of the same table. Thanks anyway. You helped me learn a couple.
– V. Rangan
Nov 14 at 5:40
Yes. I was. By using the trigger you corrected. Then saw the glitch. Worked around the same by updating the field during the first update of the same table. Thanks anyway. You helped me learn a couple.
– V. Rangan
Nov 14 at 5:40
|
show 1 more comment
draft saved
draft discarded
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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53255931%2fmysql-trigger-with-update%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
