DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Logical Delete

  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Logical Delete

    Hi, I have a table with a flag (DELETE = YES / NO )
    I need that every time i do a DELETE ..... FROM table WHERE ID..
    the flag change to delete = YES on that id.
    In postgres i can do this with a trigger on delete, set the flag on YES and then interrup the delete.

    On Oracle I'm Getting an error because of muttating table.

    How can i fix this problem in oracle?

    Thank

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Could you do the delete via a view with an INSTEAD OF trigger?
    http://download-uk.oracle.com/docs/c...13trg.htm#8307
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Sep 2005
    Posts
    278
    Create a view on the table and define Instead of trigger

    for example...

    base table - tab
    view - vw

    Code:
    create or replace trigger v1
    INSTEAD of delete on vw
    begin
      update tab
      set deleted = 'Y'
      where ....;
    end;
    and run delete command on the view
    Last edited by tabreaz; 09-11-2006 at 02:30 PM.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You update the col with "YES" or "NO" for a ID, then you delete the row.
    Why do you want to update the col?

    Tamil

  5. #5
    Join Date
    Sep 2005
    Posts
    278
    Hi tamil,

    HTML Code:
    Xeneize:
    
    In postgres i can do this with a trigger on delete, set the flag on YES and then interrup the delete.
    The row does not need to deleted, set the column deleted to be 'YES' when ever a delete command is run.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Instead of just deleting the row, you first update a column, that needs a trigger and a view.
    Since the rows are left behind in the table, will your application work normally?
    Any query against the table must have WHERE clause "DELETED_COL="YES", don't you think it is a over head?

    Tamil

  7. #7
    Join Date
    Feb 2005
    Posts
    158
    How about a BEFORE DELETE FOR EACH ROW trigger that inserts the row into another table ? [It could insert it back to the same table with the DELETE_FLAG = 'Y' in an AFTER DELETE trigger if you REALLY want.]

    Alternatively look into workspace manager which does this sort of thing
    http://download-west.oracle.com/docs...nt.htm#1002069

  8. #8
    Join Date
    Sep 2006
    Posts
    4
    Quote Originally Posted by tabreaz
    Create a view on the table and define Instead of trigger

    for example...

    base table - tab
    view - vw

    Code:
    create or replace trigger v1
    INSTEAD of delete on vw
    begin
      update tab
      set deleted = 'Y'
      where ....;
    end;
    and run delete command on the view

    Thankz, that works perfect.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    For that level of effort one might do well to move the deleted rows to a new archive table instead of flagging them. My feeling is that it would be more optimizer firendly, and you wouldn't have to maintain indexes on the deleted rows.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width