Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Unanswered: AFTER INSERT mutating trigger

    Hi,

    It's been a few years since I last used PL\SQL and I'm having trouble writing an after insert trigger.

    Basically, I have a table, DELCHECK, with 2 columns, COL1 which is a sequence number and COL2 which is a category.

    COL1 COL2
    1 A
    2 A
    3 A
    4 A
    1 B
    2 B

    There can be a maximum of 4 records per category i.e. COL2. If I try to insert a 5th record, the record with the lowest sequence number i.e. COL1 should be deleted.

    The trigger I've written is as follows

    CREATE OR REPLACE TRIGGER pi_check
    AFTER INSERT
    ON DELCHECK
    FOR EACH ROW
    DECLARE
    CURSOR C1 IS SELECT COUNT(*) CNT, COL2 FROM DELCHECK GROUP BY COL2; --Fetch number of records for each category
    CURSOR C2 IS SELECT MIN(COL1) MINVAL, COL2 FROM DELCHECK GROUP BY COL2; --Fetch the minimum sequence number for each category
    C1_REC C1%ROWTYPE;
    C2_REC C2%ROWTYPE;
    BEGIN
    OPEN C1;
    FETCH C1 INTO C1_REC;
    OPEN C2;
    FETCH C2 INTO C2_REC;
    IF :new.COL2 = C1_REC.COL2 AND C1_REC.CNT > 4
    THEN DELETE FROM DELCHECK WHERE :new.COL2 = C2_REC.COL2 AND COL1 = C2_REC.MINVAL;
    END IF;
    CLOSE C1;
    CLOSE C2;
    END;

    It complies ok but when I attempt to insert a record into the table, I get the following error

    Error report:
    SQL Error: ORA-04091: table SOURCE.DELCHECK is mutating, trigger/function may not see it
    ORA-06512: at "SOURCE.PI_CHECK", line 2
    ORA-06512: at "SOURCE.PI_CHECK", line 7
    ORA-04088: error during execution of trigger 'SOURCE.PI_CHECK'
    04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
    *Cause: A trigger (or a user defined plsql function that is referenced in
    this statement) attempted to look at (or modify) a table that was
    in the middle of being modified by the statement which fired it.
    *Action: Rewrite the trigger (or function) so it does not read that table.

    Any help in getting the trigger up and running would be appreciated!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    And what did you not understand about the solution:
    Code:
    *Action: Rewrite the trigger (or function) so it does not read that table.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    trigger can't do SQL against same table upon which the trigger is based.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2013
    Posts
    3
    The problem is that there is only one table. I can't create temp tables in the database.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why must solution be a trigger?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2013
    Posts
    3
    It doesn't have to be.. Incidentally, I tried swapping the tables over with views but predictably, same error.
    Any suggestions on how best to approach this?

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Vrooon View Post
    It doesn't have to be.. Incidentally, I tried swapping the tables over with views but predictably, same error.
    Any suggestions on how best to approach this?
    With views you could use "INSTEAD OF" triggers.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by Vrooon View Post
    It doesn't have to be.. Incidentally, I tried swapping the tables over with views but predictably, same error.
    Any suggestions on how best to approach this?
    do not do it in a trigger
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

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