Step 1) (0 point) Create the EMP_DEPT table, CREATE TABLE emp_dept ( EMPLOYEE_ID
ID: 3844873 • Letter: S
Question
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
);
CREATE TABLE department
( DEPARTMENT_ID NUMBER(4) PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(20) NOT NULL UNIQUE,
LOCATION VARCHAR2(20) NOT NULL);
INSERT INTO department VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO department VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO department VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO department VALUES(40, 'IT', 'DALLAS');
INSERT INTO department VALUES(50, 'EXECUTIVE', 'NEW YORK');
INSERT INTO department VALUES(60, 'MARKETING', 'CHICAGO');
COMMIT;
CREATE TABLE employee
( EMPLOYEE_ID NUMBER(4) PRIMARY KEY,
EMPLOYEE_NAME VARCHAR2(20) NOT NULL,
JOB_TITLE VARCHAR2(50) NOT NULL,
MANAGER_ID NUMBER(4)
REFERENCES employee(EMPLOYEE_ID) ON DELETE SET NULL,
HIRE_DATE DATE NOT NULL,
SALARY NUMBER(9, 2) NOT NULL,
COMMISSION NUMBER(9, 2),
DEPARTMENT_ID NUMBER(4) REFERENCES department(DEPARTMENT_ID));
INSERT INTO employee
VALUES(7839, 'KING', 'PRESIDENT', NULL, '20-NOV-01', 5000, NULL, 50);
INSERT INTO employee
VALUES(7596, 'JOST', 'VICE PRESIDENT', 7839, '04-MAY-01', 4500, NULL, 50);
INSERT INTO employee
VALUES(7603, 'CLARK', 'VICE PRESIDENT', 7839, '12-JUN-01', 4000, NULL, 50);
INSERT INTO employee
VALUES(7566, 'JONES', 'CHIEF ACCOUNTANT', 7596, '05-APR-01', 3000, NULL, 10);
INSERT INTO employee
VALUES(7886, 'STEEL', 'PUBLIC ACCOUNTANT', 7566, '08-MAR-03', 2500, NULL, 10);
INSERT INTO employee
VALUES(7610, 'WILSON', 'BUSINESS ANALYST', 7596, '03-DEC-01', 3000, NULL, 20);
INSERT INTO employee
VALUES(7999, 'WOLFE', 'TEST ANALYST', 7610, '15-FEB-02', 2500, NULL, 20);
INSERT INTO employee
VALUES(7944, 'LEE', 'REPORTING ANALYST', 7610, '04-SEP-06', 2400, NULL, 20);
Step 2)
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 3) Test your trigger.
Explanation / Answer
create or replace trigger EMP_DEPT_TRG after insert on employee for each row
begin
if :new.department_id != null then
insert into EMP_DEPT values(:new.employee_id,:new.employee_name,'Y','------','-------',:new.hire_date);
else
insert into EMP_DEPT values(:new.employee_id,:new.employee_name,'Y','------',(select department_name from department where department_id=:new.department_id),:new.hire_date);
end if;
end;
/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.