Results 1 to 4 of 4

Thread: triggers

  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Unanswered: triggers

    hello..
    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,
    FIRST_NAME,
    LAST_NAME,
    EMAIL,
    PHONE_NUMBER,
    HIRE_DATE,
    JOB_ID,
    SALARY,
    COMMISSION_PCT,
    MANAGER_ID,
    DEPARTMENT_ID
    on employees
    for each row
    declare
    minsal number;
    maxsal number;
    BEGIN
    select min(salary),max(salary) into minsal,maxsal from employees;
    if (:new.salary<minsal or :new.salary>maxsal)
    then
    --raise_application_error(-20500,'salary out of range');
    insert into test
    values(:new.EMPLOYEE_ID,
    :new.FIRST_NAME,
    :new.LAST_NAME,
    :new.EMAIL,
    :new.PHONE_NUMBER,
    :new.HIRE_DATE,
    :new.JOB_ID,
    :new.SALARY,
    :new.COMMISSION_PCT,
    :new.MANAGER_ID,
    :new.DEPARTMENT_ID);

    end if;
    end;


    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This looks like a homework question! So just a (big) clue: you would need to use an autonomous transaction to allow the insert into TEST to complete even though the insert into EMPLOYEES failed.

    However, using autonomous transactions in triggers is almost always a cause of bugs!

  3. #3
    Join Date
    Nov 2008
    Posts
    2
    hello tony..
    i tried using autonomous transaction..but it didnt worked..can u plz elaborate it..
    i wud be really thankful to u..


    Mani

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What happened when you tried? Please show error or describe result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •