trigger more or equal two, reacts only on more than two
0
CREATE TABLE IF NOT EXISTS owners (
id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
address VARCHAR(255),
city VARCHAR(80),
telephone VARCHAR(20),
INDEX(last_name)
) engine=InnoDB;
CREATE TABLE IF NOT EXISTS pets (
id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
birth_date DATE,
type_id INT(4) UNSIGNED NOT NULL,
owner_id INT(4) UNSIGNED NOT NULL,
INDEX(name),
FOREIGN KEY (owner_id) REFERENCES owners(id),
FOREIGN KEY (type_id) REFERENCES types(id)
) engine=InnoDB;
create trigger petadd_trigger4 before insert on pets
for each row
begin
DECLARE pets_amount int;
SELECT COUNT(*) INTO pets_amount FROM pets p WHERE p.owner_id=new.owner_id;
if (pets_amount>=2)
then
signal sqlstate '45000' SET MESSAGE_TEXT = 'Too many pets';
end if;
I don't understand why this trigger reacts only when I add more than two animals. It's some kind of bug? Because it should reacts when I tried to add second animal
mysql
asked Nov 19 '18 at 14:27
wwwwwwww
265
add a comment |
0
CREATE TABLE IF NOT EXISTS owners (
id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
address VARCHAR(255),
city VARCHAR(80),
telephone VARCHAR(20),
INDEX(last_name)
) engine=InnoDB;
CREATE TABLE IF NOT EXISTS pets (
id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
birth_date DATE,
type_id INT(4) UNSIGNED NOT NULL,
owner_id INT(4) UNSIGNED NOT NULL,
INDEX(name),
FOREIGN KEY (owner_id) REFERENCES owners(id),
FOREIGN KEY (type_id) REFERENCES types(id)
) engine=InnoDB;
create trigger petadd_trigger4 before insert on pets
for each row
begin
DECLARE pets_amount int;
SELECT COUNT(*) INTO pets_amount FROM pets p WHERE p.owner_id=new.owner_id;
if (pets_amount>=2)
then
signal sqlstate '45000' SET MESSAGE_TEXT = 'Too many pets';
end if;
I don't understand why this trigger reacts only when I add more than two animals. It's some kind of bug? Because it should reacts when I tried to add second animal
mysql
asked Nov 19 '18 at 14:27
wwwwwwww
265
1
I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Raymond Nijland
Nov 19 '18 at 14:32
I don't think your trigger is doing what you think it is.FOR EACH ROWmeans that the logic executes once, for each row, not in aggregate.
– Tim Biegeleisen
Nov 19 '18 at 14:32
1
This sort of thing may also be hard to enforce. For example, if there is room for only one more pet, what happens? Do you block an insert of two records, or do you allow one insert only to proceed?
– Tim Biegeleisen
Nov 19 '18 at 14:36
What @TimBiegeleisen says is true i don't believe that there is a (easy) way to limit records per group in MySQL when inserting or updating.. Why not the other way around? select the "TOP 2" pets from every user..
– Raymond Nijland
Nov 19 '18 at 14:40
Not related to your question. But you probably want to said "Too many pets"
– Juan Carlos Oropeza
Nov 19 '18 at 14:55
add a comment |
0
0
0
CREATE TABLE IF NOT EXISTS owners (
id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
address VARCHAR(255),
city VARCHAR(80),
telephone VARCHAR(20),
INDEX(last_name)
) engine=InnoDB;
CREATE TABLE IF NOT EXISTS pets (
id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
birth_date DATE,
type_id INT(4) UNSIGNED NOT NULL,
owner_id INT(4) UNSIGNED NOT NULL,
INDEX(name),
FOREIGN KEY (owner_id) REFERENCES owners(id),
FOREIGN KEY (type_id) REFERENCES types(id)
) engine=InnoDB;
create trigger petadd_trigger4 before insert on pets
for each row
begin
DECLARE pets_amount int;
SELECT COUNT(*) INTO pets_amount FROM pets p WHERE p.owner_id=new.owner_id;
if (pets_amount>=2)
then
signal sqlstate '45000' SET MESSAGE_TEXT = 'Too many pets';
end if;
I don't understand why this trigger reacts only when I add more than two animals. It's some kind of bug? Because it should reacts when I tried to add second animal
mysql
asked Nov 19 '18 at 14:27
wwwwwwww
265
CREATE TABLE IF NOT EXISTS owners (
id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
address VARCHAR(255),
city VARCHAR(80),
telephone VARCHAR(20),
INDEX(last_name)
) engine=InnoDB;
CREATE TABLE IF NOT EXISTS pets (
id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
birth_date DATE,
type_id INT(4) UNSIGNED NOT NULL,
owner_id INT(4) UNSIGNED NOT NULL,
INDEX(name),
FOREIGN KEY (owner_id) REFERENCES owners(id),
FOREIGN KEY (type_id) REFERENCES types(id)
) engine=InnoDB;
create trigger petadd_trigger4 before insert on pets
for each row
begin
DECLARE pets_amount int;
SELECT COUNT(*) INTO pets_amount FROM pets p WHERE p.owner_id=new.owner_id;
if (pets_amount>=2)
then
signal sqlstate '45000' SET MESSAGE_TEXT = 'Too many pets';
end if;
I don't understand why this trigger reacts only when I add more than two animals. It's some kind of bug? Because it should reacts when I tried to add second animal
mysql
mysql
asked Nov 19 '18 at 14:27
wwwwwwww
265
asked Nov 19 '18 at 14:27
wwwwwwww
265
edited Nov 19 '18 at 15:27
wwww
asked Nov 19 '18 at 14:27
wwwwwwww
265
asked Nov 19 '18 at 14:27
wwwwwwww
265
asked Nov 19 '18 at 14:27
wwwwwwww
265
265
1
I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Raymond Nijland
Nov 19 '18 at 14:32
I don't think your trigger is doing what you think it is.FOR EACH ROWmeans that the logic executes once, for each row, not in aggregate.
– Tim Biegeleisen
Nov 19 '18 at 14:32
1
This sort of thing may also be hard to enforce. For example, if there is room for only one more pet, what happens? Do you block an insert of two records, or do you allow one insert only to proceed?
– Tim Biegeleisen
Nov 19 '18 at 14:36
What @TimBiegeleisen says is true i don't believe that there is a (easy) way to limit records per group in MySQL when inserting or updating.. Why not the other way around? select the "TOP 2" pets from every user..
– Raymond Nijland
Nov 19 '18 at 14:40
Not related to your question. But you probably want to said "Too many pets"
– Juan Carlos Oropeza
Nov 19 '18 at 14:55
add a comment |
1
I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Raymond Nijland
Nov 19 '18 at 14:32
I don't think your trigger is doing what you think it is.FOR EACH ROWmeans that the logic executes once, for each row, not in aggregate.
– Tim Biegeleisen
Nov 19 '18 at 14:32
1
This sort of thing may also be hard to enforce. For example, if there is room for only one more pet, what happens? Do you block an insert of two records, or do you allow one insert only to proceed?
– Tim Biegeleisen
Nov 19 '18 at 14:36
What @TimBiegeleisen says is true i don't believe that there is a (easy) way to limit records per group in MySQL when inserting or updating.. Why not the other way around? select the "TOP 2" pets from every user..
– Raymond Nijland
Nov 19 '18 at 14:40
Not related to your question. But you probably want to said "Too many pets"
– Juan Carlos Oropeza
Nov 19 '18 at 14:55
1
1
I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Raymond Nijland
Nov 19 '18 at 14:32
I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Raymond Nijland
Nov 19 '18 at 14:32
I don't think your trigger is doing what you think it is.
FOR EACH ROW means that the logic executes once, for each row, not in aggregate.– Tim Biegeleisen
Nov 19 '18 at 14:32
I don't think your trigger is doing what you think it is.
FOR EACH ROW means that the logic executes once, for each row, not in aggregate.– Tim Biegeleisen
Nov 19 '18 at 14:32
1
1
This sort of thing may also be hard to enforce. For example, if there is room for only one more pet, what happens? Do you block an insert of two records, or do you allow one insert only to proceed?
– Tim Biegeleisen
Nov 19 '18 at 14:36
This sort of thing may also be hard to enforce. For example, if there is room for only one more pet, what happens? Do you block an insert of two records, or do you allow one insert only to proceed?
– Tim Biegeleisen
Nov 19 '18 at 14:36
What @TimBiegeleisen says is true i don't believe that there is a (easy) way to limit records per group in MySQL when inserting or updating.. Why not the other way around? select the "TOP 2" pets from every user..
– Raymond Nijland
Nov 19 '18 at 14:40
What @TimBiegeleisen says is true i don't believe that there is a (easy) way to limit records per group in MySQL when inserting or updating.. Why not the other way around? select the "TOP 2" pets from every user..
– Raymond Nijland
Nov 19 '18 at 14:40
Not related to your question. But you probably want to said "Too many pets"
– Juan Carlos Oropeza
Nov 19 '18 at 14:55
Not related to your question. But you probably want to said "Too many pets"
– Juan Carlos Oropeza
Nov 19 '18 at 14:55
add a comment |
1 Answer
1
active
oldest
votes
0
Before inserting a new record your trigger counts the already inserted records for the owner. If there are already 2 then it will not allow to insert another one.
I think you are looking for
if (pets_amount > 0)
answered Nov 19 '18 at 14:42
juergen djuergen d
160k24203261
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
});
}
});
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%2f53376741%2ftrigger-more-or-equal-two-reacts-only-on-more-than-two%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
0
Before inserting a new record your trigger counts the already inserted records for the owner. If there are already 2 then it will not allow to insert another one.
I think you are looking for
if (pets_amount > 0)
answered Nov 19 '18 at 14:42
juergen djuergen d
160k24203261
add a comment |
0
Before inserting a new record your trigger counts the already inserted records for the owner. If there are already 2 then it will not allow to insert another one.
I think you are looking for
if (pets_amount > 0)
answered Nov 19 '18 at 14:42
juergen djuergen d
160k24203261
add a comment |
0
0
0
Before inserting a new record your trigger counts the already inserted records for the owner. If there are already 2 then it will not allow to insert another one.
I think you are looking for
if (pets_amount > 0)
answered Nov 19 '18 at 14:42
juergen djuergen d
160k24203261
Before inserting a new record your trigger counts the already inserted records for the owner. If there are already 2 then it will not allow to insert another one.
I think you are looking for
if (pets_amount > 0)
answered Nov 19 '18 at 14:42
juergen djuergen d
160k24203261
answered Nov 19 '18 at 14:42
juergen djuergen d
160k24203261
answered Nov 19 '18 at 14:42
juergen djuergen d
160k24203261
answered Nov 19 '18 at 14:42
juergen djuergen d
160k24203261
160k24203261
add a comment |
add a 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.
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%2f53376741%2ftrigger-more-or-equal-two-reacts-only-on-more-than-two%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
1
I advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Raymond Nijland
Nov 19 '18 at 14:32
I don't think your trigger is doing what you think it is.
FOR EACH ROWmeans that the logic executes once, for each row, not in aggregate.– Tim Biegeleisen
Nov 19 '18 at 14:32
1
This sort of thing may also be hard to enforce. For example, if there is room for only one more pet, what happens? Do you block an insert of two records, or do you allow one insert only to proceed?
– Tim Biegeleisen
Nov 19 '18 at 14:36
What @TimBiegeleisen says is true i don't believe that there is a (easy) way to limit records per group in MySQL when inserting or updating.. Why not the other way around? select the "TOP 2" pets from every user..
– Raymond Nijland
Nov 19 '18 at 14:40
Not related to your question. But you probably want to said "Too many pets"
– Juan Carlos Oropeza
Nov 19 '18 at 14:55