DROP TABLE SALS;
CREATE TABLE SALS
(JOB VARCHAR2(9) primary key,
MINSAL NUMBER(7,2),
MAXSAL NUMBER(7,2)
);
INSERT INTO SALS VALUES ('
CLERK', 800, 1300);
INSERT INTO SALS VALUES ('
ANALYST', 3000, 3500);
INSERT INTO SALS VALUES ('
SALESMAN', 1250, 1600);
INSERT INTO SALS VALUES ('
MANAGER', 2450, 2975);
INSERT INTO SALS VALUES ('
PRESIDENT', 5000, 5500);
create
or replace trigger check_salary_EMP
after insert
or update
of SAL, JOB
on EMP
for each row
when (new.JOB != '
PRESIDENT')
declare
minsal number;
maxsal number;
begin
-- retrieve minimum
and maximum salary
for JOB
select MINSAL, MAXSAL into minsal, maxsal from SALS
where JOB = :new.JOB;
--
If the new salary has been decreased
or does
not lie
-- within the salary range
raise an
exception
if :new.SAL < minsal
or :new.SAL > maxsal
then
raise_application_error(-20225, '
Salary range exceeded');
elsif :new.SAL < :old.SAL
then
raise_application_error(-20230, '
Salary has been decreased');
elsif :new.SAL > 1.1*:old.SAL
then
raise_application_error(-20235, '
More than 10% salary increase');
end if;
end;