Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012
    Posts
    7

    Unanswered: Issue with Triggers.

    Mutating error. I know its because I am performing a sql query to a table that I is the same as my trigger function, but what else can I do????
    I need to have a trigger that will print out that query and remove the project code from the project table if no one is assigned to it

    (ERROR IS AFTER CODE)

    Here is my code:

    DROP TABLE ASSIGNMENT CASCADE CONSTRAINTS;
    DROP TABLE PROJECT CASCADE CONSTRAINTS;

    CREATE TABLE PROJECT
    (
    Code NUMBER(3),
    Name VARCHAR2(30),

    CONSTRAINT PK_PROJECT PRIMARY KEY (Code)
    );

    CREATE TABLE ASSIGNMENT
    (
    ID CHAR(5),
    Name VARCHAR2(20),
    ProjCode NUMBER(3),
    Hours NUMBER(*,0) CHECK (Hours>0),

    CONSTRAINT PK_ASSIGNMENT PRIMARY KEY (ID, ProjCode),

    CONSTRAINT FK_ASSIGNMENT_PROJECT FOREIGN KEY (ProjCode) REFERENCES PROJECT (Code)
    );

    INSERT INTO PROJECT VALUES ( 101, 'Alpha' );
    INSERT INTO PROJECT VALUES ( 222, 'Beta' );
    INSERT INTO PROJECT VALUES ( 355, 'Gamma' );
    INSERT INTO PROJECT VALUES ( 973, 'Delta' );

    INSERT INTO ASSIGNMENT VALUES ( '55055', 'Smith', 101, 20 );
    INSERT INTO ASSIGNMENT VALUES ( '55055', 'Smith', 222, 10 );
    INSERT INTO ASSIGNMENT VALUES ( '39002', 'Hammond', 973, 25 );
    INSERT INTO ASSIGNMENT VALUES ( '00001', 'Preston', 355, 5 );
    INSERT INTO ASSIGNMENT VALUES ( '10000', 'Logan', 355, 5 );
    INSERT INTO ASSIGNMENT VALUES ( '00777', 'Bond', 222, 20 );

    SELECT * FROM PROJECT;
    SELECT * FROM ASSIGNMENT;



    CREATE or REPLACE TRIGGER project_removal_trigger
    AFTER DELETE or UPDATE
    ON ASSIGNMENT
    FOR EACH ROW

    DECLARE
    print project.code%type;

    BEGIN
    SELECT projcode INTO print
    FROM ASSIGNMENT group by projcode having count(name) > 0;
    DBMS_OUTPUT.PUT_LINE(print);

    END;
    /

    update assignment set projcode = 123 where projcode = 355;
    -------------------------------------------------------------
    Error starting at line 57 in command:
    update assignment set projcode = 123 where projcode = 355
    Error report:
    SQL Error: ORA-04091: table MPATEL95.ASSIGNMENT is mutating, trigger/function may not see it
    ORA-06512: at "MPATEL95.PROJECT_REMOVAL_TRIGGER", line 5
    ORA-04088: error during execution of trigger 'MPATEL95.PROJECT_REMOVAL_TRIGGER'
    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
    Last edited by getmet; 11-11-12 at 20:50.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    "design" is fatally flawed

    >DBMS_OUTPUT.PUT_LINE(print);
    is 100% useless in Production environment
    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.

  3. #3
    Join Date
    Nov 2012
    Posts
    7
    I fixed it a bit but how can I have a trigger when i need to use that very same table to remove a row in another table???

    I want to find a way to do it without creating a copy table

    Any ideas????

    I even noticed that I cannot update projCode from the assignment table because it is a foreign key. Just confused


    DROP TABLE ASSIGNMENT CASCADE CONSTRAINTS;
    DROP TABLE PROJECT CASCADE CONSTRAINTS;

    CREATE TABLE PROJECT
    (
    Code NUMBER(3),
    Name VARCHAR2(30),

    CONSTRAINT PK_PROJECT PRIMARY KEY (Code)
    );

    CREATE TABLE ASSIGNMENT
    (
    ID CHAR(5),
    Name VARCHAR2(20),
    ProjCode NUMBER(3),
    Hours NUMBER(*,0) CHECK (Hours>0),

    CONSTRAINT PK_ASSIGNMENT PRIMARY KEY (ID, ProjCode),

    CONSTRAINT FK_ASSIGNMENT_PROJECT FOREIGN KEY (ProjCode) REFERENCES PROJECT (Code)
    );

    INSERT INTO PROJECT VALUES ( 101, 'Alpha' );
    INSERT INTO PROJECT VALUES ( 222, 'Beta' );
    INSERT INTO PROJECT VALUES ( 355, 'Gamma' );
    INSERT INTO PROJECT VALUES ( 973, 'Delta' );

    INSERT INTO ASSIGNMENT VALUES ( '55055', 'Smith', 101, 20 );
    INSERT INTO ASSIGNMENT VALUES ( '55055', 'Smith', 222, 10 );
    INSERT INTO ASSIGNMENT VALUES ( '39002', 'Hammond', 973, 25 );
    INSERT INTO ASSIGNMENT VALUES ( '00001', 'Preston', 355, 5 );
    INSERT INTO ASSIGNMENT VALUES ( '10000', 'Logan', 355, 5 );
    INSERT INTO ASSIGNMENT VALUES ( '00777', 'Bond', 222, 20 );

    SELECT * FROM PROJECT;
    SELECT * FROM ASSIGNMENT;



    CREATE or REPLACE TRIGGER project_removal_trigger
    AFTER DELETE or UPDATE
    ON ASSIGNMENT
    FOR EACH ROW

    DECLARE
    print project.code%type;

    BEGIN
    DELETE FROM PROJECT WHERE code NOT IN (SELECT Projcode from assignment group by projcode having count(name) > 0);
    END;
    /

    --update assignment set projcode = 123 where projcode = 355;
    Delete from assignment where name = 'Hammond';

  4. #4
    Join Date
    Nov 2012
    Posts
    7
    IM AN IDIOT! figured it out.... Thanks for the reply. illtry to close this thread

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you solved it then put in your fix. Help other people who are looking for a solution. Thanks
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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