pl/sql create a rowlevel trigger
up vote
0
down vote
favorite
Create a row level trigger “dep_check” that fires before insert operation associated with employees table. The trigger should check if the new Department_Number exist in Departments table. If the Department_Number does not exist in Department table, a new department record will be created (‘you can name it any name’) and the Department_Number should be the new value in the insert statement. print a message ‘Department # is created’ replace the # with the department_no. if the department exist already, the trigger should print ‘Employee record inserted’.
This is what i tried but it gives me error
enter code here
CREATE OR REPLACE TRIGGER dep_check
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
if new.department_number in departments THEN
dbms_output.put_line ('Employee record inserted.');
else
insert into departments values (:new.department_number,:new.employee_id,
:new.Last_name, :new.first_name, :new.manager_id);
dbms_output.put_line ('department '|| old.department_number || 'is created. '||
'replace the ' || old.department ||' with the '|| new.department_number);
end if;
end;
plsql
asked Nov 20 at 3:25
nunu
61
add a comment |
up vote
0
down vote
favorite
Create a row level trigger “dep_check” that fires before insert operation associated with employees table. The trigger should check if the new Department_Number exist in Departments table. If the Department_Number does not exist in Department table, a new department record will be created (‘you can name it any name’) and the Department_Number should be the new value in the insert statement. print a message ‘Department # is created’ replace the # with the department_no. if the department exist already, the trigger should print ‘Employee record inserted’.
This is what i tried but it gives me error
enter code here
CREATE OR REPLACE TRIGGER dep_check
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
if new.department_number in departments THEN
dbms_output.put_line ('Employee record inserted.');
else
insert into departments values (:new.department_number,:new.employee_id,
:new.Last_name, :new.first_name, :new.manager_id);
dbms_output.put_line ('department '|| old.department_number || 'is created. '||
'replace the ' || old.department ||' with the '|| new.department_number);
end if;
end;
plsql
asked Nov 20 at 3:25
nunu
61
You need to run a select query to check if exists. By the way, why are you storing employee's details in department table? Furthermore, displaying from a trigger usingdbms_outputis not useful. It won't appear on screen in most cases.
– Kaushik Nayak
Nov 20 at 7:13
Also, you need to ensure you're referring to thenewandoldrecords as bind variables in all places - i.e.:new.<field name>and:old.<field name>- note the presence of the:! You say "it gives me error"... what error? We aren't sitting behind you, so can't see your screen. Please edit your question to include the error message you're getting.
– Boneist
Nov 20 at 8:38
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Create a row level trigger “dep_check” that fires before insert operation associated with employees table. The trigger should check if the new Department_Number exist in Departments table. If the Department_Number does not exist in Department table, a new department record will be created (‘you can name it any name’) and the Department_Number should be the new value in the insert statement. print a message ‘Department # is created’ replace the # with the department_no. if the department exist already, the trigger should print ‘Employee record inserted’.
This is what i tried but it gives me error
enter code here
CREATE OR REPLACE TRIGGER dep_check
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
if new.department_number in departments THEN
dbms_output.put_line ('Employee record inserted.');
else
insert into departments values (:new.department_number,:new.employee_id,
:new.Last_name, :new.first_name, :new.manager_id);
dbms_output.put_line ('department '|| old.department_number || 'is created. '||
'replace the ' || old.department ||' with the '|| new.department_number);
end if;
end;
plsql
asked Nov 20 at 3:25
nunu
61
Create a row level trigger “dep_check” that fires before insert operation associated with employees table. The trigger should check if the new Department_Number exist in Departments table. If the Department_Number does not exist in Department table, a new department record will be created (‘you can name it any name’) and the Department_Number should be the new value in the insert statement. print a message ‘Department # is created’ replace the # with the department_no. if the department exist already, the trigger should print ‘Employee record inserted’.
This is what i tried but it gives me error
enter code here
CREATE OR REPLACE TRIGGER dep_check
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
if new.department_number in departments THEN
dbms_output.put_line ('Employee record inserted.');
else
insert into departments values (:new.department_number,:new.employee_id,
:new.Last_name, :new.first_name, :new.manager_id);
dbms_output.put_line ('department '|| old.department_number || 'is created. '||
'replace the ' || old.department ||' with the '|| new.department_number);
end if;
end;
plsql
plsql
asked Nov 20 at 3:25
nunu
61
asked Nov 20 at 3:25
nunu
61
asked Nov 20 at 3:25
nunu
61
asked Nov 20 at 3:25
nunu
61
asked Nov 20 at 3:25
nunu
61
61
You need to run a select query to check if exists. By the way, why are you storing employee's details in department table? Furthermore, displaying from a trigger usingdbms_outputis not useful. It won't appear on screen in most cases.
– Kaushik Nayak
Nov 20 at 7:13
Also, you need to ensure you're referring to thenewandoldrecords as bind variables in all places - i.e.:new.<field name>and:old.<field name>- note the presence of the:! You say "it gives me error"... what error? We aren't sitting behind you, so can't see your screen. Please edit your question to include the error message you're getting.
– Boneist
Nov 20 at 8:38
add a comment |
You need to run a select query to check if exists. By the way, why are you storing employee's details in department table? Furthermore, displaying from a trigger usingdbms_outputis not useful. It won't appear on screen in most cases.
– Kaushik Nayak
Nov 20 at 7:13
Also, you need to ensure you're referring to thenewandoldrecords as bind variables in all places - i.e.:new.<field name>and:old.<field name>- note the presence of the:! You say "it gives me error"... what error? We aren't sitting behind you, so can't see your screen. Please edit your question to include the error message you're getting.
– Boneist
Nov 20 at 8:38
You need to run a select query to check if exists. By the way, why are you storing employee's details in department table? Furthermore, displaying from a trigger using
dbms_output is not useful. It won't appear on screen in most cases.– Kaushik Nayak
Nov 20 at 7:13
You need to run a select query to check if exists. By the way, why are you storing employee's details in department table? Furthermore, displaying from a trigger using
dbms_output is not useful. It won't appear on screen in most cases.– Kaushik Nayak
Nov 20 at 7:13
Also, you need to ensure you're referring to the
new and old records as bind variables in all places - i.e. :new.<field name> and :old.<field name> - note the presence of the :! You say "it gives me error"... what error? We aren't sitting behind you, so can't see your screen. Please edit your question to include the error message you're getting.– Boneist
Nov 20 at 8:38
Also, you need to ensure you're referring to the
new and old records as bind variables in all places - i.e. :new.<field name> and :old.<field name> - note the presence of the :! You say "it gives me error"... what error? We aren't sitting behind you, so can't see your screen. Please edit your question to include the error message you're getting.– Boneist
Nov 20 at 8:38
add a comment |
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',
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%2f53385770%2fpl-sql-create-a-rowlevel-trigger%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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%2f53385770%2fpl-sql-create-a-rowlevel-trigger%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
You need to run a select query to check if exists. By the way, why are you storing employee's details in department table? Furthermore, displaying from a trigger using
dbms_outputis not useful. It won't appear on screen in most cases.– Kaushik Nayak
Nov 20 at 7:13
Also, you need to ensure you're referring to the
newandoldrecords as bind variables in all places - i.e.:new.<field name>and:old.<field name>- note the presence of the:! You say "it gives me error"... what error? We aren't sitting behind you, so can't see your screen. Please edit your question to include the error message you're getting.– Boneist
Nov 20 at 8:38