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;