Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trigger for validation of salary updated in emp table is within range

1061780Dec 23 2013 — edited Dec 24 2013

Hi,

I need some help in correcting my fault in the below trigger as it is not working as expected.

. trigger should be invoked if while modifying the  sal in EMP table, it is not in the valid range as specified in the SALGRADE table (between lowest  losal and highest  hisal)


salgrade table

Column NameData TypeNullableDefaultPrimary Key
GRADENUMBER(2,0)Yes - -
LOSALNUMBERYes - -
HISALNUMBERYes - -


I have created a trigger


create or replace trigger tr_validate_sal

before

insert or update on emp

for each row

declare

cursor c is select losal,hisal from salgrade;

begin

for i in c

loop

if :new.sal < i.losal and :new.sal > i.hisal

then

raise_application_error(-20400,'sal out of range');

end if;

end loop;

end;

after creating this trigger also i am able to insert or update salary not in range of salgrade (losal and hisal). please help.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 21 2014
Added on Dec 23 2013
5 comments
485 views