How to decrement a value in another table via a trigger? (MySQL)
I want my trigger to decrement the quantity by 1 in the table "quantity" when a new row is added to "rented_equipment_log" where date_returned has a value of NULL.
A basic synopsis of the database is that there is an equipment table with columns; model, maker, type and quantity. (E.g. 1001, 'Jackson', 'oar', 10)
The table rented_equipment_log has the columns; member_id, model, type, date_taken, date_returned. (E.g. 17225663, 1001, oar, 2018-11-26, 2018-11-27)
So when a member takes out a piece of equipment but has not yet returned it (date_returned is null), the quantity of the same model decrements by 1 in the table equipment.
However I'm getting a syntax error. I've looked at other questions similar to this and still can't figure out what the error is.
Here's the trigger:
delimiter //
CREATE TRIGGER UpdateQuantity
AFTER INSERT ON rented_equipment_log
FOR EACH ROW BEGIN
DECLARE q integer;
SELECT quantity INTO q FROM equipment WHERE model = NEW.model;
IF (date_returned IS NULL) THEN
UPDATE equipment
SET quantity = q -1 WHERE model = NEW.model
END IF;
END;//
And here's the error:
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near
'END IF;
END' at line 9
mysql sql syntax triggers
asked Nov 26 '18 at 12:23
rookiecookierookiecookie
155
add a comment |
I want my trigger to decrement the quantity by 1 in the table "quantity" when a new row is added to "rented_equipment_log" where date_returned has a value of NULL.
A basic synopsis of the database is that there is an equipment table with columns; model, maker, type and quantity. (E.g. 1001, 'Jackson', 'oar', 10)
The table rented_equipment_log has the columns; member_id, model, type, date_taken, date_returned. (E.g. 17225663, 1001, oar, 2018-11-26, 2018-11-27)
So when a member takes out a piece of equipment but has not yet returned it (date_returned is null), the quantity of the same model decrements by 1 in the table equipment.
However I'm getting a syntax error. I've looked at other questions similar to this and still can't figure out what the error is.
Here's the trigger:
delimiter //
CREATE TRIGGER UpdateQuantity
AFTER INSERT ON rented_equipment_log
FOR EACH ROW BEGIN
DECLARE q integer;
SELECT quantity INTO q FROM equipment WHERE model = NEW.model;
IF (date_returned IS NULL) THEN
UPDATE equipment
SET quantity = q -1 WHERE model = NEW.model
END IF;
END;//
And here's the error:
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near
'END IF;
END' at line 9
mysql sql syntax triggers
asked Nov 26 '18 at 12:23
rookiecookierookiecookie
155
2
A;afternew.model?
– Salman A
Nov 26 '18 at 12:29
add a comment |
I want my trigger to decrement the quantity by 1 in the table "quantity" when a new row is added to "rented_equipment_log" where date_returned has a value of NULL.
A basic synopsis of the database is that there is an equipment table with columns; model, maker, type and quantity. (E.g. 1001, 'Jackson', 'oar', 10)
The table rented_equipment_log has the columns; member_id, model, type, date_taken, date_returned. (E.g. 17225663, 1001, oar, 2018-11-26, 2018-11-27)
So when a member takes out a piece of equipment but has not yet returned it (date_returned is null), the quantity of the same model decrements by 1 in the table equipment.
However I'm getting a syntax error. I've looked at other questions similar to this and still can't figure out what the error is.
Here's the trigger:
delimiter //
CREATE TRIGGER UpdateQuantity
AFTER INSERT ON rented_equipment_log
FOR EACH ROW BEGIN
DECLARE q integer;
SELECT quantity INTO q FROM equipment WHERE model = NEW.model;
IF (date_returned IS NULL) THEN
UPDATE equipment
SET quantity = q -1 WHERE model = NEW.model
END IF;
END;//
And here's the error:
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near
'END IF;
END' at line 9
mysql sql syntax triggers
asked Nov 26 '18 at 12:23
rookiecookierookiecookie
155
I want my trigger to decrement the quantity by 1 in the table "quantity" when a new row is added to "rented_equipment_log" where date_returned has a value of NULL.
A basic synopsis of the database is that there is an equipment table with columns; model, maker, type and quantity. (E.g. 1001, 'Jackson', 'oar', 10)
The table rented_equipment_log has the columns; member_id, model, type, date_taken, date_returned. (E.g. 17225663, 1001, oar, 2018-11-26, 2018-11-27)
So when a member takes out a piece of equipment but has not yet returned it (date_returned is null), the quantity of the same model decrements by 1 in the table equipment.
However I'm getting a syntax error. I've looked at other questions similar to this and still can't figure out what the error is.
Here's the trigger:
delimiter //
CREATE TRIGGER UpdateQuantity
AFTER INSERT ON rented_equipment_log
FOR EACH ROW BEGIN
DECLARE q integer;
SELECT quantity INTO q FROM equipment WHERE model = NEW.model;
IF (date_returned IS NULL) THEN
UPDATE equipment
SET quantity = q -1 WHERE model = NEW.model
END IF;
END;//
And here's the error:
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near
'END IF;
END' at line 9
mysql sql syntax triggers
mysql sql syntax triggers
asked Nov 26 '18 at 12:23
rookiecookierookiecookie
155
asked Nov 26 '18 at 12:23
rookiecookierookiecookie
155
asked Nov 26 '18 at 12:23
rookiecookierookiecookie
155
asked Nov 26 '18 at 12:23
rookiecookierookiecookie
155
asked Nov 26 '18 at 12:23
rookiecookierookiecookie
155
155
2
A;afternew.model?
– Salman A
Nov 26 '18 at 12:29
add a comment |
2
A;afternew.model?
– Salman A
Nov 26 '18 at 12:29
2
2
A
; after new.model?– Salman A
Nov 26 '18 at 12:29
A
; after new.model?– Salman A
Nov 26 '18 at 12:29
add a comment |
1 Answer
1
active
oldest
votes
I'm not sure why you are getting an error there. But your query is more complicated than necessary:
delimiter //
CREATE TRIGGER UpdateQuantity
AFTER INSERT ON rented_equipment_log
FOR EACH ROW
BEGIN
UPDATE equipment e
SET e.quantity = e.quantity - 1
WHERE e.model = NEW.model and ?.date_returned IS NULL;
END;//
I'm not sure if date_returned should be new.date_returned or e.date_returned. I don't even understand that condition. I would be expecting e.equipment > 0.
answered Nov 26 '18 at 12:26
Gordon LinoffGordon Linoff
792k36316419
I believe also you need to add "AND date_returned IS NULL"
– Zeki Gumus
Nov 26 '18 at 12:31
@ZekiGumus . . . Thank you.
– Gordon Linoff
Nov 26 '18 at 12:35
Hi, firstly thanks for the solution. After trying this I realised what was wrong. Basically in my procedure to add a new row to "rented_equipment_log" I didn't have "date_returned" as a parameter - so when I tried this trigger it gave me an error saying that there was an "unknown column" (date_returned) in the where clause. I removed it from your trigger and now everything is working smoothly. We don't need "date_returned" in the 'AddLoan' procedure as the person has not returned it yet. Thank you for your help!
– rookiecookie
Nov 26 '18 at 13:11
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%2f53481059%2fhow-to-decrement-a-value-in-another-table-via-a-trigger-mysql%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
I'm not sure why you are getting an error there. But your query is more complicated than necessary:
delimiter //
CREATE TRIGGER UpdateQuantity
AFTER INSERT ON rented_equipment_log
FOR EACH ROW
BEGIN
UPDATE equipment e
SET e.quantity = e.quantity - 1
WHERE e.model = NEW.model and ?.date_returned IS NULL;
END;//
I'm not sure if date_returned should be new.date_returned or e.date_returned. I don't even understand that condition. I would be expecting e.equipment > 0.
answered Nov 26 '18 at 12:26
Gordon LinoffGordon Linoff
792k36316419
I believe also you need to add "AND date_returned IS NULL"
– Zeki Gumus
Nov 26 '18 at 12:31
@ZekiGumus . . . Thank you.
– Gordon Linoff
Nov 26 '18 at 12:35
Hi, firstly thanks for the solution. After trying this I realised what was wrong. Basically in my procedure to add a new row to "rented_equipment_log" I didn't have "date_returned" as a parameter - so when I tried this trigger it gave me an error saying that there was an "unknown column" (date_returned) in the where clause. I removed it from your trigger and now everything is working smoothly. We don't need "date_returned" in the 'AddLoan' procedure as the person has not returned it yet. Thank you for your help!
– rookiecookie
Nov 26 '18 at 13:11
add a comment |
I'm not sure why you are getting an error there. But your query is more complicated than necessary:
delimiter //
CREATE TRIGGER UpdateQuantity
AFTER INSERT ON rented_equipment_log
FOR EACH ROW
BEGIN
UPDATE equipment e
SET e.quantity = e.quantity - 1
WHERE e.model = NEW.model and ?.date_returned IS NULL;
END;//
I'm not sure if date_returned should be new.date_returned or e.date_returned. I don't even understand that condition. I would be expecting e.equipment > 0.
answered Nov 26 '18 at 12:26
Gordon LinoffGordon Linoff
792k36316419
I believe also you need to add "AND date_returned IS NULL"
– Zeki Gumus
Nov 26 '18 at 12:31
@ZekiGumus . . . Thank you.
– Gordon Linoff
Nov 26 '18 at 12:35
Hi, firstly thanks for the solution. After trying this I realised what was wrong. Basically in my procedure to add a new row to "rented_equipment_log" I didn't have "date_returned" as a parameter - so when I tried this trigger it gave me an error saying that there was an "unknown column" (date_returned) in the where clause. I removed it from your trigger and now everything is working smoothly. We don't need "date_returned" in the 'AddLoan' procedure as the person has not returned it yet. Thank you for your help!
– rookiecookie
Nov 26 '18 at 13:11
add a comment |
I'm not sure why you are getting an error there. But your query is more complicated than necessary:
delimiter //
CREATE TRIGGER UpdateQuantity
AFTER INSERT ON rented_equipment_log
FOR EACH ROW
BEGIN
UPDATE equipment e
SET e.quantity = e.quantity - 1
WHERE e.model = NEW.model and ?.date_returned IS NULL;
END;//
I'm not sure if date_returned should be new.date_returned or e.date_returned. I don't even understand that condition. I would be expecting e.equipment > 0.
answered Nov 26 '18 at 12:26
Gordon LinoffGordon Linoff
792k36316419
I'm not sure why you are getting an error there. But your query is more complicated than necessary:
delimiter //
CREATE TRIGGER UpdateQuantity
AFTER INSERT ON rented_equipment_log
FOR EACH ROW
BEGIN
UPDATE equipment e
SET e.quantity = e.quantity - 1
WHERE e.model = NEW.model and ?.date_returned IS NULL;
END;//
I'm not sure if date_returned should be new.date_returned or e.date_returned. I don't even understand that condition. I would be expecting e.equipment > 0.
answered Nov 26 '18 at 12:26
Gordon LinoffGordon Linoff
792k36316419
edited Nov 26 '18 at 12:35
answered Nov 26 '18 at 12:26
Gordon LinoffGordon Linoff
792k36316419
answered Nov 26 '18 at 12:26
Gordon LinoffGordon Linoff
792k36316419
answered Nov 26 '18 at 12:26
Gordon LinoffGordon Linoff
792k36316419
792k36316419
I believe also you need to add "AND date_returned IS NULL"
– Zeki Gumus
Nov 26 '18 at 12:31
@ZekiGumus . . . Thank you.
– Gordon Linoff
Nov 26 '18 at 12:35
Hi, firstly thanks for the solution. After trying this I realised what was wrong. Basically in my procedure to add a new row to "rented_equipment_log" I didn't have "date_returned" as a parameter - so when I tried this trigger it gave me an error saying that there was an "unknown column" (date_returned) in the where clause. I removed it from your trigger and now everything is working smoothly. We don't need "date_returned" in the 'AddLoan' procedure as the person has not returned it yet. Thank you for your help!
– rookiecookie
Nov 26 '18 at 13:11
add a comment |
I believe also you need to add "AND date_returned IS NULL"
– Zeki Gumus
Nov 26 '18 at 12:31
@ZekiGumus . . . Thank you.
– Gordon Linoff
Nov 26 '18 at 12:35
Hi, firstly thanks for the solution. After trying this I realised what was wrong. Basically in my procedure to add a new row to "rented_equipment_log" I didn't have "date_returned" as a parameter - so when I tried this trigger it gave me an error saying that there was an "unknown column" (date_returned) in the where clause. I removed it from your trigger and now everything is working smoothly. We don't need "date_returned" in the 'AddLoan' procedure as the person has not returned it yet. Thank you for your help!
– rookiecookie
Nov 26 '18 at 13:11
I believe also you need to add "AND date_returned IS NULL"
– Zeki Gumus
Nov 26 '18 at 12:31
I believe also you need to add "AND date_returned IS NULL"
– Zeki Gumus
Nov 26 '18 at 12:31
@ZekiGumus . . . Thank you.
– Gordon Linoff
Nov 26 '18 at 12:35
@ZekiGumus . . . Thank you.
– Gordon Linoff
Nov 26 '18 at 12:35
Hi, firstly thanks for the solution. After trying this I realised what was wrong. Basically in my procedure to add a new row to "rented_equipment_log" I didn't have "date_returned" as a parameter - so when I tried this trigger it gave me an error saying that there was an "unknown column" (date_returned) in the where clause. I removed it from your trigger and now everything is working smoothly. We don't need "date_returned" in the 'AddLoan' procedure as the person has not returned it yet. Thank you for your help!
– rookiecookie
Nov 26 '18 at 13:11
Hi, firstly thanks for the solution. After trying this I realised what was wrong. Basically in my procedure to add a new row to "rented_equipment_log" I didn't have "date_returned" as a parameter - so when I tried this trigger it gave me an error saying that there was an "unknown column" (date_returned) in the where clause. I removed it from your trigger and now everything is working smoothly. We don't need "date_returned" in the 'AddLoan' procedure as the person has not returned it yet. Thank you for your help!
– rookiecookie
Nov 26 '18 at 13:11
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.
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%2f53481059%2fhow-to-decrement-a-value-in-another-table-via-a-trigger-mysql%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
2
A
;afternew.model?– Salman A
Nov 26 '18 at 12:29