Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    1

    Question Unanswered: Trigger to prevent row from being delete in Oracle

    I am trying to create a BEFORE DELETE trigger that will prevent a user from deleting a particular row in a table:

    CREATE OR REPLACE TRIGGER del_100_group_tb
    BEFORE DELETE ON group_tb
    FOR EACH ROW

    WHEN ( old.group_div = 100 AND
    new.group_div <> old.group_div OR
    new.group_section <> old.group_section OR
    new.group_group <> old.group_group)

    BEGIN


    raise_application_error(-20010, 'I dont want to display a message');

    END;
    /


    But I do not want to display an error message. I just want it to silently prevent the delete from happening. Help please!

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Have an exception block in your application that captures the 20010 error. There is no other way. A trigger either completes or it errors.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Why not forgetting this trigger and just add a condition in the WHERE clause of the DELETE statements ? Wouldn't it be clearer ?

    That's what I would do, anyway. I avoid using triggers as often as I can (which means almost always ).

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Dec 2010
    Posts
    8
    hi newbie here,

    how can i create a trigger for this question:
    Create a validation (database trigger) that will not allow deletion of record in PROJECTS table if it has an existing child record in EMP_PRROJECT table.

    here are the tables affected:
    PROJECTS:
    PR PROJECT_DESC
    -- --------------------
    TU Transurban
    IE Integral Energy
    DL Dallas Mavericks
    SG Singapore
    AV Adult Video

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

    please help! TIA!

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No trigger in this case. If referential integrity is enforced (foreign keys), database will take care about it itself (i.e. won't allow you to delete "parent" record as long as "child" record(s) exist).

Posting Permissions

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