Create a trigger called EMP_MIN_SAL_TRG on the EMPLOYEE table. When an INSERT or

Question

Create a trigger called EMP_MIN_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 table in 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) Create a table TAB_MINIMUM_SALARY as follows.

CREATE TABLE TAB_MINIMUM_SALARY

(

     JOB_TITLE       VARCHAR2(100) PRIMARY KEY,

     MINIMUM_SALARY NUMBER(7, 2) NOT NULL

);

Step 2) Populate the TAB_INIMUM_SALARY table as follows.

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

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

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

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

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

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

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

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

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

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

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

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

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

COMMIT;

Step 3) Create the trigger emp_MIN_SAL_TRG.

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

You must get the minimum salaries from the TAB_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 TAB_MINIMUM_SALARY table, the job title is considered as “X”. (e.g., the job title “TBA” is not in the TAB_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.

If you modified the EMPLOYEE table created in Assignment #1, please delete and re-populate it.

Step 4) Test your trigger.

You need to create/run some test cases to check whether the values of the SALARY column in the EMPLOYEE table meet the criteria in the TAB_MINIMUM_SALARY table in any situations. You do not need to submit your test cases.

Explanation / Answer

solution fot the sql trigger statement

CREATE OR REPLACE TRIGGER MIN_SAL_TRG

BEFORE INSERT OR UPDATE OF salary ON EMPLOYEE

FOR EACH ROW

DECLARE

v_min_sal NUMBER(1);

BEGIN

SELECT 1

INTO    v_min_sal

FROM    MINIMUM_SALARY

WHERE   UPPER(JOB_TITLE) = UPPER(:NEW.JOB_TITLE)

AND     :NEW.salary >= MIN_SALARY;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20999, 'Salary value is invalid');

END;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.