MySQL: User-defined functions for triggers
0
I need to write a difficult trigger to enforce a strange pattern in my dataset, so I decided to split the work into several functions. I am however getting several errors that I don't understand:
Error Code: 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 '`Product Name`;
Error Code: 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 'STRING BEGIN SET @temp_query = (SELECT s1.`Product Name` FROM Sells s1 WHE'
My code:
/* DEFINE VARIABLES */
SET @above_beer_name = null;
SET @below_beer_name = null;
SET @below_beer_average = null;
SET @above_beer_average = null;
SET @average_below_above = null;
SET @temp_query = null;
/* Function gets one beer higher than the input Sells entry in price*/
DELIMITER $$
CREATE FUNCTION getAboveBeer() RETURNS STRING
BEGIN
SET @temp_query = (SELECT s1.`Product Name`
FROM Sells s1
WHERE s1.`Bar Name` = NEW.`Bar Name`
AND NEW.`Price` <= s1.`Price`
ORDER BY `Price` ASC LIMIT 1);
SET @above_beer_name = @temp_query.`Product Name`;
RETURN @above_beer_name;
END$$
/* Function gets one beer lower than the input Sells entry in price*/
DELIMITER $$
CREATE FUNCTION getBelowBeer() RETURNS STRING
BEGIN
SET @temp_query = (SELECT Sells.`Product Name` FROM Sells
WHERE Sells.`Bar Name` = NEW.`Bar Name`
AND NEW.`Price` >= s1.`Price`
ORDER BY ASC LIMIT 1);
SET @below_beer_name = @temp_query.`Product Name`;
RETURN @below_beer_name;
END $$;
mysql sql triggers innodb
edited Nov 15 '18 at 20:49
Rick James
69.2k561101
asked Nov 15 '18 at 6:25
Leo ScaranoLeo Scarano
418
add a comment |
0
I need to write a difficult trigger to enforce a strange pattern in my dataset, so I decided to split the work into several functions. I am however getting several errors that I don't understand:
Error Code: 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 '`Product Name`;
Error Code: 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 'STRING BEGIN SET @temp_query = (SELECT s1.`Product Name` FROM Sells s1 WHE'
My code:
/* DEFINE VARIABLES */
SET @above_beer_name = null;
SET @below_beer_name = null;
SET @below_beer_average = null;
SET @above_beer_average = null;
SET @average_below_above = null;
SET @temp_query = null;
/* Function gets one beer higher than the input Sells entry in price*/
DELIMITER $$
CREATE FUNCTION getAboveBeer() RETURNS STRING
BEGIN
SET @temp_query = (SELECT s1.`Product Name`
FROM Sells s1
WHERE s1.`Bar Name` = NEW.`Bar Name`
AND NEW.`Price` <= s1.`Price`
ORDER BY `Price` ASC LIMIT 1);
SET @above_beer_name = @temp_query.`Product Name`;
RETURN @above_beer_name;
END$$
/* Function gets one beer lower than the input Sells entry in price*/
DELIMITER $$
CREATE FUNCTION getBelowBeer() RETURNS STRING
BEGIN
SET @temp_query = (SELECT Sells.`Product Name` FROM Sells
WHERE Sells.`Bar Name` = NEW.`Bar Name`
AND NEW.`Price` >= s1.`Price`
ORDER BY ASC LIMIT 1);
SET @below_beer_name = @temp_query.`Product Name`;
RETURN @below_beer_name;
END $$;
mysql sql triggers innodb
edited Nov 15 '18 at 20:49
Rick James
69.2k561101
asked Nov 15 '18 at 6:25
Leo ScaranoLeo Scarano
418
A couple of obvious errors - there's not such data type as string and the order by in the second function is not ordering by anything. A less obvious error is SET @below_beer_name = @temp_query.Product Name; the syntactically correct version would be SET @below_beer_name = @temp_query since @temp_query is not a table - but maybe you want it to be?
– P.Salmon
Nov 15 '18 at 7:48
If you are looking to declare variables, to be using inside a stored function. you should useDECLAREsyntax, and it should be done inside the stored function definition instead.
– Madhur Bhaiya
Nov 15 '18 at 12:49
add a comment |
0
0
0
I need to write a difficult trigger to enforce a strange pattern in my dataset, so I decided to split the work into several functions. I am however getting several errors that I don't understand:
Error Code: 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 '`Product Name`;
Error Code: 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 'STRING BEGIN SET @temp_query = (SELECT s1.`Product Name` FROM Sells s1 WHE'
My code:
/* DEFINE VARIABLES */
SET @above_beer_name = null;
SET @below_beer_name = null;
SET @below_beer_average = null;
SET @above_beer_average = null;
SET @average_below_above = null;
SET @temp_query = null;
/* Function gets one beer higher than the input Sells entry in price*/
DELIMITER $$
CREATE FUNCTION getAboveBeer() RETURNS STRING
BEGIN
SET @temp_query = (SELECT s1.`Product Name`
FROM Sells s1
WHERE s1.`Bar Name` = NEW.`Bar Name`
AND NEW.`Price` <= s1.`Price`
ORDER BY `Price` ASC LIMIT 1);
SET @above_beer_name = @temp_query.`Product Name`;
RETURN @above_beer_name;
END$$
/* Function gets one beer lower than the input Sells entry in price*/
DELIMITER $$
CREATE FUNCTION getBelowBeer() RETURNS STRING
BEGIN
SET @temp_query = (SELECT Sells.`Product Name` FROM Sells
WHERE Sells.`Bar Name` = NEW.`Bar Name`
AND NEW.`Price` >= s1.`Price`
ORDER BY ASC LIMIT 1);
SET @below_beer_name = @temp_query.`Product Name`;
RETURN @below_beer_name;
END $$;
mysql sql triggers innodb
edited Nov 15 '18 at 20:49
Rick James
69.2k561101
asked Nov 15 '18 at 6:25
Leo ScaranoLeo Scarano
418
I need to write a difficult trigger to enforce a strange pattern in my dataset, so I decided to split the work into several functions. I am however getting several errors that I don't understand:
Error Code: 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 '`Product Name`;
Error Code: 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 'STRING BEGIN SET @temp_query = (SELECT s1.`Product Name` FROM Sells s1 WHE'
My code:
/* DEFINE VARIABLES */
SET @above_beer_name = null;
SET @below_beer_name = null;
SET @below_beer_average = null;
SET @above_beer_average = null;
SET @average_below_above = null;
SET @temp_query = null;
/* Function gets one beer higher than the input Sells entry in price*/
DELIMITER $$
CREATE FUNCTION getAboveBeer() RETURNS STRING
BEGIN
SET @temp_query = (SELECT s1.`Product Name`
FROM Sells s1
WHERE s1.`Bar Name` = NEW.`Bar Name`
AND NEW.`Price` <= s1.`Price`
ORDER BY `Price` ASC LIMIT 1);
SET @above_beer_name = @temp_query.`Product Name`;
RETURN @above_beer_name;
END$$
/* Function gets one beer lower than the input Sells entry in price*/
DELIMITER $$
CREATE FUNCTION getBelowBeer() RETURNS STRING
BEGIN
SET @temp_query = (SELECT Sells.`Product Name` FROM Sells
WHERE Sells.`Bar Name` = NEW.`Bar Name`
AND NEW.`Price` >= s1.`Price`
ORDER BY ASC LIMIT 1);
SET @below_beer_name = @temp_query.`Product Name`;
RETURN @below_beer_name;
END $$;
mysql sql triggers innodb
mysql sql triggers innodb
edited Nov 15 '18 at 20:49
Rick James
69.2k561101
asked Nov 15 '18 at 6:25
Leo ScaranoLeo Scarano
418
edited Nov 15 '18 at 20:49
Rick James
69.2k561101
asked Nov 15 '18 at 6:25
Leo ScaranoLeo Scarano
418
edited Nov 15 '18 at 20:49
Rick James
69.2k561101
edited Nov 15 '18 at 20:49
Rick James
69.2k561101
edited Nov 15 '18 at 20:49
Rick James
69.2k561101
69.2k561101
asked Nov 15 '18 at 6:25
Leo ScaranoLeo Scarano
418
asked Nov 15 '18 at 6:25
Leo ScaranoLeo Scarano
418
asked Nov 15 '18 at 6:25
Leo ScaranoLeo Scarano
418
418
A couple of obvious errors - there's not such data type as string and the order by in the second function is not ordering by anything. A less obvious error is SET @below_beer_name = @temp_query.Product Name; the syntactically correct version would be SET @below_beer_name = @temp_query since @temp_query is not a table - but maybe you want it to be?
– P.Salmon
Nov 15 '18 at 7:48
If you are looking to declare variables, to be using inside a stored function. you should useDECLAREsyntax, and it should be done inside the stored function definition instead.
– Madhur Bhaiya
Nov 15 '18 at 12:49
add a comment |
A couple of obvious errors - there's not such data type as string and the order by in the second function is not ordering by anything. A less obvious error is SET @below_beer_name = @temp_query.Product Name; the syntactically correct version would be SET @below_beer_name = @temp_query since @temp_query is not a table - but maybe you want it to be?
– P.Salmon
Nov 15 '18 at 7:48
If you are looking to declare variables, to be using inside a stored function. you should useDECLAREsyntax, and it should be done inside the stored function definition instead.
– Madhur Bhaiya
Nov 15 '18 at 12:49
A couple of obvious errors - there's not such data type as string and the order by in the second function is not ordering by anything. A less obvious error is SET @below_beer_name = @temp_query.
Product Name; the syntactically correct version would be SET @below_beer_name = @temp_query since @temp_query is not a table - but maybe you want it to be?– P.Salmon
Nov 15 '18 at 7:48
A couple of obvious errors - there's not such data type as string and the order by in the second function is not ordering by anything. A less obvious error is SET @below_beer_name = @temp_query.
Product Name; the syntactically correct version would be SET @below_beer_name = @temp_query since @temp_query is not a table - but maybe you want it to be?– P.Salmon
Nov 15 '18 at 7:48
If you are looking to declare variables, to be using inside a stored function. you should use
DECLARE syntax, and it should be done inside the stored function definition instead.– Madhur Bhaiya
Nov 15 '18 at 12:49
If you are looking to declare variables, to be using inside a stored function. you should use
DECLARE syntax, and it should be done inside the stored function definition instead.– Madhur Bhaiya
Nov 15 '18 at 12:49
add a comment |
0
active
oldest
votes
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
});
}
});
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%2f53313593%2fmysql-user-defined-functions-for-triggers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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%2f53313593%2fmysql-user-defined-functions-for-triggers%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
A couple of obvious errors - there's not such data type as string and the order by in the second function is not ordering by anything. A less obvious error is SET @below_beer_name = @temp_query.
Product Name; the syntactically correct version would be SET @below_beer_name = @temp_query since @temp_query is not a table - but maybe you want it to be?– P.Salmon
Nov 15 '18 at 7:48
If you are looking to declare variables, to be using inside a stored function. you should use
DECLAREsyntax, and it should be done inside the stored function definition instead.– Madhur Bhaiya
Nov 15 '18 at 12:49