Results 1 to 9 of 9
  1. #1
    Join Date
    May 2011
    Posts
    5

    Unanswered: SQL Oracle Trigger Helo

    I have gotten pretty far on this on my own. All I am asking for is help with the actual deleting of the values in my trigger. I am using Oracle SQL Developer.


    So I have these two very basic tables.

    CODE PNAME
    ------------ --------------
    101 ProjectX
    222 ProjectY
    355 ProjectZ
    973 ProjectAlpha

    ID NAME PCODE HOURS
    ----- ------------ -------------- ------------
    55055 Smith 101 20
    55055 Smith 222 10
    39002 Bob 973 25
    00001 Preston 355 5
    10000 Logan 355 5
    00777 Bond 222 20


    When an employee is removed from the second table and leaves no employee working on any project we should remove that project from the first table.

    So for example if I delete job 355 nothing happens because one person is still working on that project.

    Now when I delete ProjCode 101 or update ProjCode 101 to some other existing project I should remove this Code/projName from First table.

    Then the tables will look as such and project 101 is deleted.

    CODE NAME
    ------------ --------------
    222 ProjectY
    355 ProjectZ
    973 ProjectAlpha

    ID NAME PROJCODE HOURS
    ----- ------------ -------------- ------------
    55055 Smith 973 20
    55055 Smith 222 10
    39002 Bob 973 25
    10000 Logan 355 5
    00777 Bond 222 20


    I got the hint part of the problem.
    Start with a query that returns the value of projects not being worked on by any person. (Not in the intersect, Oracle uses minus command for this).

    --Start my trigger
    CREATE OR REPLACE TRIGGER DeleteProject AFTER DELETE ON ASSIGNMENT
    FOR EACH ROW
    BEGIN

    --findvalue to be removed. Example if 101 changed or deleted, that is
    --the value the following query will return. Also before any updates -
    --or deletes I verified that 0 rows returned for below query.


    select count(code) into countCode FROM (SELECT code FROM PROJECT
    minus
    SELECT ProjCode FROM ASSIGNMENT);

    select code FROM Project
    minus
    SELECT ProjCode FROM ASSIGNMENT;


    My real question is how do I use the above to delete 101 from the first table. Here is my two tries that were unsuccessful.

    CREATE OR REPLACE TRIGGER DeleteProject AFTER UPDATE or DELETE ON ASSIGNMENT
    FOR EACH ROW
    Declare
    countCode integer;
    delCode integer;
    pCode integer;
    BEGIN
    select count(code) into countCode FROM (SELECT code FROM PROJECT
    minus
    SELECT ProjCode FROM ASSIGNMENT);

    select code into delCode FROM Project
    minus
    SELECT ProjCode FROM ASSIGNMENT;

    select code into pCode FROM Project
    minus
    SELECT ProjCode FROM ASSIGNMENT;


    IF (countCode = 0) THEN
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || delCode);
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || countCode);
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || pCode);
    DELETE FROM PROJECT WHERE (pCode = delCode);
    END IF;
    END;



    I am able to get the print statements to work so the if statement works as expected.

    Thank you for your help.

    Now back to trying to get this to work.


    PS. I was able to write other triggers, procedures and PL/SQL statements, but I am stuck with this last piece.

    PPS. I even have the print statement to add to the end of this when it does what I want and will verify by printing out the values in both tables.

    PPPS. I am sure it is something stupid I am missing.

    Thanks again.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Delete from project where code = 101;
    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
    May 2011
    Posts
    5
    Quote Originally Posted by anacedent View Post
    Delete from project where code = 101;
    It needs to be more dynamic and be able to delete if say we deleted 355.

  4. #4
    Join Date
    May 2011
    Posts
    5
    OK. Some more progress...

    I am now getting following error. Which upon further review means that a delete was tried but not system was not able to complete these delete function.

    Closer...

    Line 23 is where the Delete command is located in below code.

    ORA-06512: at line 23
    02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
    *Cause: attempted to delete a parent key value that had a foreign
    dependency.

    *Action: delete dependencies first then parent or disable constraint.

    CREATE OR REPLACE TRIGGER DeleteProject AFTER UPDATE or DELETE ON ASSIGNMENT
    FOR EACH ROW
    Declare
    countCode integer;
    delCode integer;
    pCode integer;
    pId integer;
    BEGIN

    select count(code) into countCode FROM (SELECT code FROM PROJECT
    minus
    SELECT ProjCode FROM ASSIGNMENT);

    select code, ID into delCode, pId FROM Project
    minus
    SELECT ProjCode FROM ASSIGNMENT;

    select code into pCode FROM Project
    minus
    SELECT ProjCode FROM ASSIGNMENT;

    IF (countCode > 0) THEN
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || delCode);
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || countCode);
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || pCode);
    DELETE FROM PROJECT WHERE (pCode = delCode);
    END IF;
    END;

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    does the error accurately report reality regarding your tables & data?
    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
    May 2011
    Posts
    5
    OK. So the following code runs correctly, but not as a trigger.

    If I run the following from Declare to End, the code works. Meaning the delete is working properly.

    I verified this by querying the first table and the project 101 was not there. Only the remaining three were returned.

    So what I am doing wrong in the trigger set up.


    CREATE OR REPLACE TRIGGER DeleteProject AFTER UPDATE or DELETE ON ASSIGNMENT
    FOR EACH ROW
    Declare
    countCode integer;
    delCode integer;
    pCode integer;
    BEGIN

    select count(code) into countCode FROM (SELECT code FROM PROJECT
    minus
    SELECT ProjCode FROM ASSIGNMENT);

    select code into delCode FROM Project
    minus
    SELECT ProjCode FROM ASSIGNMENT;

    select code into pCode FROM Project
    minus
    SELECT ProjCode FROM ASSIGNMENT;

    IF (countCode > 0) THEN
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || delCode);
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || countCode);
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || pCode);
    DELETE FROM PROJECT WHERE (code = delCode);
    END IF;
    END;



    When I try to delete with the trigger in place I get the following error:

    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.


    Thanks again.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    One restriction which can exist is that TRIGGER is not allowed to issue SQL against table upon which the trigger is based.
    The reason being is that the content of this table is in flux & any new SQL can not determine whether it operates against the old contents or the new contents.
    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.

  8. #8
    Join Date
    May 2011
    Posts
    5
    I tried writing a cursor and I got the same error, but worded a little different.

    ORA-06512: at line 21
    02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
    *Cause: attempted to delete a parent key value that had a foreign
    dependency.
    *Action: delete dependencies first then parent or disable constraint.

    How do I delete the dependencies?

    At least you know I can write the code.

    create or replace
    TRIGGER DeleteProject AFTER UPDATE or DELETE ON ASSIGNMENT
    FOR EACH ROW
    Declare
    countCode integer;
    delCode integer;
    pCode integer;
    CURSOR DelPjctPtr IS
    SELECT code, count(code)
    FROM(SELECT code FROM PROJECT
    minus
    SELECT ProjCode FROM ASSIGNMENT)
    group by code
    order by code;

    BEGIN
    open DelPjctPtr;
    loop
    fetch DelPjctPtr into delCode, countCode;
    if (DelPjctPtr%found) then
    if (countCode > 0) then
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || delCode);
    DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || countCode);
    DELETE FROM PROJECT WHERE delCode in ((SELECT code FROM Project)
    minus
    (SELECT ProjCode FROM assignment));
    else
    exit;
    END IF;
    END IF;
    end loop;

    close DelPjctPtr;


    END;


    How do I remove this dependency?

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    start with the data as posted.
    DELETE FROM ASSIGNMENT WHERE PCODE=101;
    Then the TRIGGER fires which contains follow SQL
    SELECT ProjCode FROM ASSIGNMENT
    now should/does the SELECT above return the row WHERE PCODE=101?
    With an AFTER trigger, in theory the row is no longer in the table.
    This result set would cause the row to remain in PROJECT table.

    Research the use of ":OLD" reference within triggers
    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.

Posting Permissions

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