Mixing
How to find alternative for INNER JOIN in triggers for this case?
0
I find difficult to understand why proceeding the trigger with inner join function, cmd causes error, that: psql:createTrigger.sql:46: ERROR: "currentStudentNumber" is not a known variable LINE 8: SELECT COUNT(Student_subject.StudentID) INTO currentStudentNumber
--rule, that lecturer can not have more than 7 students
CREATE FUNCTION lecturersStudentNumber() RETURNS "trigger" AS $$
DECLARE
maxStudenNumber CONSTANT SMALLINT :=7;
currentStudentNumber SMALLINT;
BEGIN
SELECT COUNT(Student_Subject.StudentID) INTO currentStudentNumber
FROM rora3887.Subject_Lecturer
INNER JOIN rora3887.Lecturer ON Subject_Lecturer.LecturerID=Lecturer.LecturerID
INNER JOIN rora3887.Subject ON Subject_Lecturer.SubjectID=Subject.SubjectID
INNER JOIN rora3887.Student_Subject ON Subject.SubjectID=Student_Subject.SubjectID
GROUP BY Lecturer.LecturerID;
IF MAX(currentStudentNumber) + 1 > maxStudentNumber
THEN RAISE EXCEPTION 'Lecturer can not have more than 7 students';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER lecturersMaxStudentNumber
BEFORE INSERT OR UPDATE ON rora3887.Subject_Lecturer
FOR EACH ROW EXECUTE PROCEDURE lecturersStudentNumber();
I have a file createTrigger.sql with such code.
My database reliation scheme
postgresql triggers inner-join psql
edited Nov 21 '18 at 18:36
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 13:48
Rolanda RaudytėRolanda Raudytė
52
add a comment |
0
I find difficult to understand why proceeding the trigger with inner join function, cmd causes error, that: psql:createTrigger.sql:46: ERROR: "currentStudentNumber" is not a known variable LINE 8: SELECT COUNT(Student_subject.StudentID) INTO currentStudentNumber
--rule, that lecturer can not have more than 7 students
CREATE FUNCTION lecturersStudentNumber() RETURNS "trigger" AS $$
DECLARE
maxStudenNumber CONSTANT SMALLINT :=7;
currentStudentNumber SMALLINT;
BEGIN
SELECT COUNT(Student_Subject.StudentID) INTO currentStudentNumber
FROM rora3887.Subject_Lecturer
INNER JOIN rora3887.Lecturer ON Subject_Lecturer.LecturerID=Lecturer.LecturerID
INNER JOIN rora3887.Subject ON Subject_Lecturer.SubjectID=Subject.SubjectID
INNER JOIN rora3887.Student_Subject ON Subject.SubjectID=Student_Subject.SubjectID
GROUP BY Lecturer.LecturerID;
IF MAX(currentStudentNumber) + 1 > maxStudentNumber
THEN RAISE EXCEPTION 'Lecturer can not have more than 7 students';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER lecturersMaxStudentNumber
BEFORE INSERT OR UPDATE ON rora3887.Subject_Lecturer
FOR EACH ROW EXECUTE PROCEDURE lecturersStudentNumber();
I have a file createTrigger.sql with such code.
My database reliation scheme
postgresql triggers inner-join psql
edited Nov 21 '18 at 18:36
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 13:48
Rolanda RaudytėRolanda Raudytė
52
1
You might get an answer if you tagged this question with the dbms you are using (postgresql?)
– P.Salmon
Nov 21 '18 at 16:15
add a comment |
0
0
0
I find difficult to understand why proceeding the trigger with inner join function, cmd causes error, that: psql:createTrigger.sql:46: ERROR: "currentStudentNumber" is not a known variable LINE 8: SELECT COUNT(Student_subject.StudentID) INTO currentStudentNumber
--rule, that lecturer can not have more than 7 students
CREATE FUNCTION lecturersStudentNumber() RETURNS "trigger" AS $$
DECLARE
maxStudenNumber CONSTANT SMALLINT :=7;
currentStudentNumber SMALLINT;
BEGIN
SELECT COUNT(Student_Subject.StudentID) INTO currentStudentNumber
FROM rora3887.Subject_Lecturer
INNER JOIN rora3887.Lecturer ON Subject_Lecturer.LecturerID=Lecturer.LecturerID
INNER JOIN rora3887.Subject ON Subject_Lecturer.SubjectID=Subject.SubjectID
INNER JOIN rora3887.Student_Subject ON Subject.SubjectID=Student_Subject.SubjectID
GROUP BY Lecturer.LecturerID;
IF MAX(currentStudentNumber) + 1 > maxStudentNumber
THEN RAISE EXCEPTION 'Lecturer can not have more than 7 students';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER lecturersMaxStudentNumber
BEFORE INSERT OR UPDATE ON rora3887.Subject_Lecturer
FOR EACH ROW EXECUTE PROCEDURE lecturersStudentNumber();
I have a file createTrigger.sql with such code.
My database reliation scheme
postgresql triggers inner-join psql
edited Nov 21 '18 at 18:36
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 13:48
Rolanda RaudytėRolanda Raudytė
52
I find difficult to understand why proceeding the trigger with inner join function, cmd causes error, that: psql:createTrigger.sql:46: ERROR: "currentStudentNumber" is not a known variable LINE 8: SELECT COUNT(Student_subject.StudentID) INTO currentStudentNumber
--rule, that lecturer can not have more than 7 students
CREATE FUNCTION lecturersStudentNumber() RETURNS "trigger" AS $$
DECLARE
maxStudenNumber CONSTANT SMALLINT :=7;
currentStudentNumber SMALLINT;
BEGIN
SELECT COUNT(Student_Subject.StudentID) INTO currentStudentNumber
FROM rora3887.Subject_Lecturer
INNER JOIN rora3887.Lecturer ON Subject_Lecturer.LecturerID=Lecturer.LecturerID
INNER JOIN rora3887.Subject ON Subject_Lecturer.SubjectID=Subject.SubjectID
INNER JOIN rora3887.Student_Subject ON Subject.SubjectID=Student_Subject.SubjectID
GROUP BY Lecturer.LecturerID;
IF MAX(currentStudentNumber) + 1 > maxStudentNumber
THEN RAISE EXCEPTION 'Lecturer can not have more than 7 students';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER lecturersMaxStudentNumber
BEFORE INSERT OR UPDATE ON rora3887.Subject_Lecturer
FOR EACH ROW EXECUTE PROCEDURE lecturersStudentNumber();
I have a file createTrigger.sql with such code.
My database reliation scheme
postgresql triggers inner-join psql
postgresql triggers inner-join psql
edited Nov 21 '18 at 18:36
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 13:48
Rolanda RaudytėRolanda Raudytė
52
edited Nov 21 '18 at 18:36
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 13:48
Rolanda RaudytėRolanda Raudytė
52
edited Nov 21 '18 at 18:36
Madhur Bhaiya
19.6k62236
edited Nov 21 '18 at 18:36
Madhur Bhaiya
19.6k62236
edited Nov 21 '18 at 18:36
Madhur Bhaiya
19.6k62236
19.6k62236
asked Nov 21 '18 at 13:48
Rolanda RaudytėRolanda Raudytė
52
asked Nov 21 '18 at 13:48
Rolanda RaudytėRolanda Raudytė
52
asked Nov 21 '18 at 13:48
Rolanda RaudytėRolanda Raudytė
52
52
1
You might get an answer if you tagged this question with the dbms you are using (postgresql?)
– P.Salmon
Nov 21 '18 at 16:15
add a comment |
1
You might get an answer if you tagged this question with the dbms you are using (postgresql?)
– P.Salmon
Nov 21 '18 at 16:15
1
1
You might get an answer if you tagged this question with the dbms you are using (postgresql?)
– P.Salmon
Nov 21 '18 at 16:15
You might get an answer if you tagged this question with the dbms you are using (postgresql?)
– P.Salmon
Nov 21 '18 at 16:15
add a comment |
1 Answer
1
active
oldest
votes
0
The problem is with the keyword INTO:
SELECT COUNT(Student_Subject.StudentID) INTO currentStudentNumber
Change INTO --> AS
The resulting query will be:
SELECT COUNT(Student_Subject.StudentID) AS currentStudentNumber
Hope it works :)
edited Nov 21 '18 at 16:12
kiner_shah
1,26221423
answered Nov 21 '18 at 15:56
Aymeric SamynAymeric Samyn
12
This error I wrote before in my question dissapeared. But now, when i create a table, then create trigger and insert data to database, occurs: "psql:inserts.sql:103: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead." and all Subject_Lecturer table is empty.
– Rolanda Raudytė
Nov 21 '18 at 17:42
Follow the hint and write PERFORM instead of SELECT.
– Aymeric Samyn
Nov 22 '18 at 8:43
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%2f53413537%2fhow-to-find-alternative-for-inner-join-in-triggers-for-this-case%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
The problem is with the keyword INTO:
SELECT COUNT(Student_Subject.StudentID) INTO currentStudentNumber
Change INTO --> AS
The resulting query will be:
SELECT COUNT(Student_Subject.StudentID) AS currentStudentNumber
Hope it works :)
edited Nov 21 '18 at 16:12
kiner_shah
1,26221423
answered Nov 21 '18 at 15:56
Aymeric SamynAymeric Samyn
12
This error I wrote before in my question dissapeared. But now, when i create a table, then create trigger and insert data to database, occurs: "psql:inserts.sql:103: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead." and all Subject_Lecturer table is empty.
– Rolanda Raudytė
Nov 21 '18 at 17:42
Follow the hint and write PERFORM instead of SELECT.
– Aymeric Samyn
Nov 22 '18 at 8:43
add a comment |
0
The problem is with the keyword INTO:
SELECT COUNT(Student_Subject.StudentID) INTO currentStudentNumber
Change INTO --> AS
The resulting query will be:
SELECT COUNT(Student_Subject.StudentID) AS currentStudentNumber
Hope it works :)
edited Nov 21 '18 at 16:12
kiner_shah
1,26221423
answered Nov 21 '18 at 15:56
Aymeric SamynAymeric Samyn
12
This error I wrote before in my question dissapeared. But now, when i create a table, then create trigger and insert data to database, occurs: "psql:inserts.sql:103: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead." and all Subject_Lecturer table is empty.
– Rolanda Raudytė
Nov 21 '18 at 17:42
Follow the hint and write PERFORM instead of SELECT.
– Aymeric Samyn
Nov 22 '18 at 8:43
add a comment |
0
0
0
The problem is with the keyword INTO:
SELECT COUNT(Student_Subject.StudentID) INTO currentStudentNumber
Change INTO --> AS
The resulting query will be:
SELECT COUNT(Student_Subject.StudentID) AS currentStudentNumber
Hope it works :)
edited Nov 21 '18 at 16:12
kiner_shah
1,26221423
answered Nov 21 '18 at 15:56
Aymeric SamynAymeric Samyn
12
The problem is with the keyword INTO:
SELECT COUNT(Student_Subject.StudentID) INTO currentStudentNumber
Change INTO --> AS
The resulting query will be:
SELECT COUNT(Student_Subject.StudentID) AS currentStudentNumber
Hope it works :)
edited Nov 21 '18 at 16:12
kiner_shah
1,26221423
answered Nov 21 '18 at 15:56
Aymeric SamynAymeric Samyn
12
edited Nov 21 '18 at 16:12
kiner_shah
1,26221423
edited Nov 21 '18 at 16:12
kiner_shah
1,26221423
edited Nov 21 '18 at 16:12
kiner_shah
1,26221423
1,26221423
answered Nov 21 '18 at 15:56
Aymeric SamynAymeric Samyn
12
answered Nov 21 '18 at 15:56
Aymeric SamynAymeric Samyn
12
answered Nov 21 '18 at 15:56
Aymeric SamynAymeric Samyn
12
12
This error I wrote before in my question dissapeared. But now, when i create a table, then create trigger and insert data to database, occurs: "psql:inserts.sql:103: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead." and all Subject_Lecturer table is empty.
– Rolanda Raudytė
Nov 21 '18 at 17:42
Follow the hint and write PERFORM instead of SELECT.
– Aymeric Samyn
Nov 22 '18 at 8:43
add a comment |
This error I wrote before in my question dissapeared. But now, when i create a table, then create trigger and insert data to database, occurs: "psql:inserts.sql:103: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead." and all Subject_Lecturer table is empty.
– Rolanda Raudytė
Nov 21 '18 at 17:42
Follow the hint and write PERFORM instead of SELECT.
– Aymeric Samyn
Nov 22 '18 at 8:43
This error I wrote before in my question dissapeared. But now, when i create a table, then create trigger and insert data to database, occurs: "psql:inserts.sql:103: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead." and all Subject_Lecturer table is empty.
– Rolanda Raudytė
Nov 21 '18 at 17:42
This error I wrote before in my question dissapeared. But now, when i create a table, then create trigger and insert data to database, occurs: "psql:inserts.sql:103: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead." and all Subject_Lecturer table is empty.
– Rolanda Raudytė
Nov 21 '18 at 17:42
Follow the hint and write PERFORM instead of SELECT.
– Aymeric Samyn
Nov 22 '18 at 8:43
Follow the hint and write PERFORM instead of SELECT.
– Aymeric Samyn
Nov 22 '18 at 8:43
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%2f53413537%2fhow-to-find-alternative-for-inner-join-in-triggers-for-this-case%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
You might get an answer if you tagged this question with the dbms you are using (postgresql?)
– P.Salmon
Nov 21 '18 at 16:15