Logical Delete

[Xeneize]

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

[DaPi]

Could you do the delete via a view with an INSTEAD OF trigger?
http://download-uk.oracle.com/docs/c...13trg.htm#8307

[tabreaz]

Create a view on the table and define Instead of trigger

for example...

base table - tab
view - vw

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

[tamilselvan]

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

[tabreaz]

Hi tamil,

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.

[tamilselvan]

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

[gamyers]

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

[Xeneize]

Create a view on the table and define Instead of trigger

for example...

base table - tab
view - vw

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.

[slimdave]

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.