Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    3

    Unanswered: instead of trigger not working

    Here's the view code:

    create or replace view emp_mgr_vu as
    select a.ename, a.job, b.ename Manager
    from emp a, emp b
    where a.mgr = b.empno(+)

    Here's the trigger code:

    create or replace trigger upd_emp_mgr_iou
    instead of update on emp_mgr_vu
    declare
    emp_id number;
    mgr_id number;
    begin
    select empno into emp_id from emp where ename = :NEW.ename;
    select mgr into mgr_id from emp where ename = :NEW.Manager;
    update emp
    set mgr = mgr_id
    where empno = emp_id;
    end;

    Here's a before picture of the view:

    SQL> select * from emp_mgr_vu;

    ENAME JOB MANAGER
    ---------- --------- ----------
    SMITH SALESMAN BLAKE
    ALLEN SALESMAN BLAKE
    WARD SALESMAN BLAKE
    JONES MANAGER KING
    ...

    Here's an update statement against the veiw:

    SQL> update emp_mgr_vu
    2 set Manager = 'KING'
    3 where ename = 'SMITH';

    1 row updated.

    SQL> commit;

    Commit complete.

    Here's an after picture of the view:

    SQL> select * from emp_mgr_vu;

    ENAME JOB MANAGER
    ---------- --------- ----------
    SMITH SALESMAN
    ALLEN SALESMAN BLAKE
    WARD SALESMAN BLAKE
    JONES MANAGER KING

    What's the problem, why is it nulling out the manager?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: instead of trigger not working

    The problem is this line of the trigger:

    select mgr into mgr_id from emp where ename = :NEW.Manager;

    This is getting the ID of the new manager's manager!

    Should be:

    select empno into mgr_id from emp where ename = :NEW.Manager;

  3. #3
    Join Date
    Feb 2003
    Posts
    3

    Re: instead of trigger not working

    grrrrr...I hate when it's something so stupid

    Thanks Andrew

Posting Permissions

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