please help me in solving my problem.Its really urgent..
I have to insert data in a table employees.I want that if the salary of the employee to be inserted is greater than the maximum salary or is less than the minimum salary then the data should not be inserted in employees table.Instead it shoul be inserted in another table named 'test'. I have to create a trigger for this.
I created trigger as following:
create or replace trigger trig
before insert or update of EMPLOYEE_ID,
for each row
select min(salary),max(salary) into minsal,maxsal from employees;
if (:new.salary<minsal or :new.salary>maxsal)
--raise_application_error(-20500,'salary out of range');
insert into test
if now I insert some data in the table employees such that salary is greater than max salary,then it gets inserted in test but gets also inserted in employees.I dont wnt it to be inserted in employees. If i raise application error then only error is displayed and nothing else is done.
Please help me as soon as possible.