Thread: Trigger Error

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    69
    Rep Power
    12

    Angry Trigger Error


    Hi There,
    I have created a trigger on a table which should update value in another table. Trigger is created without any error but while executing I get mutate error!
    Here is code -

    CREATE OR REPLACE TRIGGER holiday_trig
    AFTER UPDATE OR DELETE OF status
    ON holidays
    FOR EACH ROW
    DECLARE
    totHolidays number;
    BEGIN
    IF UPDATING THEN
    SELECT SUM(workingdays) INTO totHolidays
    FROM holidays
    WHERE id = ld.id;

    UPDATE ssgemployee
    SET holidaystaken = totHolidays
    WHERE id = ld.id;
    ELSIF DELETING THEN
    UPDATE ssgemployee
    SET holidaystaken = holidaystaken - ld.workingdays
    WHERE id = ld.id;
    END IF;
    END;
    /

    Could some one please tell me where I am going wrong?

    Error code is -

    ERROR at line 1:
    ORA-04091: table xxx.HOLIDAYS is mutating, trigger/function may not see it
    ORA-06512: at "xxx.HOLIDAY_TRIG", line 5
    ORA-04088: error during execution of trigger 'xxx.HOLIDAY_TRIG'
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    64
    Rep Power
    11
    the following is from oracle:

    A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error. Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering from.

    i didn't try it but you can try and use autonomous transaction to do the updates (i'm no so sure it will work though).

IMN logo majestic logo threadwatch logo seochat tools logo