Results 1 to 5 of 5
  1. #1
    xcallmejudasx's Avatar
    xcallmejudasx is offline Senior Member
    Join Date
    Oct 2008
    Location
    Houston, TX & Flint, MI
    Posts
    609
    Rep Power
    6

    Default Mutating trigger help

    I have a trigger that will delete a row from (table)myProjects and change the (column)checkout status (Table)in projectDescriptions.

    I understand the trigger is being invalidated because the Trigger is referencing the myProjects table and trying to delete from it. This has to happen for the trigger to work(read: I can't think of another way to accomplish the same thing)

    The code for my trigger is
    Java Code:
    CREATE OR REPLACE TRIGGER updateCheckedout
    AFTER DELETE OR UPDATE OR INSERT ON myprojects FOR EACH ROW
    BEGIN
      IF INSERTING THEN
        UPDATE partdescriptions
        SET checkedout = 'Y'
        WHERE pid = :new.pid ;
      END IF;
      
      IF DELETING THEN
        UPDATE partdescriptions
        SET checkedout = 'N'
        WHERE pid = :old.pid;
      END IF;    
      
      IF UPDATING THEN
        IF :new.completed = 'Y' THEN
          DELETE FROM myprojects
          WHERE :new.pid = :old.pid;
        END IF;
      END IF;
    
    END;
    How I can go about fixing the UPDATING section of code to delete from table1 and update table2 whenever table1.column gets updated?

    I'll gladly post my table data/descriptions if anyone thinks it will help.
    Liberty has never come from the government.
    Liberty has always come from the subjects of government.
    The history of liberty is the history of resistance.
    The history of liberty is a history of the limitation of governmental power, not the increase of it.

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    You would definately be better off asking this question on either a generic SQL forum, or a forum for the DB involved, rather than a Java DB connectivity forum.

  3. #3
    markw8500's Avatar
    markw8500 is offline Senior Member
    Join Date
    Jul 2008
    Location
    Pennsylvania, USA
    Posts
    136
    Rep Power
    0

    Default

    Triggers can be tricky... If you can, post the descriptions of each table and go step by on what you want to do, when table1 gets updated, inserted into, or deleted from...
    Who Cares... As Long As It Works...

  4. #4
    xcallmejudasx's Avatar
    xcallmejudasx is offline Senior Member
    Join Date
    Oct 2008
    Location
    Houston, TX & Flint, MI
    Posts
    609
    Rep Power
    6

    Default

    I ended up trying to make the UPDATING section call a procedure that would delete the completed work from my projects but again I ran into a mutating trigger error. I just don't think it's possible to combine all this into 1 option because I HAVE to be able to reference the table to check if it's completed.

    The work around I decided on was
    Java Code:
      IF UPDATING THEN
        IF :new.completed = 'Y' THEN
          UPDATE partdescriptions
          SET checkedout = 'N'
          WHERE pid = :old.pid;
        --  removeCompletedProjects(:new.pid);  
        END IF;   
      END IF;
    and then just giving the user the option to call removeCompletedProjects whenever they felt like.(This is just dummy data to help me learn PL/SQL on a personal database)

    And if you're still curious mark;
    Java Code:
    DESC myProjects
    pid        completed
    -------- ----------
    100000              N
    100001              Y
    100002              N
    
    DESC partDescriptions
    pid            checkedOut
    ----------- ------------
    100000                     Y
    100001                     Y
    100002                     Y
    100003                     N
    Essentially whenever update was ran on myprojects it would check if completed = 'Y' and if so it would change checkedout = 'N'. This works as intended and if I delete a row from myprojects it checks it back into the partdescriptions so thats fine too.

    Originally I didn't see the point in having myprojects hold onto completed projects so I was going to have it remove them whenever they were finished so I wanted to combine the steps by having the trigger call my deleteCompleted procedure (which is just this)
    Java Code:
    create or replace PROCEDURE removeCompletedProjects
    IS
    BEGIN
      DELETE FROM myprojects
      WHERE completed = 'Y';
    END;
    But like I said It kept giving me mutating table errors so I said f-that. It's not a big deal if completed projects are left in my table as long as they show up as not checked out. I did however leave the procedure available to just execute on its own if I felt like removing them.
    Liberty has never come from the government.
    Liberty has always come from the subjects of government.
    The history of liberty is the history of resistance.
    The history of liberty is a history of the limitation of governmental power, not the increase of it.

  5. #5
    markw8500's Avatar
    markw8500 is offline Senior Member
    Join Date
    Jul 2008
    Location
    Pennsylvania, USA
    Posts
    136
    Rep Power
    0

    Default

    Hey... As long as it works...
    Who Cares... As Long As It Works...

Similar Threads

  1. programmaticly trigger an event
    By maxim in forum AWT / Swing
    Replies: 3
    Last Post: 02-23-2009, 03:27 PM
  2. JSlider (to NOT cont'd trigger statechanged )
    By hanifa in forum AWT / Swing
    Replies: 4
    Last Post: 09-19-2008, 07:58 AM
  3. sending the message from trigger to mailid
    By geeta_ravikanti in forum JDBC
    Replies: 0
    Last Post: 04-04-2008, 11:09 AM
  4. Timer which should never end and should trigger daily
    By garinapavan in forum New To Java
    Replies: 0
    Last Post: 08-10-2007, 05:23 AM

Posting Permissions

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