1) (30 points) Employees may move to different departments. We want to keep trac
ID: 3844316 • Letter: 1
Question
1) (30 points)
Employees may move to different departments. We want to keep track of the departments where each employee has been. To do so we create a new table EMP_DEPT that keeps track of such history.
EMP_DEPT(EMPLOYEE_ID, EMPLOYEE_NAME, IS_NEW_EMPLOYEE,
OLD_DEPARTMENT_NAME, NEW_DEPARTMENT_NAME, EFFECTIVE_DATE);
Write a trigger EMP_DEPT_TRG that monitors the EMPLOYEE table as follows.
When a row (record) is inserted into the EMPLOYEE table, the trigger automatically inserts a row (record) into the EMP_DEPT table in any situations.
The IS_NEW_EMPLOYEE is always 'Y'.
The OLD_DEPARTMENT_NAME is always '------'.
If the new DEPARTMENT_ID is not NULL, find the new department name from the DEPARTMENT table based on the new DEPARTMENT_ID.
If the new DEPARTMENT_ID is NULL, the NEW_DEPARTMENT_NAME will be '------'.
When an employee changes his/her department (the old DEPARTMENT_ID is not equal to the new DEPARTMENT_ID), the trigger automatically inserts a row (record) into the EMP_DEPT table. (If both the old DEPARTMENT_ID and new DEPARTMENT_ID are NULL (from NULL department to NULL department), the trigger does not insert a row (record) into the EMP_DEPT table.)
The IS_NEW_EMPLOYEE is always 'N'.
If the old DEPARTMENT_ID is not NULL, find the old department name from the DEPARTMENT table based on the old DEPARTMENT_ID.
If the old DEPARTMENT_ID is NULL, the OLD_DEPARTMENT_NAME will be '------'.
If the new DEPARTMENT_ID is not NULL, find the new department name from the DEPARTMENT table based on the new DEPARTMENT_ID.
If the new DEPARTMENT_ID is NULL, the NEW_DEPARTMENT_NAME will be '------'.
The SYSDATE can be used in the EFFECTIVE_DATE column.
You can assume that the insert/update statements do not violate the integrity constraints between the DEPARTMENT and EMPLOYEE tables.
No temporary table/view/procedure/function is allowed in your trigger.
You can only use the DEPARTMENT, EMPLOYEE, and EMP_DEPT tables in your trigger. You will receive zero points if you use a different table (e.g., different table names, column names, or data types) in your trigger.
Step 1) (0 point) Create the EMP_DEPT table,
CREATE TABLE emp_dept
(
EMPLOYEE_ID NUMBER(4) NOT NULL,
EMPLOYEE_NAME VARCHAR2(50) NOT NULL,
IS_NEW_EMPLOYEE CHAR NOT NULL,
OLD_DEPARTMENT_NAME VARCHAR2(100) NOT NULL,
NEW_DEPARTMENT_NAME VARCHAR2(100) NOT NULL,
EFFECTIVE_DATE DATE NOT NULL
);
Step 2) Create the trigger emp_dept_trg.
You will receive zero points if you use a different trigger name.
Step 3) Test your trigger.
You need to create/run some test cases to check your trigger. You do not need to submit your test cases.
Q & A)
Q: I keep getting the following prompt when I try to use :OLD and :NEW when doing this week's homework. Does this have to do with my system settings or do I need to enter something on this screen?
A: You should always click the 2nd button or F5 to run your PL/SQL program. You can find that from lecture note 1, page 8.
The question
Create the trigger emp_dept_trg.
Perform the steps it is database programming
Enter Binds Name: New NULLExplanation / Answer
Solution:-
If you are using Window 10 then this is common problem which every student is facing. As per my knowledge bold and underline third option will definitely solve your problem even in any windows.
The second possibility is that it may be because of your bias settings which you need to change by only doing window updation. By control panel it is not possible.
The third thing is try to open a new SQL worksheet window in the schema and execute a "whole" script (not a statement) by pressing F5 (not F9).
or put "set define off;" before the command
Then highlight both commands and press F9 to run. Or you could run all the commands with F5.
It seems, that if the commands are executed separetly with F9, then the set define off does not take affect.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.