transplantedNYr 0 Junior Poster in Training

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;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.