#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    5
    Rep Power
    0

    Before Delete Trigger


    Hi!

    I have a realy annoying problem...
    here is the code...
    Code:
    CREATE OR REPLACE TRIGGER RechtMgrDel
    BEFORE DELETE ON benrecht
    FOR EACH ROW
    BEGIN
      if :old.gname<>'--' then
    	RAISE_APPLICATION_ERROR(-20901,'dont do it!');
      end if;
    END;
    /
    SHOW ERROR;
    if the trigger finds a row, it is not allowed to delete, the message is displayed and the triggeractions are rolledback...
    I want the triger to delete the rows, which are allowed to delete
    and to leave the rows, which are not allowed to delete..
    HOW????
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    5
    Rep Power
    0

    hm


    hi!

    i thought, INSTEAD OF trigger are only allowed on views??
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,374
    Rep Power
    391
    Yes. So

    Code:
    create view mc as select * from benrecht
    Code:
    CREATE OR REPLACE TRIGGER RechtMgrDel
    instead of DELETE ON mc
    FOR EACH ROW
    BEGIN
      if :old.gname<>'--' then
    	delete from benrecht
         where pk = :old.pk;
      end if;
    END;
    /
    SHOW ERROR;
  8. #5
  9. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    5
    Rep Power
    0
    thank you!
    I also had this solution... but im new to oracle
    and though there might be some magic word..
    SKIP ROW
    or something like that
  12. #7
  13. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    I would like to do it as follows.
    Please be noted that it is only pseudo code not the complete implementation, you can write the code if you think it is worth for you.

    CREATE OR REPLACE TRIGGER RechtMgrDel
    BEFORE DELETE ON benrecht
    FOR EACH ROW
    BEGIN

    Check_Validity_Of_Data (parameter list);

    END;
    /
    SHOW ERROR;


    CREATE OR REPLACE PROCEDURE Check_Validity_Of_Data (pamater list) IS
    .....
    .....
    .....
    BEGIN
    .....
    .....
    .....

    IF param1 = this_value THEN
    RAISE_APPLICATION_ERROR('This record could not be deleted.....');
    END IF;

    END Check_Validity_Of_Data;
    /

    Regards,

IMN logo majestic logo threadwatch logo seochat tools logo