find mistake... Trigger

[Deven Oza]

Hi everyone,

I want to Write a trigger that will limit a user to enter only numeric values between 100 and 200 into a field called bonus for the table empBonus. and I have created trigger so please check for me that where I made mistake.
thank you.

trigger as under which i wrote.

create or replace trigger bonus_chk

before insert on empbonus
for each row

declare
emp_id number(3) := &emp_id;
emp_name varchar2(20) := '&emp_name';
emp_sal number(5) := &emp_sal;
emp_bonus number(3) := &emp_bonus;

begin

if emp_bonus < 100 or &emp_bonus > 200 then
raise_applicati on_error(-20001,' Employee's bonus must be in between 100 to 200);

else
insert into empbonus
values
(&emp_id, '&emp_name','&e mp_sal','&emp_b onus');

dbms_output.put _line('One row created successfully !');

end if;

end;

[nunnasujatha]

what exactly the problem u r getting.

i have some code probably that can help u.

create or replace trigger suj_range_check
2 before insert on employee
3 for each row
4 declare
5 empid number(2):= :new.empid;
6 sal number(5):= :new.sal;
7 begin
8 if (sal>7000 or sal<500) then
9 raise_applicati on_error(-20002,'employee sal must b in b/w 500 and 7000');
10 else
11 insert into employee values(empid,sa l);
12 dbms_output.put _line('1 row created');
13 end if;
14 end;
15 /

Trigger created.

SQL> insert into employee values(8,400);
insert into employee values(8,400)
*
ERROR at line 1:
ORA-20002: employee sal must b in b/w 500 and 7000
ORA-06512: at "SUJJ.SUJ_RANGE _CHECK", line 6
ORA-04088: error during execution of trigger 'SUJJ.SUJ_RANGE _CHECK'

it's checking the range .

[nunnasujatha]

if it is giving the following oracle error

ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-00036: maximum number of recursive SQL levels (50) exceeded

then the problem
is with insert
inside the trigger body u r trying to insert values into the table it again calls the trigger(before insert).so infinte calls.

[nunnasujatha]

for the first code that i have given it's checking the range correctly but for insertion it's firing the trigger repeatedly.so
wat i did is i tried with after insert ,and if the sal is not in the given range i tried to delete it from the table (as it is already inserted).

SQL> create or replace trigger suj_range_check
2 after insert on employee
3 for each row
4 declare
5 empid number(2):= :new.empid;
6 sal number(5):= :new.sal;
7 begin
8 if (sal>7000 or sal<500) then
9 raise_applicati on_error(-20002,'employee sal must b in b/w 500 and 7000');
10 delete from employee where empid=:new.empi d;
11 else
12 dbms_output.put _line('1 row created');
13 end if;
14 end;
15 /

Trigger created.

SQL> insert into employee values(8,400);
insert into employee values(8,400)
*
ERROR at line 1:
ORA-20002: employee sal must b in b/w 500 and 7000
ORA-06512: at "SUJJ.SUJ_RANGE _CHECK", line 6
ORA-04088: error during execution of trigger 'SUJJ.SUJ_RANGE _CHECK'


SQL> insert into employee values(8,5000);
1 row created

1 row created.


hope this helps u

[suvam]

Pls refer to ur original code with Trigger "suj_range_chec k" .
In the body remove the Else part as it will automatically execute if the trigger does not raise Raise_Applicati on_Error .
Rest of the things r OK .