Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unanswered: Mutating Table, Inconsistent behaviour?

    Problem is message: 'Mutating Table blablabla'. Already read a lot about it and also used workaround with multiple triggers and temptables etc.
    Case: I have a parent table and a child table. With a constraint from child to parent. When I want to delete the child there has to be an update in the parent-table. This was working already on two other tables(parent, child) . But now i get the mutating table-error again. I think the relationship between the tables where i have the problem and the relationship between the tables where i don't have the problem is different. Both have CASCADE on delete action.

    If anyone has any idea, what could cause the problem i would be thankful. I have already used workarounds and know what the mutating table problem means.

    Thanks beforehand!

    CDK

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

    Re: Mutating Table, Inconsistent behaviour?

    Originally posted by thepercival
    Problem is message: 'Mutating Table blablabla'. Already read a lot about it and also used workaround with multiple triggers and temptables etc.
    Case: I have a parent table and a child table. With a constraint from child to parent. When I want to delete the child there has to be an update in the parent-table. This was working already on two other tables(parent, child) . But now i get the mutating table-error again. I think the relationship between the tables where i have the problem and the relationship between the tables where i don't have the problem is different. Both have CASCADE on delete action.

    If anyone has any idea, what could cause the problem i would be thankful. I have already used workarounds and know what the mutating table problem means.

    Thanks beforehand!

    CDK
    From the Application Developer's Guide:

    A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.

    The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.

    This restriction applies to all triggers that use the FOR EACH ROW clause, and statement triggers that are fired as the result of a DELETE CASCADE.

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    read my problem

    My problem is that it works on next case:

    CREATE OR REPLACE TRIGGER ish.JpExtCalcDelete
    BEFORE DELETE ON ish.JpExt
    REFERENCING OLD AS old_JpExt
    FOR EACH ROW
    DECLARE
    Mutating_table EXCEPTION;
    PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
    BEGIN
    UPDATE ish.JobPlan
    SET JpExtCalc = JpExtCalc - ld_JpExt.JpeCost
    WHERE JpId = ld_JpExt.JpeJpId
    ;
    EXCEPTION
    WHEN Mutating_table THEN
    NULL;
    END;
    /

    jobplan is parent

    and not on this case:

    CREATE OR REPLACE TRIGGER ish.WoDelete
    BEFORE DELETE ON ish.WorkOrder
    REFERENCING OLD AS old_WorkOrder
    FOR EACH ROW
    DECLARE
    Mutating_table EXCEPTION;
    PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
    BEGIN
    IF ( ld_WorkOrder.WoPrjId IS NOT NULL ) THEN
    UPDATE ish.Project
    SET PrjMatCalc = PrjMatCalc - ld_WorkOrder.WoMatCalc
    WHERE PrjId = ld_WorkOrder.WoPrjId
    ;
    END IF;
    EXCEPTION
    WHEN Mutating_table THEN
    NULL;

    END;
    /

    here project is the parent
    while they seem to have the same relationship

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

    Re: read my problem

    Originally posted by thepercival
    My problem is that it works on next case:

    CREATE OR REPLACE TRIGGER ish.JpExtCalcDelete
    BEFORE DELETE ON ish.JpExt
    REFERENCING OLD AS old_JpExt
    FOR EACH ROW
    DECLARE
    Mutating_table EXCEPTION;
    PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
    BEGIN
    UPDATE ish.JobPlan
    SET JpExtCalc = JpExtCalc - ld_JpExt.JpeCost
    WHERE JpId = ld_JpExt.JpeJpId
    ;
    EXCEPTION
    WHEN Mutating_table THEN
    NULL;
    END;
    /

    jobplan is parent

    and not on this case:

    CREATE OR REPLACE TRIGGER ish.WoDelete
    BEFORE DELETE ON ish.WorkOrder
    REFERENCING OLD AS old_WorkOrder
    FOR EACH ROW
    DECLARE
    Mutating_table EXCEPTION;
    PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
    BEGIN
    IF ( ld_WorkOrder.WoPrjId IS NOT NULL ) THEN
    UPDATE ish.Project
    SET PrjMatCalc = PrjMatCalc - ld_WorkOrder.WoMatCalc
    WHERE PrjId = ld_WorkOrder.WoPrjId
    ;
    END IF;
    EXCEPTION
    WHEN Mutating_table THEN
    NULL;

    END;
    /

    here project is the parent
    while they seem to have the same relationship
    1) What was the triggering statement in each case

    2) How can you tell that you get the error, when your trigger actually sweeps it under the carpet and pretends nothing happened?

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    more details

    1 ) The triggering statement was a deletion of the child in both cases.
    (deletion of the parent throws both times an exception while deletion of the child throws an exception only in case 2(this is the problem) )

    2 ) What you say is actually right. If I delete a record in JpExt(child) in the first case the column JpExtCalc should be subtracted with the column JpeCost(column of deleted child record) and will not throw an exception. When you delete a parent record (JobPlan ), through the cascade delete the child will be deleted and this will throw an exception, because of the fired trigger. But in case 2 there will also be thrown an exception on a deletion of the child, which is not what should happen.

    If it is not clear i want to give more details.

    beforehand thanks CDK

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

    Re: more details

    Originally posted by thepercival
    1 ) The triggering statement was a deletion of the child in both cases.
    (deletion of the parent throws both times an exception while deletion of the child throws an exception only in case 2(this is the problem) )

    2 ) What you say is actually right. If I delete a record in JpExt(child) in the first case the column JpExtCalc should be subtracted with the column JpeCost(column of deleted child record) and will not throw an exception. When you delete a parent record (JobPlan ), through the cascade delete the child will be deleted and this will throw an exception, because of the fired trigger. But in case 2 there will also be thrown an exception on a deletion of the child, which is not what should happen.

    If it is not clear i want to give more details.

    beforehand thanks CDK
    I think you will have to post the FULL DDL for each of the 4 tables, including all constraints.

    Is there by any chance also a foreign key on Project referencing WorkOrder, with ON DELETE CASCADE? That would cause a mutating error, because the delete of WorkOrder would cascade to Project, but then the trigger tries to update Project.

  7. #7
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    full ddl

    i will post a full ddl

  8. #8
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    jobplan (parent) constraints

    see file
    Attached Thumbnails Attached Thumbnails jobplan constraints.jpg  

  9. #9
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    jpext (child) constraint

    see pic
    Attached Thumbnails Attached Thumbnails jpext constraints.jpg  

  10. #10
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    project (parent) constraint

    see pic
    Attached Thumbnails Attached Thumbnails project constraints.jpg  

  11. #11
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    workorder ( child ) constraints

    see pic
    Attached Thumbnails Attached Thumbnails workorder constraints.jpg  

  12. #12
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    triggers again

    see pic
    Attached Thumbnails Attached Thumbnails triggers on children.jpg  

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

    Re: full ddl

    Originally posted by thepercival
    i will post a full ddl
    Sorry, but I can't look at those screen dumps without getting a blue screen and having to reboot my PC! Maybe later at home I can.

    Is the a foreign key constraint on Project referencing WorkOrder with ON DELETE CASCADE? Seems unlikely, but....?

  14. #14
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    no

    no that's not the case. I thought maybe project has a cascade delete to one of his parent. And this parent has a cascade delete to workorder. but i don't know if this results into a mutating table error. our database model is not optimised. But at this moment we can't optimise because of economical and customer reasons.

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

    Re: no

    Originally posted by thepercival
    no that's not the case. I thought maybe project has a cascade delete to one of his parent. And this parent has a cascade delete to workorder. but i don't know if this results into a mutating table error. our database model is not optimised. But at this moment we can't optimise because of economical and customer reasons.
    It seems to me very likely you have such a loop in your constraints - judging by the number of foreign keys you have with ON DELETE CASCADE. I have never seen ON DELETE CASCADE (and SET NULL) used so liberally in a database before!

    This seems highly suspect to me. Normally most foreign keys are set to RESTRICT (the default). This means that if someone tries to delete an employee (e.g.) they are likely to be prevented due to a constraint like "cannot delete employee while related projects exist".

    In your database, a user can delete an employee and the database just says (metaphorically) "OK, you want to delete that employee, so I'll also delete any Projects he has ever worked on, managed, approved, requested, ... Also I'll delete any Work Orders under those Projects, plus any Work Orders where that employee was the project manager, plus... etc. etc. etc."

    (That is based on my interpretation of the various CASCADE FKs I can see in your database).

    This is scary! One false move and the user can wipe out half the database!

    I would only ever use ON DELETE CASCADE for child tables that are like sub-components of the parent table - i.e. where if you wanted to delete the parent it is obvious that you mean to delete the children too. Valid examples may be:

    EmployeeSalaryHistory of Employee
    OrderLine within Order

    I would NEVER use ON DELETE CASCADE for relationships like:

    Project managed by Employee
    Employee works in Department

    Just imagine! Some manager decides to restructure the company, and deletes the HR department (wants to consolidate HR into Payroll). Hits delete and instead of getting an error message, he successfully deletes the HR department, all the employees who currently work in HR, all the projects those employees manage, etc. etc.

    I suggest you need to review your foreign key design ASAP!!!

Posting Permissions

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