I have to create a trigger on an Employee table. If an INSERT or UPDATE statement is issued for the Employee table the trigger launches and makes sure that the value of 'salary' field meets the criteria in the job_min_sal table. After trying over and over I got a mutating table error and now am very frustrated and don't know what to do.
JOB VARCHAR2(50) PRIMARY KEY
MIN_SAL NUMBER(7,2) NOT NULL
The JOB_MIN_SAL table is populated with a variety of job titles and salaries. I am confused working with my trigger and wondering if I could get some assistance where to go from here
CREATE OR REPLACE TRIGGER employee_job_salary
BEFORE INSERT OR UPDATE OF SALARY on employee
FOR EACH ROW
WHERE UPPER(job) = UPPER(:NEW.job);
I know I am really far off I am just looking for help as for what this requires and what steps I need to take to get this. Thanks!
The EMPLOYEE table:
i am supposing you are doing something like comparing new salary with min salary criterion and update only if :new.SALARY >= v_salary
what are you doing if this is not met, are u trapping an exception or just ignoring the error or returning an error code to debug.
post more info
CREATE TABLE job_min_salary ( job VARCHAR2(50) PRIMARY KEY, min_sal NUMBER(7,2) NOT NULL ); INSERT INTO job_min_salary VALUES('CEO','100'); -- 1 rows inserted. CREATE TABLE employee ( employee_id NUMBER(4), employee_name VARCHAR2(20), job VARCHAR2(50), manager_id NUMBER(4), hire_date DATE, salary NUMBER(9), commision NUMBER(9), department_id NUMBER(4) ); INSERT INTO employee VALUES(1, 'Name', 'CEO', 1, TO_DATE('2000-01-01', 'YYYY-MM-DD'), 80, 80, 1); -- 1 rows inserted. CREATE OR REPLACE TRIGGER employee_job_salary BEFORE INSERT OR UPDATE OF salary ON employee FOR EACH ROW DECLARE v_salary NUMBER(1); BEGIN SELECT 1 INTO v_salary FROM job_min_salary WHERE UPPER(job) = UPPER(:NEW.job) AND :NEW.salary >= min_sal; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20999, 'Salary value is too low for given job'); END; -- TRIGGER EMPLOYEE_JOB_SALARY compiled SELECT * FROM employee; -- 1 Name CEO 1 2000-01-01 00:00:00 80 80 1 UPDATE employee SET salary = 10 WHERE job = 'CEO'; -- ORA-20999: Salary value is too low for given job UPDATE employee SET salary = 100 WHERE job = 'CEO'; -- 1 rows updated. SELECT * FROM employee; -- 1 Name CEO 1 2000-01-01 00:00:00 100 80 1