Create a trigger called EMP_SAL_TRG on the EMPLOYEE table. When an INSERT or UPDATE statement is issued against the EMPLOYEE table, the trigger is fired to ensure that the value of the SALARY column meets the criteria in the TAB_MINIMUM_SALARY tablein any situations.(For example, you can find that the minimum salary for a programmer is 800 from the TAB_MINIMUM_SALARY table. Your trigger ensures that the salary for a programmer in the EMPLOYEE table is greater than or equal to 800 in any situations.)

Step 1) (0 point) Create a table MINIMUM_SALARY as follows.

 

CREATE TABLE MINIMUM_SALARY

(

    JOB_TITLE      VARCHAR2(100) PRIMARY KEY,

    MINIMUM_SALARY NUMBER(7, 2) NOT NULL

);

 

Step 2) (0 point) Populate the TAB_INIMUM_SALARY table as follows.

 

INSERT INTO minimum_salary VALUES ('BUSINESS ANALYST', 2800);

INSERT INTO minimum_salary VALUES ('CHIEF ACCOUNTANT', 2900);

INSERT INTO minimum_salary VALUES ('DATABASE ADMINISTRATOR', 2800);

INSERT INTO minimum_salary VALUES ('PRESIDENT', 4800);

INSERT INTO minimum_salary VALUES ('PROGRAMMER', 800);

INSERT INTO minimum_salary VALUES ('PUBLIC ACCOUNTANT', 2400);

INSERT INTO minimum_salary VALUES ('REPORTING ANALYST', 2000);

INSERT INTO minimum_salary VALUES ('SALES CONSULTANT', 1500);

INSERT INTO minimum_salary VALUES ('SALES EXECUTIVE', 2800);

INSERT INTO minimum_salary VALUES ('SALES REPRESENTATIVE', 2000);

INSERT INTO minimum_salary VALUES ('TEST ANALYST', 1500);

INSERT INTO minimum_salary VALUES ('VICE PRESIDENT', 3800);

INSERT INTO minimum_salary VALUES ('X', 800);

COMMIT;

 

Create table EMPLOYEE as described below.

 

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));

 

Populate the EMPLOYEE table.

 

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);

INSERT INTO employee

VALUES(7900, 'FISHER', 'SALES EXECUTIVE', 7603, '06-DEC-01', 3000, 500, 30);

INSERT INTO employee

VALUES(7921, 'JACKSON', 'SALES REPRESENTATIVE', 7900, '25-FEB-05', 2500, 400, 30);

INSERT INTO employee

VALUES(7952, 'LANCASTER', 'SALES CONSULTANT', 7900, '06-DEC-06', 2000, 150, 30);

INSERT INTO employee 

VALUES(7910, 'SMITH', 'DATABASE ADMINISTRATOR', 7596, '20-DEC-01', 2900, NULL, 40);

INSERT INTO employee 

VALUES(7788, 'SCOTT', 'PROGRAMMER', 7910, '15-JAN-03', 2500, NULL, 40);

INSERT INTO employee

VALUES(7876, 'ADAMS', 'PROGRAMMER', 7910, '15-JAN-03', 2000, NULL, 40);

INSERT INTO employee

VALUES(7934, 'MILLER', 'PROGRAMMER', 7876, '25-JAN-02', 1000, NULL, 40);

INSERT INTO employee

VALUES(8000, 'BREWSTER', 'TBA', NULL,  '22-AUG-13', 2500, NULL, NULL);

COMMIT;

 

Step 3) Create the trigger EMP_MIN_SAL_TRG.

 

·       The MINIMUM_SALARY table is read-only. Your trigger cannot modify any rows in the MINIMUM_SALARY table.

·       You must get the minimum salaries from the MINIMUM_SALARY table in your trigger.

·       The job is not case sensitive (e.g., PROGRAMMER = Programmer). 

·       Hard coding, except the string 'X', is not allowed in your trigger (e.g., IF UPPER (job_title) = 'PROGRAMMER' THEN v_min_sal = 800 ...).

·       If the job title cannot be found from the MINIMUM_SALARY table, the job title is considered as "X". (e.g., the job title "TBA" is not in the MINIMUM_SALARY table, you need to check whether the salary is equal to or greater than the minimum salary for UPPER(job_title) = 'X'.)

·       If the salary is equal to or greater than the minimum salary of the corresponding job, your trigger does not change anything.

·       If the salary is less than the minimum salary of the corresponding job title, your trigger increases the salary to the minimum salary of the corresponding job title.

·       No temporary table/view/procedure/function is allowed in your trigger.

·       To avoid a mutating table error, please take a look at examples on page 8 of class handout 8. (Hint: you cannot use some INSERT/UPDATE statements to modify the EMPLOYEE table in your trigger.)

• You will receive 0 points if you use a different table (e.g., different table names, column names, or data types) in your trigger.

·       You will receive 0 points if you use a different trigger name.