What is the proper way to create an update trigger with a variable getting value from the same table?

Question

I'm creating a update trigger where an employee can never have a salary that is greater than the president's. However I need to subquery the president's salary for comparison and the "new" updated employee's salary.

I originally had the the subquery using from the from employees table but had to make a new table because of the mutating table problem. I don't think creating a new table is plausible solution for a real implementation.

Is there a way I can save the president's salary without creating a new table?

CREATE OR REPLACE TRIGGER prevent_salary 
BEFORE UPDATE ON employees
FOR EACH ROW 
    declare pres_sal number(8,2);
BEGIN 
    select salary into pres_sal from employees_salary where job_id='AD_PRES';--employees_salary was employees but that gives mutating error

    IF (:new.salary > pres_sal)
        THEN UPDATE employees 
        SET salary = :old.salary
        WHERE employee_id = :old.employee_id;
    END IF;
END;

score 1 · Answer 1 · answered Nov 16 '17 at 23:08

You may try this :

CREATE OR REPLACE TRIGGER prevent_salary 
BEFORE UPDATE ON employees
FOR EACH ROW 
    declare pres_sal number(8,2);
BEGIN 
    select salary into pres_sal from employees_salary where job_id='AD_PRES';--employees_salary was employees but that gives mutating error

    IF (:new.salary > pres_sal)
     Raise_Application_Error (-20101, 'An employee''s salary couldn''t exceed president''s !');
    END IF;
END;

score 1 · Accepted Answer · answered Nov 16 '17 at 23:12

One way to do it is to save off the president's salary in a BEFORE STATEMENT trigger and then use that in the FOR EACH ROW trigger.

"Compound Triggers", which have been around at least since version 11.1, offer a nice way to do that all in one place.

Here is an example:

CREATE OR REPLACE TRIGGER prevent_salary 
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER

  pres_sal NUMBER;

BEFORE STATEMENT IS
BEGIN
    select salary 
    into pres_sal 
    from employees 
    where job_id='AD_PRES';
END BEFORE STATEMENT;

BEFORE EACH ROW IS 
BEGIN 
    :new.salary := least(:new.salary, pres_sal);
END BEFORE EACH ROW;

END prevent_salary;

What is the proper way to create an update trigger with a variable getting value from the same table?

2 Answers2