Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2010
    Posts
    8

    Unanswered: before delete trigger

    hi newbie here.

    I want to create a database trigger which prevents deletion of a record on a table if that same record exists in another table.

    example:
    PROJECTS TABLE
    PR PROJECT_DESC
    -- --------------------
    TU Transurban
    IE Integral Energy
    DL Dallas Mavericks
    SG Singapore
    AV Adult Video
    XX XXX

    EMP_PROJECTS TABLE
    EMPLOYEE_ID PR
    ----------- --
    1 TU
    2 TU
    3 TU
    3 IE
    4 DL
    5 SG
    6 AV
    7 AV

    the trigger should prevent deletion of records in projects table if there is an existing record in column 'PR' present in emp_projects table. how should i do this? im using oracle by the way. help me please!

  2. #2
    Join Date
    Jul 2004
    Posts
    102
    No need to create DB trigger.
    I think you can use Primary Key and Foreign Key relation to prevent the child record delete.
    Working Together...

  3. #3
    Join Date
    Dec 2010
    Posts
    8
    that's the problem. my mentor prohibits me from using those. he's trying to train me with oracle that is why he wants me to learn coding this trigger on my own.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    so, what is the problem with implementing your homework assignment?

    "create a database trigger which prevents deletion of a record on a table"
    - choose appropriate timing (before/after), event, table and type (statement/row) of the trigger
    - preventing the triggered event might be by obtained by raising an exception
    "if that same record exists in another table"
    - query the number of rows in that table satisfying given condition

    If you have any difficulties with syntax, please, read books/notes from that course. Alternatively, it is also fully described (with examples) in SQL Reference and PL/SQL User's Guide and Reference books, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/

  5. #5
    Join Date
    Dec 2010
    Posts
    8
    i tried doing this code and produced the following error:

    SQL> create or replace trigger cannot_delete
    2 before delete
    3 on projects
    4 for each row
    5 begin
    6 if projects.project_cd = emp_projects.project_cd then
    7 raiserror('Can not delete! Child record/s exists in EMP_PROJECTS table.',16,1)
    8 rollback tran
    9 end;
    10 /

    Warning: Trigger created with compilation errors.

    SQL> show error trigger cannot_delete;
    Errors for TRIGGER CANNOT_DELETE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    4/2 PLS-00103: Encountered the symbol "ROLLBACK" when expecting one
    of the following:
    := . ( % ;

    any suggestion to fix this?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    where/what is the end of the IF statement?
    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.

  7. #7
    Join Date
    Dec 2010
    Posts
    8
    oh sorry it should be like this:


    SQL> create or replace trigger cannot_delete
    2 before delete
    3 on projects
    4 for each row
    5 begin
    6 if projects.project_cd = emp_projects.project_cd then
    7 raiserror('Can not delete! Child record/s exists in EMP_PROJECTS table.',16,1)
    8 rollback tran
    9 end if
    10 end;
    11 /



    how to correct this code???

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >how to correct this code???
    with PL/SQL, what terminates statements?
    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.

  9. #9
    Join Date
    Dec 2010
    Posts
    8
    semi colon ';' ?

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >semi colon ';' ?
    yes

    So how many statements & how many semicolons?
    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.

  11. #11
    Join Date
    Dec 2010
    Posts
    8
    there's only one statement?

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >there's only one statement?
    valid syntax generates no errors.
    invalid syntax reports errors which need to be corrected.
    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.

  13. #13
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by 4dirk1 View Post
    there's only one statement?
    You mean the CREATE trigger, right?
    What about RAISERROR? (by the way, it is not standard Oracle procedure. is it some custom one for raising exceptions?)
    What about ROLLBACK TRAN? (by the way, what is that TRAN supposed to do there? where did you find this?)
    What about the IF .. THEN .. END IF statement?

    Of course, all this is in described in the books I posted links in my previous post. There is also search feature - you should explore it.

    In the end, you cannot compare values like this. You have to SELECT the count from EMP_PROJECTS table (with given condition on PROJECT_CD column) and check whether it is greater than zero. Columns from PROJECTS table shall be referenced by :OLD prefix, as stated in CREATE TRIGGER description. You really should have a look at it.

  14. #14
    Join Date
    Dec 2010
    Posts
    8
    I tried this one, but still no luck

    SQL> create or replace trigger cannot_delete
    2 before delete on projects
    3 for each row
    4 declare
    5 c integer;
    6 begin
    7 select count(*) into c from emp_projects
    8 where emp_projects.project_cd = ld.project_cd;
    9
    10 if c > 0 then
    11 raiserror('Can not delete! Child record/s exists in EMP_PROJECTS table.',16,1);
    12 rollback tran;
    13 end if;
    14 end;
    15 /

  15. #15
    Join Date
    Mar 2007
    Posts
    623
    It looks much better, however, there are still persisting problems I stated earlier. Without stating the actual error, I may only repeat myself:
    - is RAISERROR some custom procedure? (as there is no Oracle one with this name; the closest named RAISE_APPLICATION_ERROR requires different parameters)
    - what is TRAN in ROLLBACK TRAN supposed to mean?

    By the way, ROLLBACK should not be part of the trigger; it should be the caller who decides when the transaction should be ended. I am afraid it is not allowed in trigger without special pragma which would be improper in this case at all.

Posting Permissions

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