Mutating trigger help

[xcallmejudasx]

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

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.

[masijade]

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.

[markw8500]

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...

[xcallmejudasx]

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

  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;

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)

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.

[markw8500]

Hey... As long as it works...