Triggers in sql

[ksultan]

I have a trigger(that prohibits the attribution of more than 10000 dollars to a worker that is not manager nor president) that i want to modify.

CREATE OR REPLACE TRIGGER restrict_salary
BEFORE UPDATE ON EMP
FOR EACH ROW
WHEN (new.sal > 10000)
DECLARE
job ename.job%type;
BEGIN
select job into job from ename where empid = :new.id;
IF job not in ('MANAGER', 'PRESIDENT') THEN
raise_application_error (-20002, 'Error that fit''s on page');
END IF;
END restrict_salary;
/

Now i want to prohibit an increase of more than 9% on the salary. How should i go about it. Thank you

[ORAfreak]

Hi,

OK, if I understood well your trigger must prohibit increase of salary more than 9% for all emploies
exept for president and manager.
Than this is the trigger you need:

CREATE OR REPLACE TRIGGER restrict_salary
BEFORE UPDATE ON EMP
FOR EACH ROW
WHEN (new.sal > old.sal+(old.sal*0.09))
DECLARE
    job ename.job%type;
BEGIN
    select job into job from ename where empid = :new.id;
    IF job not in ('MANAGER', 'PRESIDENT') THEN
        raise_application_error (-20002, 'Error that fit''s on page');
    END IF;
END restrict_salary;
/