Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326

    Unanswered: Instead Of Trigger Table Level Ora 8

    Hi!

    I'm trying to create an instead of trigger on a table so that when a delete command is issued against a row, the row is not deleted but a flag is set indicating the row is desired for deletion.

    I'm getting an error: ORA-25002: cannot create INSTEAD OF triggers on tables.

    Not sure if this is because I'm creating this on an Oracle 8 (release 801070400) database or if I've written something incorrectly.

    Table Spec:
    SOFTDEL VARCHAR2(1)
    VALUE VARCHAR2(20)

    Trigger Spec:
    CREATE OR REPLACE TRIGGER TEMP_VALUES_INS_DEL_UPD
    INSTEAD OF DELETE ON TEMP_VALUES
    BEGIN
    UPDATE TEMP_VALUES SET SOFTDEL = 'D';
    END;

    Thanks for your time.

    Mike.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Instead Of Trigger Table Level Ora 8

    No, INSTEAD OF triggers are valid only for views, not for tables. This is true even in 10G! :-

    http://download-west.oracle.com/docs...htm#sthref5748

    You would have to hide your table behind a view to achieve this.

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Thank you. Followed your suggestion, created view and instead of trigger on that view.

    Works like a charm.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Be sure to revoke the DELETE privilege on the table from users, so that they can't bypass your trigger!

Posting Permissions

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