I am trying to create a trigger, but it seems whenever the actual update occurs I am getting rewrite the trigger error message because of a mutating error and something like as table may not see value.
I worked on this a bit and started with the second set of code below, and worked every possible nuanced changed that would not change the logic behind this.
I can cofnirm that this mutates at the assignment portion of the code, because I was able to get printouts of everything prior to the update assignment in the code below.
The examples from my class only use a count(*) * or uses the actual table that we are writing the trigger for. In my try below I am not using the employee table except for the update call to employee.
Somehow I need to change the value if the new employee or update to employee salary is less than the default value, with the same default value.
CREATE OR REPLACE TRIGGER emp_sal_trg AFTER UPDATE OR INSERT OF salary ON employee FOR EACH ROW DECLARE v_salary NUMBER; v_default NUMBER; v_new NUMBER; BEGIN select min_sal into v_default from job_min_sal where UPPER(job) = UPPER(:NEW.job); v_salary := :NEW.salary - v_default; DBMS_OUTPUT.PUT_LINE(:NEW.salary); DBMS_OUTPUT.PUT_LINE(v_salary); if v_salary > 0 then DBMS_OUTPUT.PUT_LINE('No action taken'); else begin UPDATE employee SET salary = v_default where employee_id = :NEW.employee_id; DBMS_OUTPUT.PUT_LINE('worked?'); end; end if; exception when others then UPDATE employee SET salary = v_default where employee_id = :NEW.employee_id; DBMS_OUTPUT.PUT_LINE('huh'); END;
CREATE OR REPLACE TRIGGER emp_job_sal_trg AFTER UPDATE OR INSERT ON employee FOR EACH ROW DECLARE v_salary NUMBER; v_default NUMBER; BEGIN select salary into v_salary from job_min_sal where job = :NEW.department_name; if :NEW.Salary < v_salary then v_default := v_salary; UPDATE employee SET salary = v_default where employee_id = :NEW.employee_id; end if; END;