Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    5

    Unanswered: Trigger Explanation

    Can anyone explain why I get this error ?

    ERROR at line 1:
    ORA-01000: maximum open cursors exceeded
    ORA-06512: at "DS15.MANAGERRAISE", line 3
    ORA-04088: error during execution of trigger 'DS15.MANAGERRAISE'

    When I do this ?

    SET ECHO ON

    CREATE OR REPLACE TRIGGER ManagerRaise
    AFTER UPDATE OF Salary ON Employees
    BEGIN
    FOR managerCurs IN (SELECT D#, Manager FROM Department) LOOP
    UPDATE Employees
    SET Salary = (SELECT MAX(Salary) FROM Employees WHERE E# IN (SELECT E# FROM Works WHERE D# = managerCurs.D#))
    WHERE E# = managerCurs.Manager;
    END LOOP;
    END;
    /

    I started out with a row trigger, but it seemed impossible with that, so now I have this statement trigger implementation. The problem I'm trying to solve is "Whenever an employee is given a raise, their manager's salary must be increased to be at least as much if it's less."

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your trigger implements an infinite loop, because it specifies no conditions to stop. It is guaranteed to update all the managers every time it fires, even if no update is required. And every time it updates a manager, it fires again...

    This additional condition in the UPDATE may help:

    AND Salary < (SELECT MAX(Salary) FROM Employees WHERE E# IN (SELECT E# FROM Works WHERE D# = managerCurs.D#));

    Now if the manager already gets the highest salary in the department, no update will take place and so the trigger will not fire again.

    Actually, you don't need a cursor either - think sets:
    Code:
    CREATE OR REPLACE TRIGGER ManagerRaise
    AFTER UPDATE OF Salary ON Employees
    BEGIN
      UPDATE Employees M
      SET M.Salary = (SELECT MAX(Salary) FROM Employees WHERE E# IN (SELECT E# FROM Works WHERE D# = M.D#))
      WHERE M.Salary < (SELECT MAX(Salary) FROM Employees WHERE E# IN (SELECT E# FROM Works WHERE D# = M.D#));
    END;
    /

  3. #3
    Join Date
    Sep 2005
    Posts
    5
    So is there any good way, in general, you know of to update a table that that has an update trigger on it without getting into an infinite loop ? I know you can't use PRAGMA AUTONOMOUS_TRANSACTION, so row triggers are out.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by DS1984
    So is there any good way, in general, you know of to update a table that that has an update trigger on it without getting into an infinite loop ?
    Write the trigger in such a way as not to cause an infinite loop - like I just showed you!

  5. #5
    Join Date
    Sep 2005
    Posts
    5
    Yes, I understand your logic with having the AND Salary < condition. It makes prefect sense since the self triggering would not cause any further updates and firing would not go any further. However, Oracle still gets itself into an infinite loop with this implementation, for some bizarre reason. Any other ideas ?

    I was thinking of DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY procedure ?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sorry, I forgot that a statement-level trigger will fire even if no rows are affected by the statement. Try this:
    Code:
    CREATE OR REPLACE TRIGGER ManagerRaise
    AFTER UPDATE OF Salary ON Employees
    BEGIN
      FOR r IN (SELECT E# FROM Employees M
                WHERE M.Salary < (SELECT MAX(Salary)
                                  FROM   Employees E
                                  WHERE  E# IN (SELECT E# 
                                                FROM   Works 
                                                WHERE D# = M.D#
                                               )
                                 )
               )
      LOOP          
        UPDATE Employees M
        SET M.Salary = (SELECT MAX(Salary) FROM Employees WHERE E# IN (SELECT E# FROM Works WHERE D# = M.D#))
        WHERE M.E# = r.E#;
      END LOOP;
    END;
    /

Posting Permissions

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