Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Unanswered: Create a trigger to prevent deletion on particular record

    Hi all,

    I'm new to oracle, PL/SQL. I am trying to create a trigger to prevent a deletion on a user-defined default record in a table.

    For example,

    Create table Test
    ( ID number(30),
    NUM2 number(30)
    );

    -- default user-defined record that cannot be deleted by anyone.
    Insert into Test values (1, 100);

    What should the trigger look like? I have try serveral different snytax, but none of them working for me.

    I would really appreciate if someone can tell me how it can be done.

    Thanks in advance.

    -jc

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    CREATE OR REPLACE TRIGGER trg_deltest
       BEFORE DELETE
       ON test
       FOR EACH ROW
       WHEN (OLD.ID = 1)
    BEGIN
       raise_application_error (-20100, 'You can not delete initial record');
    END;
    /

  3. #3
    Join Date
    Sep 2004
    Posts
    2
    CREATE OR REPLACE TRIGGER trg_deltest
    AFTER DELETE
    ON test
    FOR EACH ROW
    WHEN (OLD.ID = 1)
    BEGIN
    ROLLBACK;
    END;
    /

    Would it do the same? But I got this message when running it in SQL*Plus
    SQL> DELETE FROM TEST WHERE ID = 1;
    DELETE FROM TEST WHERE ID = 1
    *
    ERROR at line 1:
    ORA-04092: cannot ROLLBACK in a trigger
    ORA-06512: at "DBtest.TRG_DELTEST", line 2
    ORA-04088: error during execution of trigger 'DBtest.TRG_DELTEST'

    thanks,
    -jc

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    Well for one...as littlefoot's code does...you want to stop the deletion BEFORE delete, not AFTER. Next, you cannot rollback or commit in a trigger. The reason it simple, the rollback will effect every statement issued after a commit not just the one which triggered the rollback. This cannot be allowed for obvious reasons.

  5. #5
    Join Date
    Aug 2004
    Posts
    330
    "raise_application_error" will automatically issue a "logical" rollback.
    Last edited by urquel; 09-15-04 at 11:06.

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    No It Won't
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by urquel
    "raise_application_error" will automatically issue a rollback.
    The raise will generate an error and cause the delete to fail. Just like if you had a primary key and you attempted to insert a duplicate record. The row is simply not inserted.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Just to add ... it IS possible to use a COMMIT or ROLLBACK in a trigger if one uses pragma autonomous_transaction. Such things can be used in, for example, exception handling when you have a procedure which raises an exception but you want to store data about it in your log table. Pure insert will store data, but RAISE will cause it to be "invisible" to other users and, eventually, be lost. In such a case you can use autonomous_transaction and commit insert into log table, while changes made by a "guilty" statement remain rolled back.

    This WILL NOT work in your trigger, jchong. Something like
    Code:
    CREATE OR REPLACE TRIGGER trg_deltest
       BEFORE DELETE
       ON TEST
       FOR EACH ROW
       WHEN (OLD.ID = 1)
    DECLARE
       PRAGMA autonomous_TRANSACTION;
    BEGIN
       ROLLBACK;
    END;
    /
    compiles but allows deletion of record with ID = 1 as rollback statement rolls back ONLY code between BEGIN and END in this trigger, and not DELETE statement you issued.

  9. #9
    Join Date
    Apr 2004
    Posts
    246
    autonomous won't help here. did anyone actually test it:

    CREATE OR REPLACE TRIGGER trg_deltest
    BEFORE DELETE
    ON TEST
    FOR EACH ROW
    WHEN (OLD.ID = 1)
    DECLARE
    PRAGMA autonomous_TRANSACTION;
    BEGIN
    ROLLBACK;
    END;
    /

    SQL> select id from test;

    ID
    ----------
    4
    5
    1

    3 rows selected.


    SQL> delete test where id=1;

    1 row deleted.


    SQL> select id from test;

    ID
    ----------
    4
    5

    2 rows selected.


    this is because the autonomous creates a new (autonomous) scope which can rollback (or commit) it's own transactions without affecting the calling scope (sort of the inverse of savepoints).

    however:
    CREATE OR REPLACE TRIGGER trg_deltest
    BEFORE DELETE
    ON test
    FOR EACH ROW
    WHEN (OLD.ID = 1)
    BEGIN
    raise_application_error (-20100, 'You can not delete initial record');
    END;
    /

    SQL> select id from test;

    ID
    ----------
    4
    5
    1

    SQL> delete test where id=1;
    delete test where id=1
    *
    ERROR at line 1:
    ORA-20100: You can not delete initial record
    ORA-06512: at "MIKE.TRG_DELTEST", line 2
    ORA-04088: error during execution of trigger 'MIKE.TRG_DELTEST'



    Obviously, this will prevent the row from being deleted, but it's up to the app to handle the error (as always). Also, it WON'T ROLLBACK:

    SQL> select id from test;

    ID
    ----------
    4
    5
    1

    3 rows selected.

    SQL> delete test where id=4;

    1 row deleted.

    SQL> delete test where id=1;
    delete test where id=1
    *
    ERROR at line 1:
    ORA-20100: You can not delete initial record
    ORA-06512: at "MIKE.TRG_DELTEST", line 2
    ORA-04088: error during execution of trigger 'MIKE.TRG_DELTEST'


    SQL> select id from test;

    ID
    ----------
    5
    1

    2 rows selected.


    See, the delete of id=4 is still active (pending a commit or rollback from me), but the delete of id 1 failed (i.e., it was stopped, which is what the user wants).
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by shoblock
    No It Won't
    yes it will rollback the transaction due to the error.

    Who said anything about a PRAGMA autonomous transaction?
    No one said anything about that.

    *EDIT*
    It won't technically ROLLBACK, but as you stated, it blocks the
    transaction from happening. I was thinking of blocking instead
    of officially rolling back.
    Last edited by The_Duck; 09-15-04 at 13:10.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by The_Duck
    It won't technically ROLLBACK, but as you stated, it blocks the
    transaction from happening. I was thinking of blocking instead
    of officially rolling back.
    No, it blocks the statement from happening. The rest of the transaction (prior statements) is not rolled back:
    Code:
    SQL> create table test as select rownum id from emp;
    
    Table created.
    
    SQL> CREATE OR REPLACE TRIGGER trg_deltest
      2     BEFORE DELETE
      3     ON test
      4     FOR EACH ROW
      5     WHEN (OLD.ID = 1)
      6  BEGIN
      7     raise_application_error (-20100, 'You can not delete initial record');
      8  END;
      9  /
    
    Trigger created.
    
    SQL> delete from test where id=2;
    
    1 row deleted.
    
    SQL> delete from test where id=1;
    delete from test where id=1
             *
    ERROR at line 1:
    ORA-20100: You can not delete initial record
    ORA-06512: at "TANDREWS.TRG_DELTEST", line 2
    ORA-04088: error during execution of trigger 'TANDREWS.TRG_DELTEST'
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from test;
    
            ID
    ----------
             1
             3
             4
             5
             6
             7
             8
             9
            10
            11
            12
            13
    
    12 rows selected.
    As you can see, row 2 is still missing.

  12. #12
    Join Date
    Apr 2004
    Posts
    246
    "yes it will rollback the transaction due to the error."
    errors don't cause rollbacks

    "Who said anything about a PRAGMA autonomous transaction? No one said anything about that."
    Lots of people. half the posts were claiming to use autonomous as a solution. I replied to everything at once.

    "It won't technically ROLLBACK, but as you stated, it blocks the transaction from happening"
    what does "technically rollback" mean? either it rolls back or not - that's how the technology works. We're not talking esoterically, were we?

    I never said it rolls back. I pointed out that a rollback in an autonomous trigger won't help. I agree with whoever already posted that rolling back in a trigger is unwanted because you don't want to rollback everything else anyway. I stated "it WON'T ROLLBACK".


    Andrew, thank god you actually read the post and understood it. I was starting to think I was posting in Bizarro World.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by shoblock
    Andrew, thank god you actually read the post and understood it. I was starting to think I was posting in Bizarro World.
    It shows how people need to be careful with their words: e.g. not confusing "transaction" and "statement"!

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    *waddles off into the duck pond*
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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